3

When I ran a query in Postgres database using the Pg Admin tool it took only 2 SECS ( I can see the parallel workers being used in the plan) but when I ran the same query in DBeaver, it took around 3 MINS to complete (the plan is different and I don't see yhe parallel workers being invoked. Can anybody explain why the difference in these tools? Are the drivers causing the different plan and execution time?. What is the driver PG Admin is using to connect to Postgres database?.

Improve this question
3
  • 1
    PGAdmin is written in Python but DBeaver is in Java. PGAdmin is especially written for PostgreSQL whereas DBeaver supports any database with a JDBC connection. I think that this question is on-topic - it's not a shopping-list question and it should not downvoted IMHO! p.s. welcome to the forum!
    – Vérace
    Nov 20, 2019 at 19:09
  • Thanks for the clarification.Is the PG Admin using the psycopg2 adapter to connect to Postgres ?.
    – Krishna
    Nov 20, 2019 at 19:59
  • 1
    Regarding parallel workers: blog.sql-workbench.eu/post/client-performance-considerations but I really doubt that the use of parallel workers can make a difference between 2 seconds and one minute. Did pgAdmin retrieve all the rows that were returned? Or maybe just the first 50 rows?
    – user1822
    Nov 20, 2019 at 20:36

2 Answers 2

Reset to default
6

Not knowing DBeaver, I can only guess: it uses a cursor to process statement results. That has two consequences:

  • Parallelization cannot be used:

    The query might be suspended during execution. In any situation in which the system thinks that partial or incremental execution might occur, no parallel plan is generated. For example, a cursor created using DECLARE CURSOR will never use a parallel plan.

  • A different execution plan may be used:

    cursor_tuple_fraction (floating point)

    Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved. The default is 0.1. Smaller values of this setting bias the planner towards using “fast start” plans for cursors, which will retrieve the first few rows quickly while perhaps taking a long time to fetch all rows. Larger values put more emphasis on the total estimated time. At the maximum setting of 1.0, cursors are planned exactly like regular queries, considering only the total estimated time and not how soon the first rows might be delivered.

Improve this answer
3
  • Well I did try setting cursor_tuple_fraction to 1.0 to see what would happen but after a restart of pg, DBeaver remained stuck unable to launch a parallel query.
    – Dzamo Norton
    Jan 21, 2020 at 16:08
  • That may well be. Try to get EXPLAIN (ANALYZE, BUFFERS) output for both queries, then we can say more.
    – Laurenz Albe
    Jan 21, 2020 at 16:33
  • Thanks. I found a trick to get DBeaver over the line which I'll post as an answer
    – Dzamo Norton
    Jan 22, 2020 at 4:50
1

I do believe that the diagnosis of Laurenz Albe is correct but, at least for me on pg 11, setting cursor_tuple_fraction = 1.0 did not result in a parallel plan for queries launched from DBeaver. But what did work was setting the ResultSet fetch size in DBeaver to 0. Note that DBeaver will fetch all of the results for every query with this setting so you may need to make more use of limit N in your queries when you only want some of the results.

Improve this answer
2
  • That confirms my first suspicion that DBeaver uses cursors. Using setFetchSize() to a value other than 0 makes the JDBC driver use cursors, which disables parallel query.
    – Laurenz Albe
    Jan 22, 2020 at 6:52
  • Warning: if you do set ResultSet fetch size in DBeaver to 0 be wary of previewing a table using F4!
    – Dzamo Norton
    Jan 22, 2020 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.