default range partition and constraint exclusion

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

Responses

Browse pgsql-hackers by date

  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