On Wed, 11 Oct 2023 at 15:49, David Rowley <dgrowle...@gmail.com> wrote:
> It might have been better if PartClauseInfo could also describe IS
> NULL quals, but I feel if we do that now then it would require lots of
> careful surgery in partprune.c to account for that. Probably the fix
> should be localised to get_steps_using_prefix_recurse() to have it do
> something like pass the keyno to try and work on rather than trying to
> get that from the "prefix" list. That way if there's no item in that
> list for that keyno, we can check in step_nullkeys for the keyno.
>
> I'll continue looking.
The fix seems to amount to the attached. The following condition
assumes that by not recursively processing step_lastkeyno - 1 that
there will be at least one more PartClauseInfo in the prefix List to
process. However, that didn't work when that partition key clause was
covered by an IS NULL clause.
If we adjust the following condition:
if (cur_keyno < step_lastkeyno - 1)
to become:
final_keyno = ((PartClauseInfo *) llast(prefix))->keyno;
if (cur_keyno < final_keyno)
then that ensures that the else clause can pick up any clauses for the
final column mentioned in the 'prefix' list, plus any nullkeys if
there happens to be any of those too.
For testing, given that 13838740f (from 2020) had a go at fixing this
already, I'm kinda thinking that it's not overkill to test all
possible 16 combinations of IS NULL and equality equals on the 4
partition key column partitioned table that commit added in
partition_prune.sql.
I added some tests there using \gexec to prevent having to write out
each of the 16 queries by hand. I tested that pruning worked (i.e 1
matching partition in EXPLAIN), and that we get the correct results
(i.e we pruned the correct partition) by running the query and we get
the expected 1 row after having inserted 16 rows, one for each
combination of quals to test.
I wanted to come up with some tests that test for multiple quals
matching the same partition key. This is tricky due to the
equivalence class code being smart and removing any duplicates or
marking the rel as dummy when it finds conflicting quals. With hash
partitioning, we're limited to just equality quals, so maybe something
could be done with range-partitioned tables instead. I see there are
some tests just above the ones I modified which try to cover this.
I also tried to outsmart the planner by using Params and prepared
queries. Namely:
set plan_cache_mode = 'force_generic_plan';
prepare q1 (int, int, int, int, int, int, int, int) as select
tableoid::regclass,* from hp_prefix_test where a = $1 and b = $2 and c
= $3 and d = $4 and a = $5 and b = $6 and c = $7 and d = $8;
explain (costs off) execute q1 (1,2,3,4,1,2,3,4);
But I was outsmarted again with a gating qual which checked the pairs
match before doing the scan :-(
Append
Subplans Removed: 15
-> Result
One-Time Filter: (($1 = $5) AND ($2 = $6) AND ($3 = $7) AND ($4 = $8))
-> Seq Scan on hp_prefix_test_p14 hp_prefix_test_1
Filter: ((a = $5) AND (b = $6) AND (c = $7) AND (d = $8))
I'm aiming to commit these as two separate fixes, so I'm going to go
look again at the first one and wait to see if anyone wants to comment
on this patch in the meantime.
David
diff --git a/src/backend/partitioning/partprune.c
b/src/backend/partitioning/partprune.c
index 7179b22a05..5df1e59e66 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -2355,9 +2355,10 @@
match_clause_to_partition_key(GeneratePruningStepsContext *context,
*
* To generate steps, step_lastexpr and step_lastcmpfn are appended to
* expressions and cmpfns, respectively, extracted from the clauses in
- * 'prefix'. Actually, since 'prefix' may contain multiple clauses for the
- * same partition key column, we must generate steps for various combinations
- * of the clauses of different keys.
+ * 'prefix'. Since 'prefix' may contain multiple clauses for each partition
+ * key, and since each step can only contain a single clause for each
+ * partition key, when there are multiple clauses for any given key, must
+ * generate steps for all combinations of the clauses.
*
* For list/range partitioning, callers must ensure that step_nullkeys is
* NULL, and that prefix contains at least one clause for each of the
@@ -2397,7 +2398,7 @@ get_steps_using_prefix(GeneratePruningStepsContext
*context,
return list_make1(step);
}
- /* Recurse to generate steps for various combinations. */
+ /* Recurse to generate steps for every combination of clauses. */
return get_steps_using_prefix_recurse(context,
step_opstrategy,
step_op_is_ne,
@@ -2438,14 +2439,17 @@
get_steps_using_prefix_recurse(GeneratePruningStepsContext *context,
List *result = NIL;
ListCell *lc;
int cur_keyno;
+ int final_keyno;
/* Actually, recursion would be limited by PARTITION_MAX_KEYS. */
check_stack_depth();
- /* Check if we need to recurse. */
Assert(start != NULL);
cur_keyno = ((PartClauseInfo *) lfirst(start))->keyno;
- if (cur_keyno < step_lastkeyno - 1)
+ final_keyno = ((PartClauseInfo *) llast(prefix))->keyno;
+
+ /* Check if we need to recurse. */
+ if (cur_keyno < final_keyno)
{
PartClauseInfo *pc;
ListCell *next_start;
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 36791293ee..1bfdf37657 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4024,20 +4024,327 @@ explain (costs off) select * from rp_prefix_test3
where a >= 1 and b >= 1 and b
Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (b = 2) AND (c = 2))
(2 rows)
+drop table rp_prefix_test1;
+drop table rp_prefix_test2;
+drop table rp_prefix_test3;
create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a
part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d
part_test_int4_ops);
-create table hp_prefix_test_p1 partition of hp_prefix_test for values with
(modulus 2, remainder 0);
-create table hp_prefix_test_p2 partition of hp_prefix_test for values with
(modulus 2, remainder 1);
--- Test that get_steps_using_prefix() handles non-NULL step_nullkeys
-explain (costs off) select * from hp_prefix_test where a = 1 and b is null and
c = 1 and d = 1;
+-- create 16 partitions
+select 'create table hp_prefix_test_p' || x::text || ' partition of
hp_prefix_test for values with (modulus 16, remainder ' || x::text || ');'
+from generate_Series(0,15) x;
+ ?column?
+---------------------------------------------------------------------------------------------------------
+ create table hp_prefix_test_p0 partition of hp_prefix_test for values with
(modulus 16, remainder 0);
+ create table hp_prefix_test_p1 partition of hp_prefix_test for values with
(modulus 16, remainder 1);
+ create table hp_prefix_test_p2 partition of hp_prefix_test for values with
(modulus 16, remainder 2);
+ create table hp_prefix_test_p3 partition of hp_prefix_test for values with
(modulus 16, remainder 3);
+ create table hp_prefix_test_p4 partition of hp_prefix_test for values with
(modulus 16, remainder 4);
+ create table hp_prefix_test_p5 partition of hp_prefix_test for values with
(modulus 16, remainder 5);
+ create table hp_prefix_test_p6 partition of hp_prefix_test for values with
(modulus 16, remainder 6);
+ create table hp_prefix_test_p7 partition of hp_prefix_test for values with
(modulus 16, remainder 7);
+ create table hp_prefix_test_p8 partition of hp_prefix_test for values with
(modulus 16, remainder 8);
+ create table hp_prefix_test_p9 partition of hp_prefix_test for values with
(modulus 16, remainder 9);
+ create table hp_prefix_test_p10 partition of hp_prefix_test for values with
(modulus 16, remainder 10);
+ create table hp_prefix_test_p11 partition of hp_prefix_test for values with
(modulus 16, remainder 11);
+ create table hp_prefix_test_p12 partition of hp_prefix_test for values with
(modulus 16, remainder 12);
+ create table hp_prefix_test_p13 partition of hp_prefix_test for values with
(modulus 16, remainder 13);
+ create table hp_prefix_test_p14 partition of hp_prefix_test for values with
(modulus 16, remainder 14);
+ create table hp_prefix_test_p15 partition of hp_prefix_test for values with
(modulus 16, remainder 15);
+(16 rows)
+
+\gexec
+create table hp_prefix_test_p0 partition of hp_prefix_test for values with
(modulus 16, remainder 0);
+create table hp_prefix_test_p1 partition of hp_prefix_test for values with
(modulus 16, remainder 1);
+create table hp_prefix_test_p2 partition of hp_prefix_test for values with
(modulus 16, remainder 2);
+create table hp_prefix_test_p3 partition of hp_prefix_test for values with
(modulus 16, remainder 3);
+create table hp_prefix_test_p4 partition of hp_prefix_test for values with
(modulus 16, remainder 4);
+create table hp_prefix_test_p5 partition of hp_prefix_test for values with
(modulus 16, remainder 5);
+create table hp_prefix_test_p6 partition of hp_prefix_test for values with
(modulus 16, remainder 6);
+create table hp_prefix_test_p7 partition of hp_prefix_test for values with
(modulus 16, remainder 7);
+create table hp_prefix_test_p8 partition of hp_prefix_test for values with
(modulus 16, remainder 8);
+create table hp_prefix_test_p9 partition of hp_prefix_test for values with
(modulus 16, remainder 9);
+create table hp_prefix_test_p10 partition of hp_prefix_test for values with
(modulus 16, remainder 10);
+create table hp_prefix_test_p11 partition of hp_prefix_test for values with
(modulus 16, remainder 11);
+create table hp_prefix_test_p12 partition of hp_prefix_test for values with
(modulus 16, remainder 12);
+create table hp_prefix_test_p13 partition of hp_prefix_test for values with
(modulus 16, remainder 13);
+create table hp_prefix_test_p14 partition of hp_prefix_test for values with
(modulus 16, remainder 14);
+create table hp_prefix_test_p15 partition of hp_prefix_test for values with
(modulus 16, remainder 15);
+-- insert one row for each test to perform.
+insert into hp_prefix_test
+select
+ case a when 0 then null else 1 end,
+ case b when 0 then null else 2 end,
+ case c when 0 then null else 3 end,
+ case d when 0 then null else 4 end
+from
+ generate_series(0,1) a,
+ generate_series(0,1) b,
+ generate_Series(0,1) c,
+ generate_Series(0,1) d;
+-- Ensure partition pruning works correctly for each combination of IS NULL
+-- and equality quals.
+select
+ 'explain (costs off) select tableoid::regclass,* from hp_prefix_test where '
||
+ string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null'
else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
+from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos),
generate_Series(0,15) g(s)
+group by g.s
+order by g.s;
+ ?column?
+-------------------------------------------------------------------------------------------------------------------------------
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b is null and c is null and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c is null and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b = 2 and c is null and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c is null and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b is null and c = 3 and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c = 3 and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b = 2 and c = 3 and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c = 3 and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b is null and c is null and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c is null and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b = 2 and c is null and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c is null and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b is null and c = 3 and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c = 3 and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b = 2 and c = 3 and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c = 3 and d = 4
+(16 rows)
+
+\gexec
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b is null and c is null and d is null
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p0 hp_prefix_test
+ Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d IS NULL))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c is null and d is null
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p1 hp_prefix_test
+ Filter: ((b IS NULL) AND (c IS NULL) AND (d IS NULL) AND (a = 1))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b = 2 and c is null and d is null
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p2 hp_prefix_test
+ Filter: ((a IS NULL) AND (c IS NULL) AND (d IS NULL) AND (b = 2))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c is null and d is null
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p12 hp_prefix_test
+ Filter: ((c IS NULL) AND (d IS NULL) AND (a = 1) AND (b = 2))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b is null and c = 3 and d is null
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p3 hp_prefix_test
+ Filter: ((a IS NULL) AND (b IS NULL) AND (d IS NULL) AND (c = 3))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c = 3 and d is null
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p15 hp_prefix_test
+ Filter: ((b IS NULL) AND (d IS NULL) AND (a = 1) AND (c = 3))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b = 2 and c = 3 and d is null
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p12 hp_prefix_test
+ Filter: ((a IS NULL) AND (d IS NULL) AND (b = 2) AND (c = 3))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c = 3 and d is null
QUERY PLAN
-------------------------------------------------------------
- Seq Scan on hp_prefix_test_p1 hp_prefix_test
- Filter: ((b IS NULL) AND (a = 1) AND (c = 1) AND (d = 1))
+ Seq Scan on hp_prefix_test_p5 hp_prefix_test
+ Filter: ((d IS NULL) AND (a = 1) AND (b = 2) AND (c = 3))
(2 rows)
-drop table rp_prefix_test1;
-drop table rp_prefix_test2;
-drop table rp_prefix_test3;
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b is null and c is null and d = 4
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p4 hp_prefix_test
+ Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c is null and d = 4
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p14 hp_prefix_test
+ Filter: ((b IS NULL) AND (c IS NULL) AND (a = 1) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b = 2 and c is null and d = 4
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p13 hp_prefix_test
+ Filter: ((a IS NULL) AND (c IS NULL) AND (b = 2) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c is null and d = 4
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p6 hp_prefix_test
+ Filter: ((c IS NULL) AND (a = 1) AND (b = 2) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b is null and c = 3 and d = 4
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p12 hp_prefix_test
+ Filter: ((a IS NULL) AND (b IS NULL) AND (c = 3) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c = 3 and d = 4
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p5 hp_prefix_test
+ Filter: ((b IS NULL) AND (a = 1) AND (c = 3) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b = 2 and c = 3 and d = 4
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p6 hp_prefix_test
+ Filter: ((a IS NULL) AND (b = 2) AND (c = 3) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c = 3 and d = 4
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on hp_prefix_test_p4 hp_prefix_test
+ Filter: ((a = 1) AND (b = 2) AND (c = 3) AND (d = 4))
+(2 rows)
+
+-- And ensure we get exactly 1 row from each.
+select
+ 'select tableoid::regclass,* from hp_prefix_test where ' ||
+ string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null'
else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
+from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos),
generate_Series(0,15) g(s)
+group by g.s
+order by g.s;
+ ?column?
+-----------------------------------------------------------------------------------------------------------
+ select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c is null and d is null
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c is null and d is null
+ select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c is null and d is null
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c
is null and d is null
+ select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c = 3 and d is null
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c = 3 and d is null
+ select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c = 3 and d is null
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c =
3 and d is null
+ select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c is null and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c is null and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c is null and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c
is null and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c = 3 and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c = 3 and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c = 3 and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c =
3 and d = 4
+(16 rows)
+
+\gexec
+select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c is null and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p0 | | | |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c is null and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p1 | 1 | | |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c is null and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p2 | | 2 | |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is
null and d is null
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p12 | 1 | 2 | |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c = 3 and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p3 | | | 3 |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c = 3 and d is null
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p15 | 1 | | 3 |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c = 3 and d is null
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p12 | | 2 | 3 |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c =
3 and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p5 | 1 | 2 | 3 |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c is null and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p4 | | | | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c is null and d = 4
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p14 | 1 | | | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c is null and d = 4
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p13 | | 2 | | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is
null and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p6 | 1 | 2 | | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c = 3 and d = 4
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p12 | | | 3 | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c = 3 and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p5 | 1 | | 3 | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c = 3 and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p6 | | 2 | 3 | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c =
3 and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p4 | 1 | 2 | 3 | 4
+(1 row)
+
drop table hp_prefix_test;
--
-- Check that gen_partprune_steps() detects self-contradiction from clauses
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index d23133fe43..6b4039179f 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -1188,16 +1188,49 @@ explain (costs off) select * from rp_prefix_test3 where
a >= 1 and b >= 1 and b
-- that the caller arranges clauses in that prefix in the required order)
explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and
b = 2 and c = 2 and d >= 0;
-create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a
part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d
part_test_int4_ops);
-create table hp_prefix_test_p1 partition of hp_prefix_test for values with
(modulus 2, remainder 0);
-create table hp_prefix_test_p2 partition of hp_prefix_test for values with
(modulus 2, remainder 1);
-
--- Test that get_steps_using_prefix() handles non-NULL step_nullkeys
-explain (costs off) select * from hp_prefix_test where a = 1 and b is null and
c = 1 and d = 1;
-
drop table rp_prefix_test1;
drop table rp_prefix_test2;
drop table rp_prefix_test3;
+
+create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a
part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d
part_test_int4_ops);
+
+-- create 16 partitions
+select 'create table hp_prefix_test_p' || x::text || ' partition of
hp_prefix_test for values with (modulus 16, remainder ' || x::text || ');'
+from generate_Series(0,15) x;
+\gexec
+
+-- insert one row for each test to perform.
+insert into hp_prefix_test
+select
+ case a when 0 then null else 1 end,
+ case b when 0 then null else 2 end,
+ case c when 0 then null else 3 end,
+ case d when 0 then null else 4 end
+from
+ generate_series(0,1) a,
+ generate_series(0,1) b,
+ generate_Series(0,1) c,
+ generate_Series(0,1) d;
+
+-- Ensure partition pruning works correctly for each combination of IS NULL
+-- and equality quals.
+select
+ 'explain (costs off) select tableoid::regclass,* from hp_prefix_test where '
||
+ string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null'
else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
+from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos),
generate_Series(0,15) g(s)
+group by g.s
+order by g.s;
+\gexec
+
+-- And ensure we get exactly 1 row from each.
+select
+ 'select tableoid::regclass,* from hp_prefix_test where ' ||
+ string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null'
else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
+from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos),
generate_Series(0,15) g(s)
+group by g.s
+order by g.s;
+\gexec
+
drop table hp_prefix_test;
--
