From: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
---|---|
To: | Didier Carlier <didier(dot)carlier(at)haulogy(dot)net> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Finding out why parallel queries not avoided |
Date: | 2018-07-22 03:45:49 |
Message-ID: | CAKJS1f99Z9uHq80HeA6k11n-d1EMzVzwn1utJ_3Ofpf_B+483g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 21 July 2018 at 20:15, Didier Carlier <didier(dot)carlier(at)haulogy(dot)net> wrote:
> explain select count(*) from calendar c1, calendar c2, measure m where
> c1.stddate='2015-01-01' and c2.stddate='2015-12-31' and m.fromdateid >=c1.calendarid and m.fromdateid < c2.calendarid;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------
> Aggregate (cost=5073362.73..5073362.74 rows=1 width=8)
> -> Nested Loop (cost=8718.47..4988195.81 rows=34066770 width=0)
> -> Index Scan using calendar_stddate_unique on calendar c2 (cost=0.28..2.30 rows=1 width=4)
> Index Cond: (stddate = '2015-12-31 00:00:00+01'::timestamp with time zone)
> -> Nested Loop (cost=8718.19..4647525.81 rows=34066770 width=4)
> -> Index Scan using calendar_stddate_unique on calendar c1 (cost=0.28..2.30 rows=1 width=4)
> Index Cond: (stddate = '2015-01-01 00:00:00+01'::timestamp with time zone)
> -> Bitmap Heap Scan on measure m (cost=8717.91..4306855.81 rows=34066770 width=4)
> Recheck Cond: ((fromdateid >= c1.calendarid) AND (fromdateid < c2.calendarid))
> -> Bitmap Index Scan on idx_measure_fromdate (cost=0.00..201.22 rows=34072527 width=0)
> Index Cond: ((fromdateid >= c1.calendarid) AND (fromdateid < c2.calendarid))
>
> Both queries return the same answers but I don't see why the second one doesn't use parallel query.
You'd likely be better of writing the query as:
select count(*) from measure where fromdateid >= (select calendarid
from calendar where stddate = '2015-01-01') and fromdateid < (select
calendarid from calendar where stddate = '2015-12-31');
The reason you get the poor nested loop plan is that nested loop is
the only join method that supports non-equijoin.
Unsure why you didn't get a parallel plan. Parallel in pg10 supports a
few more plan shapes than 9.6 did. Unsure what version you're using.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Didier Carlier | 2018-07-22 08:53:03 | Re: Finding out why parallel queries not avoided |
Previous Message | Didier Carlier | 2018-07-21 08:15:25 | Finding out why parallel queries not avoided |