partition pruning doesn't work with IS NULL clause in multikey rangepartition case
От | Ashutosh Bapat |
---|---|
Тема | partition pruning doesn't work with IS NULL clause in multikey rangepartition case |
Дата | |
Msg-id | CAFjFpRc7qjLUfXLVBBC_HAnx644sjTYM=qVoT3TJ840HPbsTXw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: partition pruning doesn't work with IS NULL clause in multikeyrange partition case
|
Список | pgsql-hackers |
Hi, Consider following test case. create table prt (a int, b int, c int) partition by range(a, b); create table prt_p1 partition of prt for values (0, 0) to (100, 100); create table prt_p1 partition of prt for values from (0, 0) to (100, 100); create table prt_p2 partition of prt for values from (100, 100) to (200, 200); create table prt_def partition of prt default; In a range partitioned table, a row with any partition key NULL goes to the default partition if it exists. insert into prt values (null, 1); insert into prt values (1, null); insert into prt values (null, null); select tableoid::regclass, * from prt; tableoid | a | b | c ----------+---+---+--- prt_def | | 1 | prt_def | 1 | | prt_def | | | (3 rows) There's a comment in get_partition_for_tuple(), which says so. /* * No range includes NULL, so this will be accepted by the * default partition if there is one, and otherwise rejected. */ But when there is IS NULL clause on any of the partition keys with some condition on other partition key, all the partitions scanned. I expected pruning to prune all the partitions except the default one. explain verbose select * from prt where a is null and b = 100; QUERY PLAN ---------------------------------------------------------------------- Append (cost=0.00..106.52 rows=3 width=12) -> Seq Scan on public.prt_p1 (cost=0.00..35.50 rows=1 width=12) Output: prt_p1.a, prt_p1.b, prt_p1.c Filter: ((prt_p1.a IS NULL) AND (prt_p1.b = 100)) -> Seq Scan on public.prt_p2 (cost=0.00..35.50 rows=1 width=12) Output: prt_p2.a, prt_p2.b, prt_p2.c Filter: ((prt_p2.a IS NULL) AND (prt_p2.b = 100)) -> Seq Scan on public.prt_def (cost=0.00..35.50 rows=1 width=12) Output: prt_def.a, prt_def.b, prt_def.c Filter: ((prt_def.a IS NULL) AND (prt_def.b = 100)) (10 rows) I thought that the following code in get_matching_range_bounds() /* * If there are no datums to compare keys with, or if we got an IS NULL * clause just return the default partition, if it exists. */ if (boundinfo->ndatums == 0 || !bms_is_empty(nullkeys)) { result->scan_default = partition_bound_has_default(boundinfo); return result; } would do the trick but through the debugger I saw that nullkeys is NULL for this query. I didn't investigate further to see why nullkeys is NULL, but it looks like that's the problem and we are missing an optimization. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
В списке pgsql-hackers по дате отправления: