default range partition and constraint exclusion
От | Amit Langote |
---|---|
Тема | default range partition and constraint exclusion |
Дата | |
Msg-id | ba7aaeb1-4399-220e-70b4-62eade1522d0@lab.ntt.co.jp обсуждение исходный текст |
Ответы |
Re: default range partition and constraint exclusion
|
Список | 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
Вложения
В списке pgsql-hackers по дате отправления: