Postgres, is query performance depending on front-end tool?

Question

I was trying the same query

select column_1, count(1)
from table
group by 1
order by 1 desc

with several front-end tools, and I was getting very different results.(I ran query several time to avoid anomalies or cashing issues) I always thought that it depends on server not a client tool.

PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

First with pgAdmin3 LTS 1.23

query ran parallelly with 4 threads and finished within 12s

Second with DbVisualizer 10.0.21

query ran only in single thread and finished within 70s

(and yes I checked it with show command and parallel setting were as stated above)

Third my colleague with Navicat

query ran parallelly with 4 threads and finished within 30s

So who makes the decision how the query is processed server or client?

EDIT:

The problem seems to be with DbVisualizer, strangely if I just run query it does not parallelise but when explain analyse option is used it does, I was checking this on server to be sure, viz screenshot

And here is the explain analyse from pgAdmin

https://explain.depesz.com/s/tP8Pi

This is the execution plan from DbVisualizer:

https://explain.depesz.com/s/RSWw

Answer 1

I have contacted the DbVis support and we have found the problem. It was in JDBC driver.

Here is some more details:

During tests, we have noticed we cannot trigger parallel queries when using the JDBC. Query being executed via PSQL shows parallel query execution planned and performed (for example: "Workers Planned: 2, Workers Launched: 2". However, running the same query via JDBC shows that the query was planned in parallel, but not executed in parallel. Typical output is: "Workers Planned: 2, Workers Launched: 0". The query is planned for parallel execution, but it is then performed in a single thread. We only see this behaviour when going via the JDBC.

https://github.com/pgjdbc/pgjdbc/issues/1246

The solution for DbVisualizer is to change Max Rows

Setting Max Rows = -1, query runs in parallel
Setting Max Rows > 0, query runs in sequentially

Answer 2

I can only guess.

• One possibility is that DbVisualizer is running the query using a cursor. That would prevent parallel query from being used.

• Another possibility is that the transaction isolation level is SERIALIZABLE. That has the same effect.

You can see the list of limitations for parallel query in the documentation.