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