From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | default range partition and constraint exclusion |
Date: | 2017-11-17 05:57:56 |
Message-ID: | ba7aaeb1-4399-220e-70b4-62eade1522d0@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi.
While working on the patch for partition pruning for declarative
partitioned tables, I noticed that default range partition will fail to be
included in a plan in certain cases due to pruning by constraint exclusion.
Consider a multi-column range-partitioned table:
create table mc2p (a int, b int) partition by range (a, b);
create table mc2p_default partition of mc2p default;
create table mc2p0 partition of mc2p
for values from (minvalue, minvalue) to (1, 1);
create table mc2p2 partition of mc2p
for values from (1, 1) to (maxvalue, maxvalue);
-- add a row with null b and check that it enters the default partition
insert into mc2p values (2);
INSERT 0 1
select tableoid::regclass, * from mc2p;
tableoid | a | b
--------------+---+---
mc2p_default | 2 |
(1 row)
-- but selecting like this doesn't work
select tableoid::regclass, * from mc2p where a = 2;
tableoid | a | b
----------+---+---
(0 rows)
because:
explain (costs off) select tableoid::regclass, * from mc2p where a = 2;
QUERY PLAN
--------------------------------------
Result
-> Append
-> Seq Scan on mc2p2
Filter: (a = 2)
(4 rows)
If you look at the default partition's constraint, which is as follows:
NOT (
((a < 1) OR ((a = 1) AND (b < 1)))
OR
((a > 1) OR ((a = 1) AND (b >= 1)))
)
you'll notice that it doesn't explicitly say that the default partition
allows rows where a is null or b is null or both are null. Given that,
constraint exclusion will end up concluding that the default partition's
constraint is refuted by a = 2.
The attached will make the constraint to look like:
NOT (
a IS NOT NULL
OR
b IS NOT NULL
((a < 1) OR ((a = 1) AND (b < 1)))
OR
((a > 1) OR ((a = 1) AND (b >= 1)))
)
Now since b IS NULL (which, btw, is NOT (b IS NOT NULL)) fails to be
refuted, as a whole, the whole constraint is not refuted. So, we get the
correct result:
select tableoid::regclass, * from mc2p where a = 2;
tableoid | a | b
--------------+---+---
mc2p_default | 2 |
(1 row)
explain (costs off) select tableoid::regclass, * from mc2p where a = 2;
QUERY PLAN
--------------------------------------
Result
-> Append
-> Seq Scan on mc2p2
Filter: (a = 2)
-> Seq Scan on mc2p_default
Filter: (a = 2)
(6 rows)
Attached patches. Thoughts?
Thanks,
Amit
Attachment | Content-Type | Size |
---|---|---|
0001-Add-default-partition-case-in-inheritance-testing.patch | text/plain | 7.3 KB |
0002-Tweak-default-range-partition-s-constraint-a-little.patch | text/plain | 4.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Bapat | 2017-11-17 06:02:09 | Re: [HACKERS] Partition-wise aggregation/grouping |
Previous Message | Masahiko Sawada | 2017-11-17 05:43:25 | Re: [HACKERS] Remove duplicate setting in test/recovery/Makefile |