Hi all,
I have a long running query that I have tweaked along with config (e.g. min_parallel_table_scan_size) to execute nicely and very fast in parallel which works as expected executed directly from psql client. The query is then embedded in a psql function like "return query select * from function_that_executes_the_query()".
I am checking the explain output (using execute explain $query) just before executing inside my function and it the plan is identical to what I would expect, planning 8 workers. However, this query actually only uses 1 worker and takes many times longer than when ran directly on the psql command line with the same server configuration parameters.
Why would the explain output be different from the executed plan? Is this a limitation of plpgsql functions? Is there any way to debug this further?
If it is meaningful during parallel execution I notice lots of "postgres: parallel worker" proceses in top and when executing from my function just a single "postgres: $user $db $host($pid) SELECT" processes.
Best regards,
Alastair