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 | Resend email |
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 |
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 |