Search Postgresql Archives

Parallel query only when EXPLAIN ANALYZEd

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

 



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

[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