From 9c9d41af4db7e7f81d0f9abc7dc16402386091b0 Mon Sep 17 00:00:00 2001 From: David Rowley Date: Tue, 7 Oct 2025 17:17:52 +1300 Subject: Teach planner to short-circuit EXCEPT/INTERSECT with dummy inputs When either inputs of an INTERSECT [ALL] operator are proven not to return any results (a dummy rel), then mark the entire INTERSECT operation as dummy. Likewise, if an EXCEPT [ALL] operation's left input is proven empty, then mark the entire operation as dummy. With EXCEPT ALL, we can easily handle the right input being dummy as we can return the left input without any processing. That can lead to significant performance gains during query execution. We can't easily handle dummy right inputs for EXCEPT (without ALL), as that would require deduplication of the left input. Wiring up those Paths is likely more complex than it's worth as the gains during execution aren't that great, so let's leave that one to be handled by the normal Path generation code. Author: David Rowley Reviewed-by: Tom Lane Discussion: https://postgr.es/m/CAApHDvri53PPF76c3M94_QNWbJfXjyCnjXuj_2=LYM-0m8WZtw@mail.gmail.com --- src/test/regress/expected/union.out | 86 ++++++++++++++++++++++++++++++++++++- src/test/regress/sql/union.sql | 38 +++++++++++++++- 2 files changed, 122 insertions(+), 2 deletions(-) (limited to 'src/test') 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 -- cgit v1.2.3