Re: Explain says 8 workers planned, only 1 executed

On 3/21/20 10:25 AM, Alastair McKinley wrote:
> 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()".

Postgres version?

What is happening in function_that_executes_the_query()?

You might want to take a look at below to see any of the conditions apply:

https://www.postgresql.org/docs/12/when-can-parallel-query-be-used.html

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com