Skip to content

Commit 4387cf9

Browse files
tglsfdctglsfdc
committed
Avoid inserting Result nodes that only compute identity projections.
The planner sometimes inserts Result nodes to perform column projections (ie, arbitrary scalar calculations) above plan nodes that lack projection logic of their own. However, we did that even if the lower plan node was in fact producing the required column set already; which is a pretty common case given the popularity of "SELECT * FROM ...". Measurements show that the useless plan node adds non-negligible overhead, especially when there are many columns in the result. So add a check to avoid inserting a Result node unless there's something useful for it to do. There are a couple of remaining places where unnecessary Result nodes could get inserted, but they are (a) much less performance-critical, and (b) coded in such a way that it's hard to avoid inserting a Result, because the desired tlist is changed on-the-fly in subsequent logic. We'll leave those alone for now. Kyotaro Horiguchi; reviewed and further hacked on by Amit Kapila and Tom Lane.
1 parent a5ff502 commit 4387cf9

File tree

7 files changed

+163
-128
lines changed

7 files changed

+163
-128
lines changed

‎src/backend/optimizer/plan/createplan.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -937,10 +937,12 @@ create_unique_plan(PlannerInfo *root, UniquePath *best_path)
937937
if (newitems || best_path->umethod == UNIQUE_PATH_SORT)
938938
{
939939
/*
940-
* If the top plan node can't do projections, we need to add a Result
941-
* node to help it along.
940+
* If the top plan node can't do projections and its existing target
941+
* list isn't already what we need, we need to add a Result node to
942+
* help it along.
942943
*/
943-
if (!is_projection_capable_plan(subplan))
944+
if (!is_projection_capable_plan(subplan) &&
945+
!tlist_same_exprs(newtlist, subplan->targetlist))
944946
subplan = (Plan *) make_result(root, newtlist, NULL, subplan);
945947
else
946948
subplan->targetlist = newtlist;

‎src/backend/optimizer/plan/planner.c

Lines changed: 11 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1379,10 +1379,12 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
13791379
{
13801380
/*
13811381
* If the top-level plan node is one that cannot do expression
1382-
* evaluation, we must insert a Result node to project the
1382+
* evaluation and its existing target list isn't already what
1383+
* we need, we must insert a Result node to project the
13831384
* desired tlist.
13841385
*/
1385-
if (!is_projection_capable_plan(result_plan))
1386+
if (!is_projection_capable_plan(result_plan) &&
1387+
!tlist_same_exprs(sub_tlist, result_plan->targetlist))
13861388
{
13871389
result_plan = (Plan *) make_result(root,
13881390
sub_tlist,
@@ -1542,10 +1544,13 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
15421544
* If the top-level plan node is one that cannot do expression
15431545
* evaluation, we must insert a Result node to project the desired
15441546
* tlist. (In some cases this might not really be required, but
1545-
* it's not worth trying to avoid it.) Note that on second and
1546-
* subsequent passes through the following loop, the top-level
1547-
* node will be a WindowAgg which we know can project; so we only
1548-
* need to check once.
1547+
* it's not worth trying to avoid it. In particular, think not to
1548+
* skip adding the Result if the initial window_tlist matches the
1549+
* top-level plan node's output, because we might change the tlist
1550+
* inside the following loop.) Note that on second and subsequent
1551+
* passes through the following loop, the top-level node will be a
1552+
* WindowAgg which we know can project; so we only need to check
1553+
* once.
15491554
*/
15501555
if (!is_projection_capable_plan(result_plan))
15511556
{

‎src/backend/optimizer/util/tlist.c

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -158,6 +158,43 @@ get_tlist_exprs(List *tlist, bool includeJunk)
158158
}
159159

160160

161+
/*
162+
* tlist_same_exprs
163+
* Check whether two target lists contain the same expressions
164+
*
165+
* Note: this function is used to decide whether it's safe to jam a new tlist
166+
* into a non-projection-capable plan node. Obviously we can't do that unless
167+
* the node's tlist shows it already returns the column values we want.
168+
* However, we can ignore the TargetEntry attributes resname, ressortgroupref,
169+
* resorigtbl, resorigcol, and resjunk, because those are only labelings that
170+
* don't affect the row values computed by the node. (Moreover, if we didn't
171+
* ignore them, we'd frequently fail to make the desired optimization, since
172+
* the planner tends to not bother to make resname etc. valid in intermediate
173+
* plan nodes.) Note that on success, the caller must still jam the desired
174+
* tlist into the plan node, else it won't have the desired labeling fields.
175+
*/
176+
bool
177+
tlist_same_exprs(List *tlist1, List *tlist2)
178+
{
179+
ListCell *lc1,
180+
*lc2;
181+
182+
if (list_length(tlist1) != list_length(tlist2))
183+
return false; /* not same length, so can't match */
184+
185+
forboth(lc1, tlist1, lc2, tlist2)
186+
{
187+
TargetEntry *tle1 = (TargetEntry *) lfirst(lc1);
188+
TargetEntry *tle2 = (TargetEntry *) lfirst(lc2);
189+
190+
if (!equal(tle1->expr, tle2->expr))
191+
return false;
192+
}
193+
194+
return true;
195+
}
196+
197+
161198
/*
162199
* Does tlist have same output datatypes as listed in colTypes?
163200
*

‎src/include/optimizer/tlist.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,9 @@ extern List *flatten_tlist(List *tlist, PVCAggregateBehavior aggbehavior,
2525
extern List *add_to_flat_tlist(List *tlist, List *exprs);
2626

2727
extern List *get_tlist_exprs(List *tlist, bool includeJunk);
28+
29+
extern bool tlist_same_exprs(List *tlist1, List *tlist2);
30+
2831
extern bool tlist_same_datatypes(List *tlist, List *colTypes, bool junkOK);
2932
extern bool tlist_same_collations(List *tlist, List *colCollations, bool junkOK);
3033

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

Lines changed: 42 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -385,61 +385,57 @@ create table nv_child_2011 () inherits (nv_parent);
385385
alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
386386
alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
387387
explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
388-
QUERY PLAN
389-
---------------------------------------------------------------------------------
390-
Result
391-
-> Append
392-
-> Seq Scan on nv_parent
393-
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
394-
-> Seq Scan on nv_child_2010
395-
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
396-
-> Seq Scan on nv_child_2011
397-
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
398-
(8 rows)
388+
QUERY PLAN
389+
---------------------------------------------------------------------------
390+
Append
391+
-> Seq Scan on nv_parent
392+
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
393+
-> Seq Scan on nv_child_2010
394+
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
395+
-> Seq Scan on nv_child_2011
396+
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
397+
(7 rows)
399398

400399
create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
401400
explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
402-
QUERY PLAN
403-
---------------------------------------------------------------------------------
404-
Result
405-
-> Append
406-
-> Seq Scan on nv_parent
407-
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
408-
-> Seq Scan on nv_child_2010
409-
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
410-
-> Seq Scan on nv_child_2011
411-
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
412-
(8 rows)
401+
QUERY PLAN
402+
---------------------------------------------------------------------------
403+
Append
404+
-> Seq Scan on nv_parent
405+
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
406+
-> Seq Scan on nv_child_2010
407+
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
408+
-> Seq Scan on nv_child_2011
409+
Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
410+
(7 rows)
413411

414412
explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
415-
QUERY PLAN
416-
---------------------------------------------------------------------------------
417-
Result
418-
-> Append
419-
-> Seq Scan on nv_parent
420-
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
421-
-> Seq Scan on nv_child_2010
422-
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
423-
-> Seq Scan on nv_child_2011
424-
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
425-
-> Seq Scan on nv_child_2009
426-
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
427-
(10 rows)
413+
QUERY PLAN
414+
---------------------------------------------------------------------------
415+
Append
416+
-> Seq Scan on nv_parent
417+
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
418+
-> Seq Scan on nv_child_2010
419+
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
420+
-> Seq Scan on nv_child_2011
421+
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
422+
-> Seq Scan on nv_child_2009
423+
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
424+
(9 rows)
428425

429426
-- after validation, the constraint should be used
430427
alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
431428
explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
432-
QUERY PLAN
433-
---------------------------------------------------------------------------------
434-
Result
435-
-> Append
436-
-> Seq Scan on nv_parent
437-
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
438-
-> Seq Scan on nv_child_2010
439-
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
440-
-> Seq Scan on nv_child_2009
441-
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
442-
(8 rows)
429+
QUERY PLAN
430+
---------------------------------------------------------------------------
431+
Append
432+
-> Seq Scan on nv_parent
433+
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
434+
-> Seq Scan on nv_child_2010
435+
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
436+
-> Seq Scan on nv_child_2009
437+
Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
438+
(7 rows)
443439

444440
-- Foreign key adding test with mixed types
445441
-- Note: these tables are TEMP to avoid name conflicts when this test

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

Lines changed: 52 additions & 58 deletions
Original file line numberDiff line numberDiff line change
@@ -1210,24 +1210,22 @@ select * from matest0 order by 1-id;
12101210
reset enable_indexscan;
12111211
set enable_seqscan = off; -- plan with fewest seqscans should be merge
12121212
explain (verbose, costs off) select * from matest0 order by 1-id;
1213-
QUERY PLAN
1214-
------------------------------------------------------------------------
1215-
Result
1216-
Output: matest0.id, matest0.name, ((1 - matest0.id))
1217-
-> Merge Append
1218-
Sort Key: ((1 - matest0.id))
1219-
-> Index Scan using matest0i on public.matest0
1220-
Output: matest0.id, matest0.name, (1 - matest0.id)
1221-
-> Index Scan using matest1i on public.matest1
1222-
Output: matest1.id, matest1.name, (1 - matest1.id)
1223-
-> Sort
1224-
Output: matest2.id, matest2.name, ((1 - matest2.id))
1225-
Sort Key: ((1 - matest2.id))
1226-
-> Seq Scan on public.matest2
1227-
Output: matest2.id, matest2.name, (1 - matest2.id)
1228-
-> Index Scan using matest3i on public.matest3
1229-
Output: matest3.id, matest3.name, (1 - matest3.id)
1230-
(15 rows)
1213+
QUERY PLAN
1214+
------------------------------------------------------------------
1215+
Merge Append
1216+
Sort Key: ((1 - matest0.id))
1217+
-> Index Scan using matest0i on public.matest0
1218+
Output: matest0.id, matest0.name, (1 - matest0.id)
1219+
-> Index Scan using matest1i on public.matest1
1220+
Output: matest1.id, matest1.name, (1 - matest1.id)
1221+
-> Sort
1222+
Output: matest2.id, matest2.name, ((1 - matest2.id))
1223+
Sort Key: ((1 - matest2.id))
1224+
-> Seq Scan on public.matest2
1225+
Output: matest2.id, matest2.name, (1 - matest2.id)
1226+
-> Index Scan using matest3i on public.matest3
1227+
Output: matest3.id, matest3.name, (1 - matest3.id)
1228+
(13 rows)
12311229

12321230
select * from matest0 order by 1-id;
12331231
id | name
@@ -1258,48 +1256,45 @@ SELECT thousand, tenthous FROM tenk1
12581256
UNION ALL
12591257
SELECT thousand, thousand FROM tenk1
12601258
ORDER BY thousand, tenthous;
1261-
QUERY PLAN
1262-
-------------------------------------------------------------------------------
1263-
Result
1264-
-> Merge Append
1265-
Sort Key: tenk1.thousand, tenk1.tenthous
1266-
-> Index Only Scan using tenk1_thous_tenthous on tenk1
1267-
-> Sort
1268-
Sort Key: tenk1_1.thousand, tenk1_1.thousand
1269-
-> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1270-
(7 rows)
1259+
QUERY PLAN
1260+
-------------------------------------------------------------------------
1261+
Merge Append
1262+
Sort Key: tenk1.thousand, tenk1.tenthous
1263+
-> Index Only Scan using tenk1_thous_tenthous on tenk1
1264+
-> Sort
1265+
Sort Key: tenk1_1.thousand, tenk1_1.thousand
1266+
-> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1267+
(6 rows)
12711268

12721269
explain (costs off)
12731270
SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1
12741271
UNION ALL
12751272
SELECT 42, 42, hundred FROM tenk1
12761273
ORDER BY thousand, tenthous;
1277-
QUERY PLAN
1278-
------------------------------------------------------------------------
1279-
Result
1280-
-> Merge Append
1281-
Sort Key: tenk1.thousand, tenk1.tenthous
1282-
-> Index Only Scan using tenk1_thous_tenthous on tenk1
1283-
-> Sort
1284-
Sort Key: (42), (42)
1285-
-> Index Only Scan using tenk1_hundred on tenk1 tenk1_1
1286-
(7 rows)
1274+
QUERY PLAN
1275+
------------------------------------------------------------------
1276+
Merge Append
1277+
Sort Key: tenk1.thousand, tenk1.tenthous
1278+
-> Index Only Scan using tenk1_thous_tenthous on tenk1
1279+
-> Sort
1280+
Sort Key: (42), (42)
1281+
-> Index Only Scan using tenk1_hundred on tenk1 tenk1_1
1282+
(6 rows)
12871283

12881284
explain (costs off)
12891285
SELECT thousand, tenthous FROM tenk1
12901286
UNION ALL
12911287
SELECT thousand, random()::integer FROM tenk1
12921288
ORDER BY thousand, tenthous;
1293-
QUERY PLAN
1294-
-------------------------------------------------------------------------------
1295-
Result
1296-
-> Merge Append
1297-
Sort Key: tenk1.thousand, tenk1.tenthous
1298-
-> Index Only Scan using tenk1_thous_tenthous on tenk1
1299-
-> Sort
1300-
Sort Key: tenk1_1.thousand, ((random())::integer)
1301-
-> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1302-
(7 rows)
1289+
QUERY PLAN
1290+
-------------------------------------------------------------------------
1291+
Merge Append
1292+
Sort Key: tenk1.thousand, tenk1.tenthous
1293+
-> Index Only Scan using tenk1_thous_tenthous on tenk1
1294+
-> Sort
1295+
Sort Key: tenk1_1.thousand, ((random())::integer)
1296+
-> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1297+
(6 rows)
13031298

13041299
-- Check min/max aggregate optimization
13051300
explain (costs off)
@@ -1345,16 +1340,15 @@ SELECT x, y FROM
13451340
UNION ALL
13461341
SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s
13471342
ORDER BY x, y;
1348-
QUERY PLAN
1349-
-------------------------------------------------------------------
1350-
Result
1351-
-> Merge Append
1352-
Sort Key: a.thousand, a.tenthous
1353-
-> Index Only Scan using tenk1_thous_tenthous on tenk1 a
1354-
-> Sort
1355-
Sort Key: b.unique2, b.unique2
1356-
-> Index Only Scan using tenk1_unique2 on tenk1 b
1357-
(7 rows)
1343+
QUERY PLAN
1344+
-------------------------------------------------------------
1345+
Merge Append
1346+
Sort Key: a.thousand, a.tenthous
1347+
-> Index Only Scan using tenk1_thous_tenthous on tenk1 a
1348+
-> Sort
1349+
Sort Key: b.unique2, b.unique2
1350+
-> Index Only Scan using tenk1_unique2 on tenk1 b
1351+
(6 rows)
13581352

13591353
reset enable_seqscan;
13601354
reset enable_indexscan;

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

Lines changed: 13 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -474,15 +474,14 @@ explain (costs off)
474474
UNION ALL
475475
SELECT * FROM t2) t
476476
WHERE ab = 'ab';
477-
QUERY PLAN
478-
---------------------------------------------------
479-
Result
480-
-> Append
481-
-> Index Scan using t1_ab_idx on t1
482-
Index Cond: ((a || b) = 'ab'::text)
483-
-> Index Only Scan using t2_pkey on t2
484-
Index Cond: (ab = 'ab'::text)
485-
(6 rows)
477+
QUERY PLAN
478+
---------------------------------------------
479+
Append
480+
-> Index Scan using t1_ab_idx on t1
481+
Index Cond: ((a || b) = 'ab'::text)
482+
-> Index Only Scan using t2_pkey on t2
483+
Index Cond: (ab = 'ab'::text)
484+
(5 rows)
486485

487486
explain (costs off)
488487
SELECT * FROM
@@ -510,10 +509,9 @@ explain (costs off)
510509
UNION ALL
511510
SELECT 2 AS t, * FROM tenk1 b) c
512511
WHERE t = 2;
513-
QUERY PLAN
514-
---------------------------------
515-
Result
516-
-> Append
517-
-> Seq Scan on tenk1 b
518-
(3 rows)
512+
QUERY PLAN
513+
---------------------------
514+
Append
515+
-> Seq Scan on tenk1 b
516+
(2 rows)
519517

0 commit comments

Comments
 (0)