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
