DBEaver Vs PG Admin

Question

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?.

Answer 1

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.

Answer 2

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.