> On 22 Jul 2018, at 05:45, David Rowley <david.rowley@2ndquadrant.com> wrote:
>
> On 21 July 2018 at 20:15, Didier Carlier <didier.carlier@haulogy.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.
It doesn’t use a parallel query but It’s faster indeed, (~12 sec vs 9sec), thanks for the info.
>
> 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.
It’s on 10.3 which is the latest available package prebuilt for SmartOS