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
