>What's max_worker_processes set to?
8
>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.rowley@xxxxxxxxxxxxxxx> wrote:
On 30 September 2016 at 08:52, Jay Knight <jay@xxxxxxxxxxxxx> 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
