Re: [HACKERS] path toward faster partition pruning

On 2017/12/20 17:27, Amit Langote wrote:
> On 2017/12/19 13:36, David Rowley wrote:
>> 5. I've noticed that partition pruning varies slightly from constraint
>> exclusion in the following case:
>>
>> create table ta (a int not null) partition by list (a);
>> create table ta1 partition of ta for values in(1,2);
>> create table ta2 partition of ta for values in(3,4);
>>
>> explain select * from ta where a <> 1 and a <> 2; -- partition ta1 is
>> not eliminated.
>>                          QUERY PLAN
>> -------------------------------------------------------------
>>  Append  (cost=0.00..96.50 rows=5050 width=4)
>>    ->  Seq Scan on ta1  (cost=0.00..48.25 rows=2525 width=4)
>>          Filter: ((a <> 1) AND (a <> 2))
>>    ->  Seq Scan on ta2  (cost=0.00..48.25 rows=2525 width=4)
>>          Filter: ((a <> 1) AND (a <> 2))
>> (5 rows)
>>
>>
>> alter table ta1 add constraint ta1_chk check (a in(1,2)); -- add a
>> check constraint to see if can be removed.
>> explain select * from ta where a <> 1 and a <> 2; -- it can.
>>                          QUERY PLAN
>> -------------------------------------------------------------
>>  Append  (cost=0.00..48.25 rows=2525 width=4)
>>    ->  Seq Scan on ta2  (cost=0.00..48.25 rows=2525 width=4)
>>          Filter: ((a <> 1) AND (a <> 2))
>> (3 rows)
> 
> I see.  It seems that the current approach of handling <> operators by
> turning clauses containing the same into (key > const OR key < const)
> doesn't always work.  I think I had noticed that for list partitioning at
> least.  I will work on alternative way of handling that in the next
> version of the patch.

I think I was able to make this work and in the process of making it work,
also came to the conclusion that this could be made to work sensibly
*only* for list partitioned tables.  That's because one cannot prune a
given partition using a set of <> operator clauses, if we cannot be sure
that those clauses exclude *all* values of the partition key allowed by
that partition.  It's only possible to do that for a list partitioned
table, because by definition one is required to spell out every value that
a given partition of such table allows.

There is a new function in the updated patch that does the pruning using
<> operator clauses and it's implemented by assuming it's only ever called
for a list partitioned table.  So, sorry range and hash partitioned tables.

Attached updated set of patches.

Thanks,
Amit