summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/optimizer/prep/prepunion.c63
-rw-r--r--src/test/regress/expected/union.out86
-rw-r--r--src/test/regress/sql/union.sql38
3 files changed, 185 insertions, 2 deletions
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index da9431108a2..f5197779684 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -1185,6 +1185,69 @@ generate_nonunion_paths(SetOperationStmt *op, PlannerInfo *root,
result_rel->reltarget = create_setop_pathtarget(root, tlist,
list_make2(lpath, rpath));
+ /* Check for provably empty setop inputs and add short-circuit paths. */
+ if (op->op == SETOP_EXCEPT)
+ {
+ /*
+ * For EXCEPTs, if the left side is dummy then there's no need to
+ * inspect the right-hand side as scanning the right to find tuples to
+ * remove won't make the left-hand input any more empty.
+ */
+ if (is_dummy_rel(lrel))
+ {
+ mark_dummy_rel(result_rel);
+
+ return result_rel;
+ }
+
+ /* Handle EXCEPTs with dummy right input */
+ if (is_dummy_rel(rrel))
+ {
+ if (op->all)
+ {
+ Path *apath;
+
+ /*
+ * EXCEPT ALL: If the right-hand input is dummy then we can
+ * simply scan the left-hand input. To keep createplan.c
+ * happy, use a single child Append to handle the translation
+ * between the set op targetlist and the targetlist of the
+ * left input. The Append will be removed in setrefs.c.
+ */
+ apath = (Path *) create_append_path(root, result_rel, list_make1(lpath),
+ NIL, NIL, NULL, 0, false, -1);
+
+ add_path(result_rel, apath);
+
+ return result_rel;
+ }
+ else
+ {
+ /*
+ * To make EXCEPT with a dummy RHS work means having to
+ * deduplicate the left input. That could be done with
+ * AggPaths, but it doesn't seem worth the effort. Let the
+ * normal path generation code below handle this one.
+ */
+ }
+ }
+ }
+ else
+ {
+ /*
+ * For INTERSECT, if either input is a dummy rel then we can mark the
+ * result_rel as dummy since intersecting with an empty relation can
+ * never yield any results. This is true regardless of INTERSECT or
+ * INTERSECT ALL.
+ */
+ if (is_dummy_rel(lrel) || is_dummy_rel(rrel))
+ {
+ mark_dummy_rel(result_rel);
+
+ return result_rel;
+ }
+ }
+
/*
* Estimate number of distinct groups that we'll need hashtable entries
* for; this is the size of the left-hand input for EXCEPT, or the smaller
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 15931beea3a..fb77d108337 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -1217,7 +1217,7 @@ select event_id
drop table events_child, events, other_events;
reset enable_indexonlyscan;
--
--- Test handling of UNION with provably empty inputs
+-- Test handling of UNION / EXCEPT / INTERSECT with provably empty inputs
--
-- Ensure the empty UNION input is pruned and de-duplication is done for the
-- remaining relation.
@@ -1271,6 +1271,90 @@ ORDER BY 1;
One-Time Filter: false
(7 rows)
+-- Ensure the planner provides a const-false Result node
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT two FROM tenk1 WHERE 1=2
+INTERSECT
+SELECT four FROM tenk1
+ORDER BY 1;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Sort
+ Output: unnamed_subquery.two
+ Sort Key: unnamed_subquery.two
+ -> Result
+ Output: unnamed_subquery.two
+ Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
+ One-Time Filter: false
+(7 rows)
+
+-- As above, with the inputs swapped
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT four FROM tenk1
+INTERSECT
+SELECT two FROM tenk1 WHERE 1=2
+ORDER BY 1;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Sort
+ Output: unnamed_subquery.four
+ Sort Key: unnamed_subquery.four
+ -> Result
+ Output: unnamed_subquery.four
+ Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
+ One-Time Filter: false
+(7 rows)
+
+-- Try with both inputs dummy
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT four FROM tenk1 WHERE 1=2
+INTERSECT
+SELECT two FROM tenk1 WHERE 1=2
+ORDER BY 1;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Sort
+ Output: unnamed_subquery.four
+ Sort Key: unnamed_subquery.four
+ -> Result
+ Output: unnamed_subquery.four
+ Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
+ One-Time Filter: false
+(7 rows)
+
+-- Ensure the planner provides a const-false Result node when the left input
+-- is empty
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT two FROM tenk1 WHERE 1=2
+EXCEPT
+SELECT four FROM tenk1
+ORDER BY 1;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Sort
+ Output: unnamed_subquery.two
+ Sort Key: unnamed_subquery.two
+ -> Result
+ Output: unnamed_subquery.two
+ Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
+ One-Time Filter: false
+(7 rows)
+
+-- Ensure the planner only scans the left input when right input is empty
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT two FROM tenk1
+EXCEPT ALL
+SELECT four FROM tenk1 WHERE 1=2
+ORDER BY 1;
+ QUERY PLAN
+--------------------------------
+ Sort
+ Output: tenk1.two
+ Sort Key: tenk1.two
+ -> Seq Scan on public.tenk1
+ Output: tenk1.two
+(5 rows)
+
-- Test constraint exclusion of UNION ALL subqueries
explain (costs off)
SELECT * FROM
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index e252316f69b..782cca23701 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -460,7 +460,7 @@ drop table events_child, events, other_events;
reset enable_indexonlyscan;
--
--- Test handling of UNION with provably empty inputs
+-- Test handling of UNION / EXCEPT / INTERSECT with provably empty inputs
--
-- Ensure the empty UNION input is pruned and de-duplication is done for the
@@ -487,6 +487,42 @@ UNION
SELECT ten FROM tenk1 WHERE 1=2
ORDER BY 1;
+-- Ensure the planner provides a const-false Result node
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT two FROM tenk1 WHERE 1=2
+INTERSECT
+SELECT four FROM tenk1
+ORDER BY 1;
+
+-- As above, with the inputs swapped
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT four FROM tenk1
+INTERSECT
+SELECT two FROM tenk1 WHERE 1=2
+ORDER BY 1;
+
+-- Try with both inputs dummy
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT four FROM tenk1 WHERE 1=2
+INTERSECT
+SELECT two FROM tenk1 WHERE 1=2
+ORDER BY 1;
+
+-- Ensure the planner provides a const-false Result node when the left input
+-- is empty
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT two FROM tenk1 WHERE 1=2
+EXCEPT
+SELECT four FROM tenk1
+ORDER BY 1;
+
+-- Ensure the planner only scans the left input when right input is empty
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT two FROM tenk1
+EXCEPT ALL
+SELECT four FROM tenk1 WHERE 1=2
+ORDER BY 1;
+
-- Test constraint exclusion of UNION ALL subqueries
explain (costs off)
SELECT * FROM
generated by cgit v1.2.3 (git 2.46.0) at 2025-10-10 15:19:52 +0000