Search Postgresql Archives

Re: Parallel query only when EXPLAIN ANALYZEd

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux