Re: Parallel query only when EXPLAIN ANALYZEd

>What's max_worker_processes set to?

8

>One theory would be that, the worker might not have been available
>when you performed the query execution, but it just happened to be
>when you did the EXPLAIN ANALYZE

This happens consistently this way. Every time I run it with explain
analyze it uses parallel workers, and every time I run it without it
doesn't. I just enabled auto_explain, and see that it is "planning" to use
a worker, but doesn't launch it. Is there a way to know why a planned
worker doesn't get launched?

2016-09-29 16:45:44 CDT LOG: duration: 50703.595 ms plan:
Query Text: select count(*) from t1
join t2 on t1.item = t2.item and t1.low <= t2.high and t1.high >= t2.low
Finalize Aggregate (cost=14609058.99..14609059.00 rows=1 width=8)
(actual time=50703.584..50703.584 rows=1 loops=1)
-> Gather (cost=14609058.88..14609058.99 rows=1 width=8) (actual
time=50703.581..50703.581 rows=1 loops=1)
Workers Planned: 1
Workers Launched: 0
-> Partial Aggregate (cost=14608958.88..14608958.89 rows=1
width=8) (actual time=50703.579..50703.579 rows=1 loops=1)
-> Nested Loop (cost=0.42..13608937.28 rows=400008641
width=0) (actual time=0.534..50577.673 rows=3669891 loops=1)
-> Parallel Seq Scan on t1 (cost=0.00..3386.71
rows=176471 width=12) (actual time=0.041..18.351 rows=300000 loops=1)
-> Index Only Scan using t2_item_low_high_idx on
t2 (cost=0.42..63.77 rows=1333 width=12) (actual time=0.167..0.168 rows=12
loops=300000)
Index Cond: ((item = t1.item) AND (low <=
t1.high) AND (high >= t1.low))
Heap Fetches: 0

Thanks,
Jay K

On Thu, Sep 29, 2016 at 4:29 PM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On 30 September 2016 at 08:52, Jay Knight <jay(at)jayknight(dot)com> wrote:
> > So, why might postgres parallelize the query when I explain analyze it,
> but
> > not when I just run it by itself?
>
> One theory would be that, the worker might not have been available
> when you performed the query execution, but it just happened to be
> when you did the EXPLAIN ANALYZE. If the executor can't get a free
> worker process, then it'll just do all the work in the main process.
> The plan parallel plan that you've shown, given no extra worker
> processes, would most likely perform the same as the serial plan you
> showed, since the extra Finalize Aggregate node is only handling 1 row
> anyway.
>
> What's max_worker_processes set to?
>
> If this is just a test machine, you should be able to see what's going
> on if you install auto_explain, and enable auto_explain.log_analyze
> (https://www.postgresql.org/docs/current/static/auto-explain.html)
> Setting this up will log the EXPLAIN ANALYZE to the PostgreSQL logs
> when you execute the query as normal.
>
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>