Parallel query only when EXPLAIN ANALYZEd

From: Jay Knight <jay(at)jayknight(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Parallel query only when EXPLAIN ANALYZEd
Date: 2016-09-29 19:52:53
Message-ID: CAD4o9TS7TnDuepPQgOhxfM5ZTVksT0XOvrd8=TbxSYCgPaQ+DA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I installed 9.6 on Windows 7, and am experimenting with the new parallel
query feature. I've found a behavior that seems inconsistent to me.
Consider these two tables:

create table t1 as (
with r as (
SELECT generate_series(1,300000) AS id, ceil(random()*25)::int as item,
ceil(random()*100000)::int AS low
)
select item, low, ceil(low + random()*100)::int as high from r
);
create index on t1(item, low, high);

create table t2 as (
with r as (
SELECT generate_series(1,300000) AS id, ceil(random()*25)::int as item,
ceil(random()*100000)::int AS low
)
select item, low, ceil(low + random()*100)::int as high from r
);
create index on t2(item, low, high);

And this query:

explain analyze
select count(*)
from t1
join t2 on t1.item = t2.item and t1.low <= t2.high and t1.high >= t2.low;

If max_parallel_workers_per_gather is set to zero, this query take around
54 seconds on my machine (with and without the explain analyze). But if I
set max_parallel_workers_per_gather=4, The query with explain analyze takes
25 seconds, and the query plan shows that it is using some workers to
parallelize the work. But* when I run it with
max_parallel_workers_per_gather=4 without the explain analyze, it takes 54
seconds again*. Watching my CPU graphs, it looks the same as it does with
max_parallel_workers_per_gather=0 (just one core spikes). Query plan with
zero workers: https://explain.depesz.com/s/RUO, and with 4 workers:
https://explain.depesz.com/s/AB97.

So, why might postgres parallelize the query when I explain analyze it, but
not when I just run it by itself?

Thanks,
Jay K

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2016-09-29 20:00:56 Re: lost synchronization with server: got message type "Z"
Previous Message Thomas Kellerer 2016-09-29 19:50:34 pg_upgrade from 9.5 to 9.6 fails with "invalid argument"