Re: [HACKERS] path toward faster partition pruning

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Beena Emerson <memissemerson(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] path toward faster partition pruning
Date: 2017-12-21 10:38:36
Message-ID: 5ebae4cf-8145-975c-ad75-16eb7f756f32@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Some-interface-changes-for-partition_bound_-cmp-bsea-v16.patch text/plain 11.6 KB
0002-Introduce-a-get_partitions_from_clauses-v16.patch text/plain 61.8 KB
0003-Move-some-code-of-set_append_rel_size-to-separate-fu-v16.patch text/plain 8.6 KB
0004-More-refactoring-around-partitioned-table-AppendPath-v16.patch text/plain 13.1 KB
0005-Teach-planner-to-use-get_partitions_from_clauses-v16.patch text/plain 44.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2017-12-21 10:42:25 Re: pgsql: Add parallel-aware hash joins.
Previous Message Feike Steenbergen 2017-12-21 10:30:50 Fix permissions check on pg_stat_get_wal_senders