Skip to content

Commit 7d8ac98

Browse files
robertmhaasrobertmhaas
committed
Charge cpu_tuple_cost * 0.5 for Append and MergeAppend nodes.
Previously, Append didn't charge anything at all, and MergeAppend charged only cpu_operator_cost, about half the value used here. This change might make MergeAppend plans slightly more likely to be chosen than before, since this commit increases the assumed cost for Append -- with default values -- by 0.005 per tuple but MergeAppend by only 0.0025 per tuple. Since the comparisons required by MergeAppend are costed separately, it's not clear why MergeAppend needs to be otherwise more expensive than Append, so hopefully this is OK. Prior to partition-wise join, it didn't really matter whether or not an Append node had any cost of its own, because every plan had to use the same number of Append or MergeAppend nodes and in the same places. Only the relative cost of Append vs. MergeAppend made a difference. Now, however, it is possible to avoid some of the Append nodes using a partition-wise join, so it's worth making an effort. Pending patches for partition-wise aggregate care too, because an Append of Aggregate nodes will incur the Append overhead fewer times than an Aggregate over an Append. Although in most cases this change will favor the use of partition-wise techniques, it does the opposite when the join cardinality is greater than the sum of the input cardinalities. Since this situation arises in an existing regression test, I [rhaas] adjusted it to keep the overall plan shape approximately the same. Jeevan Chalke, per a suggestion from David Rowley. Reviewed by Ashutosh Bapat. Some changes by me. The larger patch series of which this patch is a part was also reviewed and tested by Antonin Houska, Rajkumar Raghuwanshi, David Rowley, Dilip Kumar, Konstantin Knizhnik, Pascal Legrand, Rafia Sabih, and me. Discussion: http://postgr.es/m/CAKJS1f9UXdk6ZYyqbJnjFO9a9hyHKGW7B=ZRh-rxy9qxfPA5Gw@mail.gmail.com
1 parent 38b41f1 commit 7d8ac98

File tree

4 files changed

+91
-83
lines changed

4 files changed

+91
-83
lines changed

‎src/backend/optimizer/path/costsize.c

Lines changed: 17 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -100,6 +100,13 @@
100100

101101
#define LOG2(x) (log(x) / 0.693147180559945)
102102

103+
/*
104+
* Append and MergeAppend nodes are less expensive than some other operations
105+
* which use cpu_tuple_cost; instead of adding a separate GUC, estimate the
106+
* per-tuple cost as cpu_tuple_cost multiplied by this value.
107+
*/
108+
#define APPEND_CPU_COST_MULTIPLIER 0.5
109+
103110

104111
double seq_page_cost = DEFAULT_SEQ_PAGE_COST;
105112
double random_page_cost = DEFAULT_RANDOM_PAGE_COST;
@@ -1828,10 +1835,6 @@ append_nonpartial_cost(List *subpaths, int numpaths, int parallel_workers)
18281835
/*
18291836
* cost_append
18301837
* Determines and returns the cost of an Append node.
1831-
*
1832-
* We charge nothing extra for the Append itself, which perhaps is too
1833-
* optimistic, but since it doesn't do any selection or projection, it is a
1834-
* pretty cheap node.
18351838
*/
18361839
void
18371840
cost_append(AppendPath *apath)
@@ -1914,6 +1917,13 @@ cost_append(AppendPath *apath)
19141917
apath->first_partial_path,
19151918
apath->path.parallel_workers);
19161919
}
1920+
1921+
/*
1922+
* Although Append does not do any selection or projection, it's not free;
1923+
* add a small per-tuple overhead.
1924+
*/
1925+
apath->path.total_cost +=
1926+
cpu_tuple_cost * APPEND_CPU_COST_MULTIPLIER * apath->path.rows;
19171927
}
19181928

19191929
/*
@@ -1968,12 +1978,10 @@ cost_merge_append(Path *path, PlannerInfo *root,
19681978
run_cost += tuples * comparison_cost * logN;
19691979

19701980
/*
1971-
* Also charge a small amount (arbitrarily set equal to operator cost) per
1972-
* extracted tuple. We don't charge cpu_tuple_cost because a MergeAppend
1973-
* node doesn't do qual-checking or projection, so it has less overhead
1974-
* than most plan nodes.
1981+
* Although MergeAppend does not do any selection or projection, it's not
1982+
* free; add a small per-tuple overhead.
19751983
*/
1976-
run_cost += cpu_operator_cost * tuples;
1984+
run_cost += cpu_tuple_cost * APPEND_CPU_COST_MULTIPLIER * tuples;
19771985

19781986
path->startup_cost = startup_cost + input_startup_cost;
19791987
path->total_cost = startup_cost + run_cost + input_total_cost;

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

Lines changed: 68 additions & 68 deletions
Original file line numberDiff line numberDiff line change
@@ -1144,59 +1144,59 @@ INSERT INTO plt1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_ser
11441144
ANALYZE plt1_e;
11451145
-- test partition matching with N-way join
11461146
EXPLAIN (COSTS OFF)
1147-
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
1147+
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
11481148
QUERY PLAN
11491149
--------------------------------------------------------------------------------------
1150-
Sort
1151-
Sort Key: t1.c, t3.c
1152-
-> HashAggregate
1153-
Group Key: t1.c, t2.c, t3.c
1150+
GroupAggregate
1151+
Group Key: t1.c, t2.c, t3.c
1152+
-> Sort
1153+
Sort Key: t1.c, t3.c
11541154
-> Result
11551155
-> Append
11561156
-> Hash Join
1157-
Hash Cond: (t1.c = t2.c)
1158-
-> Seq Scan on plt1_p1 t1
1159-
-> Hash
1160-
-> Hash Join
1161-
Hash Cond: (t2.c = ltrim(t3.c, 'A'::text))
1157+
Hash Cond: (t1.c = ltrim(t3.c, 'A'::text))
1158+
-> Hash Join
1159+
Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c))
1160+
-> Seq Scan on plt1_p1 t1
1161+
-> Hash
11621162
-> Seq Scan on plt2_p1 t2
1163-
-> Hash
1164-
-> Seq Scan on plt1_e_p1 t3
1165-
-> Hash Join
1166-
Hash Cond: (t1_1.c = t2_1.c)
1167-
-> Seq Scan on plt1_p2 t1_1
11681163
-> Hash
1169-
-> Hash Join
1170-
Hash Cond: (t2_1.c = ltrim(t3_1.c, 'A'::text))
1171-
-> Seq Scan on plt2_p2 t2_1
1172-
-> Hash
1173-
-> Seq Scan on plt1_e_p2 t3_1
1164+
-> Seq Scan on plt1_e_p1 t3
11741165
-> Hash Join
1175-
Hash Cond: (t1_2.c = t2_2.c)
1176-
-> Seq Scan on plt1_p3 t1_2
1166+
Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
1167+
-> Hash Join
1168+
Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
1169+
-> Seq Scan on plt1_p2 t1_1
1170+
-> Hash
1171+
-> Seq Scan on plt2_p2 t2_1
11771172
-> Hash
1178-
-> Hash Join
1179-
Hash Cond: (t2_2.c = ltrim(t3_2.c, 'A'::text))
1173+
-> Seq Scan on plt1_e_p2 t3_1
1174+
-> Hash Join
1175+
Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
1176+
-> Hash Join
1177+
Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
1178+
-> Seq Scan on plt1_p3 t1_2
1179+
-> Hash
11801180
-> Seq Scan on plt2_p3 t2_2
1181-
-> Hash
1182-
-> Seq Scan on plt1_e_p3 t3_2
1181+
-> Hash
1182+
-> Seq Scan on plt1_e_p3 t3_2
11831183
(33 rows)
11841184

1185-
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
1185+
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
11861186
avg | avg | avg | c | c | c
11871187
----------------------+----------------------+-----------------------+------+------+-------
11881188
24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
1189-
74.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
1190-
124.0000000000000000 | 124.5000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
1189+
75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
1190+
123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
11911191
174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
1192-
224.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
1193-
274.0000000000000000 | 274.5000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
1192+
225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
1193+
273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
11941194
324.0000000000000000 | 324.0000000000000000 | 648.0000000000000000 | 0006 | 0006 | A0006
1195-
374.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
1196-
424.0000000000000000 | 424.5000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
1195+
375.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
1196+
423.0000000000000000 | 423.0000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
11971197
474.0000000000000000 | 474.0000000000000000 | 948.0000000000000000 | 0009 | 0009 | A0009
1198-
524.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
1199-
574.0000000000000000 | 574.5000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
1198+
525.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
1199+
573.0000000000000000 | 573.0000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
12001200
(12 rows)
12011201

12021202
-- joins where one of the relations is proven empty
@@ -1289,59 +1289,59 @@ INSERT INTO pht1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_ser
12891289
ANALYZE pht1_e;
12901290
-- test partition matching with N-way join
12911291
EXPLAIN (COSTS OFF)
1292-
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
1293-
QUERY PLAN
1294-
--------------------------------------------------------------------------------------
1295-
Sort
1296-
Sort Key: t1.c, t3.c
1297-
-> HashAggregate
1298-
Group Key: t1.c, t2.c, t3.c
1292+
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
1293+
QUERY PLAN
1294+
--------------------------------------------------------------------------------------------
1295+
GroupAggregate
1296+
Group Key: t1.c, t2.c, t3.c
1297+
-> Sort
1298+
Sort Key: t1.c, t3.c
12991299
-> Result
13001300
-> Append
13011301
-> Hash Join
1302-
Hash Cond: (t1.c = t2.c)
1303-
-> Seq Scan on pht1_p1 t1
1304-
-> Hash
1305-
-> Hash Join
1306-
Hash Cond: (t2.c = ltrim(t3.c, 'A'::text))
1302+
Hash Cond: (t1.c = ltrim(t3.c, 'A'::text))
1303+
-> Hash Join
1304+
Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c))
1305+
-> Seq Scan on pht1_p1 t1
1306+
-> Hash
13071307
-> Seq Scan on pht2_p1 t2
1308-
-> Hash
1309-
-> Seq Scan on pht1_e_p1 t3
1308+
-> Hash
1309+
-> Seq Scan on pht1_e_p1 t3
13101310
-> Hash Join
1311-
Hash Cond: (t1_1.c = t2_1.c)
1312-
-> Seq Scan on pht1_p2 t1_1
1311+
Hash Cond: (ltrim(t3_1.c, 'A'::text) = t1_1.c)
1312+
-> Seq Scan on pht1_e_p2 t3_1
13131313
-> Hash
13141314
-> Hash Join
1315-
Hash Cond: (t2_1.c = ltrim(t3_1.c, 'A'::text))
1316-
-> Seq Scan on pht2_p2 t2_1
1315+
Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
1316+
-> Seq Scan on pht1_p2 t1_1
13171317
-> Hash
1318-
-> Seq Scan on pht1_e_p2 t3_1
1318+
-> Seq Scan on pht2_p2 t2_1
13191319
-> Hash Join
1320-
Hash Cond: (t1_2.c = t2_2.c)
1321-
-> Seq Scan on pht1_p3 t1_2
1320+
Hash Cond: (ltrim(t3_2.c, 'A'::text) = t1_2.c)
1321+
-> Seq Scan on pht1_e_p3 t3_2
13221322
-> Hash
13231323
-> Hash Join
1324-
Hash Cond: (t2_2.c = ltrim(t3_2.c, 'A'::text))
1325-
-> Seq Scan on pht2_p3 t2_2
1324+
Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
1325+
-> Seq Scan on pht1_p3 t1_2
13261326
-> Hash
1327-
-> Seq Scan on pht1_e_p3 t3_2
1327+
-> Seq Scan on pht2_p3 t2_2
13281328
(33 rows)
13291329

1330-
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
1330+
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
13311331
avg | avg | avg | c | c | c
13321332
----------------------+----------------------+-----------------------+------+------+-------
13331333
24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
1334-
74.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
1335-
124.0000000000000000 | 124.5000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
1334+
75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
1335+
123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
13361336
174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
1337-
224.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
1338-
274.0000000000000000 | 274.5000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
1337+
225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
1338+
273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
13391339
324.0000000000000000 | 324.0000000000000000 | 648.0000000000000000 | 0006 | 0006 | A0006
1340-
374.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
1341-
424.0000000000000000 | 424.5000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
1340+
375.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
1341+
423.0000000000000000 | 423.0000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
13421342
474.0000000000000000 | 474.0000000000000000 | 948.0000000000000000 | 0009 | 0009 | A0009
1343-
524.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
1344-
574.0000000000000000 | 574.5000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
1343+
525.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
1344+
573.0000000000000000 | 573.0000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
13451345
(12 rows)
13461346

13471347
--

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -235,7 +235,7 @@ SELECT *, pg_typeof(f1) FROM
235235
explain verbose select '42' union all select '43';
236236
QUERY PLAN
237237
-------------------------------------------------
238-
Append (cost=0.00..0.04 rows=2 width=32)
238+
Append (cost=0.00..0.05 rows=2 width=32)
239239
-> Result (cost=0.00..0.01 rows=1 width=32)
240240
Output: '42'::text
241241
-> Result (cost=0.00..0.01 rows=1 width=32)
@@ -245,7 +245,7 @@ explain verbose select '42' union all select '43';
245245
explain verbose select '42' union all select 43;
246246
QUERY PLAN
247247
------------------------------------------------
248-
Append (cost=0.00..0.04 rows=2 width=4)
248+
Append (cost=0.00..0.05 rows=2 width=4)
249249
-> Result (cost=0.00..0.01 rows=1 width=4)
250250
Output: 42
251251
-> Result (cost=0.00..0.01 rows=1 width=4)

‎src/test/regress/sql/partition_join.sql

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -213,8 +213,8 @@ ANALYZE plt1_e;
213213

214214
-- test partition matching with N-way join
215215
EXPLAIN (COSTS OFF)
216-
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
217-
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
216+
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
217+
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
218218

219219
-- joins where one of the relations is proven empty
220220
EXPLAIN (COSTS OFF)
@@ -258,8 +258,8 @@ ANALYZE pht1_e;
258258

259259
-- test partition matching with N-way join
260260
EXPLAIN (COSTS OFF)
261-
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
262-
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
261+
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
262+
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
263263

264264
--
265265
-- multiple levels of partitioning

0 commit comments

Comments
 (0)