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