diff options
author | David Rowley <drowley@postgresql.org> | 2023-01-09 17:15:08 +1300 |
---|---|---|
committer | David Rowley <drowley@postgresql.org> | 2023-01-09 17:15:08 +1300 |
commit | 3c569049b7b502bb4952483d19ce622ff0af5fd6 (patch) | |
tree | 7ce433043ae025e93ed62e5a763d1ed01942f0c4 /src/test | |
parent | 216a784829c2c5f03ab0c43e009126cbb819e9b2 (diff) | |
download | postgresql-3c569049b7b502bb4952483d19ce622ff0af5fd6.tar.gz |
Allow left join removals and unique joins on partitioned tables
This allows left join removals and unique joins to work with partitioned
tables. The planner just lacked sufficient proofs that a given join
would not cause any row duplication. Unique indexes currently serve as
that proof, so have get_relation_info() populate the indexlist for
partitioned tables too.
Author: Arne Roland
Reviewed-by: Alvaro Herrera, Zhihong Yu, Amit Langote, David Rowley
Discussion: https://postgr.es/m/c3b2408b7a39433b8230bbcd02e9f302@index.de
Diffstat (limited to 'src/test')
-rw-r--r-- | src/test/regress/expected/join.out | 10 | ||||
-rw-r--r-- | src/test/regress/expected/partition_join.out | 4 | ||||
-rw-r--r-- | src/test/regress/sql/join.sql | 7 | ||||
-rw-r--r-- | src/test/regress/sql/partition_join.sql | 4 |
4 files changed, 21 insertions, 4 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 3ddea3b683..c2b85d2795 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4860,6 +4860,16 @@ select 1 from (select a.id FROM a left join b on a.b_id = b.id) q, Filter: (a.id = i) (4 rows) +CREATE TEMP TABLE parted_b (id int PRIMARY KEY) partition by range(id); +CREATE TEMP TABLE parted_b1 partition of parted_b for values from (0) to (10); +-- test join removals on a partitioned table +explain (costs off) +select a.* from a left join parted_b pb on a.b_id = pb.id; + QUERY PLAN +--------------- + Seq Scan on a +(1 row) + rollback; create temp table parent (k int primary key, pd int); create temp table child (k int unique, cd int); diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index c59caf1cb3..c649c4aeaa 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -4874,7 +4874,7 @@ ANALYZE fract_t; SET max_parallel_workers_per_gather = 0; SET enable_partitionwise_join = on; EXPLAIN (COSTS OFF) -SELECT * FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY id ASC LIMIT 10; +SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10; QUERY PLAN ----------------------------------------------------------------------- Limit @@ -4891,7 +4891,7 @@ SELECT * FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY id ASC LIMIT 10; (11 rows) EXPLAIN (COSTS OFF) -SELECT * FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY id DESC LIMIT 10; +SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DESC LIMIT 10; QUERY PLAN -------------------------------------------------------------------------------- Limit diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 9fc6ef4376..027927354c 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1709,6 +1709,13 @@ explain (costs off) select 1 from (select a.id FROM a left join b on a.b_id = b.id) q, lateral generate_series(1, q.id) gs(i) where q.id = gs.i; +CREATE TEMP TABLE parted_b (id int PRIMARY KEY) partition by range(id); +CREATE TEMP TABLE parted_b1 partition of parted_b for values from (0) to (10); + +-- test join removals on a partitioned table +explain (costs off) +select a.* from a left join parted_b pb on a.b_id = pb.id; + rollback; create temp table parent (k int primary key, pd int); diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index 67f506361f..9e16f1ca55 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -1157,10 +1157,10 @@ SET max_parallel_workers_per_gather = 0; SET enable_partitionwise_join = on; EXPLAIN (COSTS OFF) -SELECT * FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY id ASC LIMIT 10; +SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10; EXPLAIN (COSTS OFF) -SELECT * FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY id DESC LIMIT 10; +SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DESC LIMIT 10; -- cleanup DROP TABLE fract_t; |