Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9c9d41a

Browse files
committed
Teach planner to short-circuit EXCEPT/INTERSECT with dummy inputs
When either inputs of an INTERSECT [ALL] operator are proven not to returnany results (a dummy rel), then mark the entire INTERSECT operation asdummy.Likewise, if an EXCEPT [ALL] operation's left input is proven empty, thenmark the entire operation as dummy.With EXCEPT ALL, we can easily handle the right input being dummy aswe can return the left input without any processing. That can lead tosignificant performance gains during query execution. We can't easilyhandle dummy right inputs for EXCEPT (without ALL), as that would requirededuplication of the left input. Wiring up those Paths is likely morecomplex 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 <dgrowleyml@gmail.com>Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://postgr.es/m/CAApHDvri53PPF76c3M94_QNWbJfXjyCnjXuj_2=LYM-0m8WZtw@mail.gmail.com
1 parent928df06 commit9c9d41a

File tree

3 files changed

+185
-2
lines changed

3 files changed

+185
-2
lines changed

‎src/backend/optimizer/prep/prepunion.c‎

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1185,6 +1185,69 @@ generate_nonunion_paths(SetOperationStmt *op, PlannerInfo *root,
11851185
result_rel->reltarget=create_setop_pathtarget(root,tlist,
11861186
list_make2(lpath,rpath));
11871187

1188+
/* Check for provably empty setop inputs and add short-circuit paths. */
1189+
if (op->op==SETOP_EXCEPT)
1190+
{
1191+
/*
1192+
* For EXCEPTs, if the left side is dummy then there's no need to
1193+
* inspect the right-hand side as scanning the right to find tuples to
1194+
* remove won't make the left-hand input any more empty.
1195+
*/
1196+
if (is_dummy_rel(lrel))
1197+
{
1198+
mark_dummy_rel(result_rel);
1199+
1200+
returnresult_rel;
1201+
}
1202+
1203+
/* Handle EXCEPTs with dummy right input */
1204+
if (is_dummy_rel(rrel))
1205+
{
1206+
if (op->all)
1207+
{
1208+
Path*apath;
1209+
1210+
/*
1211+
* EXCEPT ALL: If the right-hand input is dummy then we can
1212+
* simply scan the left-hand input. To keep createplan.c
1213+
* happy, use a single child Append to handle the translation
1214+
* between the set op targetlist and the targetlist of the
1215+
* left input. The Append will be removed in setrefs.c.
1216+
*/
1217+
apath= (Path*)create_append_path(root,result_rel,list_make1(lpath),
1218+
NIL,NIL,NULL,0, false,-1);
1219+
1220+
add_path(result_rel,apath);
1221+
1222+
returnresult_rel;
1223+
}
1224+
else
1225+
{
1226+
/*
1227+
* To make EXCEPT with a dummy RHS work means having to
1228+
* deduplicate the left input. That could be done with
1229+
* AggPaths, but it doesn't seem worth the effort. Let the
1230+
* normal path generation code below handle this one.
1231+
*/
1232+
}
1233+
}
1234+
}
1235+
else
1236+
{
1237+
/*
1238+
* For INTERSECT, if either input is a dummy rel then we can mark the
1239+
* result_rel as dummy since intersecting with an empty relation can
1240+
* never yield any results. This is true regardless of INTERSECT or
1241+
* INTERSECT ALL.
1242+
*/
1243+
if (is_dummy_rel(lrel)||is_dummy_rel(rrel))
1244+
{
1245+
mark_dummy_rel(result_rel);
1246+
1247+
returnresult_rel;
1248+
}
1249+
}
1250+
11881251
/*
11891252
* Estimate number of distinct groups that we'll need hashtable entries
11901253
* for; this is the size of the left-hand input for EXCEPT, or the smaller

‎src/test/regress/expected/union.out‎

Lines changed: 85 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1217,7 +1217,7 @@ select event_id
12171217
drop table events_child, events, other_events;
12181218
reset enable_indexonlyscan;
12191219
--
1220-
-- Test handling of UNION with provably empty inputs
1220+
-- Test handling of UNION/ EXCEPT / INTERSECTwith provably empty inputs
12211221
--
12221222
-- Ensure the empty UNION input is pruned and de-duplication is done for the
12231223
-- remaining relation.
@@ -1271,6 +1271,90 @@ ORDER BY 1;
12711271
One-Time Filter: false
12721272
(7 rows)
12731273

1274+
-- Ensure the planner provides a const-false Result node
1275+
EXPLAIN (COSTS OFF, VERBOSE)
1276+
SELECT two FROM tenk1 WHERE 1=2
1277+
INTERSECT
1278+
SELECT four FROM tenk1
1279+
ORDER BY 1;
1280+
QUERY PLAN
1281+
---------------------------------------------------------------------
1282+
Sort
1283+
Output: unnamed_subquery.two
1284+
Sort Key: unnamed_subquery.two
1285+
-> Result
1286+
Output: unnamed_subquery.two
1287+
Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
1288+
One-Time Filter: false
1289+
(7 rows)
1290+
1291+
-- As above, with the inputs swapped
1292+
EXPLAIN (COSTS OFF, VERBOSE)
1293+
SELECT four FROM tenk1
1294+
INTERSECT
1295+
SELECT two FROM tenk1 WHERE 1=2
1296+
ORDER BY 1;
1297+
QUERY PLAN
1298+
---------------------------------------------------------------------
1299+
Sort
1300+
Output: unnamed_subquery.four
1301+
Sort Key: unnamed_subquery.four
1302+
-> Result
1303+
Output: unnamed_subquery.four
1304+
Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
1305+
One-Time Filter: false
1306+
(7 rows)
1307+
1308+
-- Try with both inputs dummy
1309+
EXPLAIN (COSTS OFF, VERBOSE)
1310+
SELECT four FROM tenk1 WHERE 1=2
1311+
INTERSECT
1312+
SELECT two FROM tenk1 WHERE 1=2
1313+
ORDER BY 1;
1314+
QUERY PLAN
1315+
---------------------------------------------------------------------
1316+
Sort
1317+
Output: unnamed_subquery.four
1318+
Sort Key: unnamed_subquery.four
1319+
-> Result
1320+
Output: unnamed_subquery.four
1321+
Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
1322+
One-Time Filter: false
1323+
(7 rows)
1324+
1325+
-- Ensure the planner provides a const-false Result node when the left input
1326+
-- is empty
1327+
EXPLAIN (COSTS OFF, VERBOSE)
1328+
SELECT two FROM tenk1 WHERE 1=2
1329+
EXCEPT
1330+
SELECT four FROM tenk1
1331+
ORDER BY 1;
1332+
QUERY PLAN
1333+
---------------------------------------------------------------------
1334+
Sort
1335+
Output: unnamed_subquery.two
1336+
Sort Key: unnamed_subquery.two
1337+
-> Result
1338+
Output: unnamed_subquery.two
1339+
Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
1340+
One-Time Filter: false
1341+
(7 rows)
1342+
1343+
-- Ensure the planner only scans the left input when right input is empty
1344+
EXPLAIN (COSTS OFF, VERBOSE)
1345+
SELECT two FROM tenk1
1346+
EXCEPT ALL
1347+
SELECT four FROM tenk1 WHERE 1=2
1348+
ORDER BY 1;
1349+
QUERY PLAN
1350+
--------------------------------
1351+
Sort
1352+
Output: tenk1.two
1353+
Sort Key: tenk1.two
1354+
-> Seq Scan on public.tenk1
1355+
Output: tenk1.two
1356+
(5 rows)
1357+
12741358
-- Test constraint exclusion of UNION ALL subqueries
12751359
explain (costs off)
12761360
SELECT * FROM

‎src/test/regress/sql/union.sql‎

Lines changed: 37 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -460,7 +460,7 @@ drop table events_child, events, other_events;
460460
reset enable_indexonlyscan;
461461

462462
--
463-
-- Test handling of UNION with provably empty inputs
463+
-- Test handling of UNION/ EXCEPT / INTERSECTwith provably empty inputs
464464
--
465465

466466
-- Ensure the empty UNION input is pruned and de-duplication is done for the
@@ -487,6 +487,42 @@ UNION
487487
SELECT tenFROM tenk1WHERE1=2
488488
ORDER BY1;
489489

490+
-- Ensure the planner provides a const-false Result node
491+
EXPLAIN (COSTS OFF, VERBOSE)
492+
SELECT twoFROM tenk1WHERE1=2
493+
INTERSECT
494+
SELECT fourFROM tenk1
495+
ORDER BY1;
496+
497+
-- As above, with the inputs swapped
498+
EXPLAIN (COSTS OFF, VERBOSE)
499+
SELECT fourFROM tenk1
500+
INTERSECT
501+
SELECT twoFROM tenk1WHERE1=2
502+
ORDER BY1;
503+
504+
-- Try with both inputs dummy
505+
EXPLAIN (COSTS OFF, VERBOSE)
506+
SELECT fourFROM tenk1WHERE1=2
507+
INTERSECT
508+
SELECT twoFROM tenk1WHERE1=2
509+
ORDER BY1;
510+
511+
-- Ensure the planner provides a const-false Result node when the left input
512+
-- is empty
513+
EXPLAIN (COSTS OFF, VERBOSE)
514+
SELECT twoFROM tenk1WHERE1=2
515+
EXCEPT
516+
SELECT fourFROM tenk1
517+
ORDER BY1;
518+
519+
-- Ensure the planner only scans the left input when right input is empty
520+
EXPLAIN (COSTS OFF, VERBOSE)
521+
SELECT twoFROM tenk1
522+
EXCEPT ALL
523+
SELECT fourFROM tenk1WHERE1=2
524+
ORDER BY1;
525+
490526
-- Test constraint exclusion of UNION ALL subqueries
491527
explain (costs off)
492528
SELECT*FROM

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp