1

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

enter image description here

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

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Baker
  • 425
  • 1
  • 7
  • 20
  • How do you measure the time? How many aggregate rows does the query produce? What is the definition of `column_1` ? – joop Sep 19 '19 at 10:13
  • @joop query returns 1010 rows, query run time is the info from tool itself usually it is in the right down corner – Baker Sep 19 '19 at 12:59
  • @LaurenzAlbe i added it, but there is a catch, more in my post edit – Baker Sep 19 '19 at 12:59
  • Please also add the `EXPLAIN (ANALYZE, BUFFERS)` output for the same query on DbVisualizer. – Laurenz Albe Sep 19 '19 at 13:02
  • 1
    The screenshots prove nothing - all I can see is that in the one with the red X, no query at all is running. You forgot to include `BUFFERS` with the second `EXPLAIN`. The query seems to access a lot of blocks, so variations in run time caused by different caching are unsurprising. – Laurenz Albe Sep 19 '19 at 13:16
  • @LaurenzAlbe the screen has the info, yellow arrow I draw points at parallel workers while red X means there are none for dbvisualizer select, also you can see it with CPU usage there is only one thread with 100 percent usage, or look above where you can see all 8 cores and their usage – Baker Sep 19 '19 at 13:30
  • 1
    I'll mark the question as off-topic because you don't provide all necessary information. – Laurenz Albe Sep 19 '19 at 13:36
  • If `EXPLAIN` run from DbVisualizer shows that a parallel plan is used, then it will be used. It can happen that a query planned parallel will not run parallel, because concurrent queries already exhausted `max_parallel_workers`. But that's independent of the client GUI used. – Laurenz Albe Sep 19 '19 at 13:51
  • @LaurenzAlbe it does not, I can see on server how many threads are being used, because only one of 8 cores is utilized compare to five cores with explain analyze, I have tried it at least 20 times and result was always the same. I will try to contact dbvisualizer support and see if something will come up from them – Baker Sep 19 '19 at 14:10
  • See here: https://blog.sql-workbench.eu/post/client-performance-considerations/ –  Sep 24 '19 at 09:35

2 Answers2

0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

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

Baker
  • 425
  • 1
  • 7
  • 20