Postgresql – DBEaver Vs PG Admin

dbeaverpgadmin-4postgresql

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

Best Answer

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.

Related Question