aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/optimizer/path/costsize.c26
-rw-r--r--src/test/regress/expected/partition_join.out136
-rw-r--r--src/test/regress/expected/subselect.out4
-rw-r--r--src/test/regress/sql/partition_join.sql8
4 files changed, 91 insertions, 83 deletions
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 16ef348f408..d8db0b29e1f 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -100,6 +100,13 @@
#define LOG2(x) (log(x) / 0.693147180559945)
+/*
+ * Append and MergeAppend nodes are less expensive than some other operations
+ * which use cpu_tuple_cost; instead of adding a separate GUC, estimate the
+ * per-tuple cost as cpu_tuple_cost multiplied by this value.
+ */
+#define APPEND_CPU_COST_MULTIPLIER 0.5
+
double seq_page_cost = DEFAULT_SEQ_PAGE_COST;
double random_page_cost = DEFAULT_RANDOM_PAGE_COST;
@@ -1828,10 +1835,6 @@ append_nonpartial_cost(List *subpaths, int numpaths, int parallel_workers)
/*
* cost_append
* Determines and returns the cost of an Append node.
- *
- * We charge nothing extra for the Append itself, which perhaps is too
- * optimistic, but since it doesn't do any selection or projection, it is a
- * pretty cheap node.
*/
void
cost_append(AppendPath *apath)
@@ -1914,6 +1917,13 @@ cost_append(AppendPath *apath)
apath->first_partial_path,
apath->path.parallel_workers);
}
+
+ /*
+ * Although Append does not do any selection or projection, it's not free;
+ * add a small per-tuple overhead.
+ */
+ apath->path.total_cost +=
+ cpu_tuple_cost * APPEND_CPU_COST_MULTIPLIER * apath->path.rows;
}
/*
@@ -1968,12 +1978,10 @@ cost_merge_append(Path *path, PlannerInfo *root,
run_cost += tuples * comparison_cost * logN;
/*
- * Also charge a small amount (arbitrarily set equal to operator cost) per
- * extracted tuple. We don't charge cpu_tuple_cost because a MergeAppend
- * node doesn't do qual-checking or projection, so it has less overhead
- * than most plan nodes.
+ * Although MergeAppend does not do any selection or projection, it's not
+ * free; add a small per-tuple overhead.
*/
- run_cost += cpu_operator_cost * tuples;
+ run_cost += cpu_tuple_cost * APPEND_CPU_COST_MULTIPLIER * tuples;
path->startup_cost = startup_cost + input_startup_cost;
path->total_cost = startup_cost + run_cost + input_total_cost;
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 636bedadf2e..a72d8bc2082 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1144,59 +1144,59 @@ INSERT INTO plt1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_ser
ANALYZE plt1_e;
-- test partition matching with N-way join
EXPLAIN (COSTS OFF)
-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;
+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;
QUERY PLAN
--------------------------------------------------------------------------------------
- Sort
- Sort Key: t1.c, t3.c
- -> HashAggregate
- Group Key: t1.c, t2.c, t3.c
+ GroupAggregate
+ Group Key: t1.c, t2.c, t3.c
+ -> Sort
+ Sort Key: t1.c, t3.c
-> Result
-> Append
-> Hash Join
- Hash Cond: (t1.c = t2.c)
- -> Seq Scan on plt1_p1 t1
- -> Hash
- -> Hash Join
- Hash Cond: (t2.c = ltrim(t3.c, 'A'::text))
+ Hash Cond: (t1.c = ltrim(t3.c, 'A'::text))
+ -> Hash Join
+ Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c))
+ -> Seq Scan on plt1_p1 t1
+ -> Hash
-> Seq Scan on plt2_p1 t2
- -> Hash
- -> Seq Scan on plt1_e_p1 t3
- -> Hash Join
- Hash Cond: (t1_1.c = t2_1.c)
- -> Seq Scan on plt1_p2 t1_1
-> Hash
- -> Hash Join
- Hash Cond: (t2_1.c = ltrim(t3_1.c, 'A'::text))
- -> Seq Scan on plt2_p2 t2_1
- -> Hash
- -> Seq Scan on plt1_e_p2 t3_1
+ -> Seq Scan on plt1_e_p1 t3
-> Hash Join
- Hash Cond: (t1_2.c = t2_2.c)
- -> Seq Scan on plt1_p3 t1_2
+ Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
+ -> Hash Join
+ Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
-> Hash
- -> Hash Join
- Hash Cond: (t2_2.c = ltrim(t3_2.c, 'A'::text))
+ -> Seq Scan on plt1_e_p2 t3_1
+ -> Hash Join
+ Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
+ -> Hash Join
+ Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
-> Seq Scan on plt2_p3 t2_2
- -> Hash
- -> Seq Scan on plt1_e_p3 t3_2
+ -> Hash
+ -> Seq Scan on plt1_e_p3 t3_2
(33 rows)
-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;
+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;
avg | avg | avg | c | c | c
----------------------+----------------------+-----------------------+------+------+-------
24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
- 74.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
- 124.0000000000000000 | 124.5000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
+ 75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
+ 123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
- 224.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
- 274.0000000000000000 | 274.5000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
+ 225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
+ 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
324.0000000000000000 | 324.0000000000000000 | 648.0000000000000000 | 0006 | 0006 | A0006
- 374.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
- 424.0000000000000000 | 424.5000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
+ 375.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
+ 423.0000000000000000 | 423.0000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
474.0000000000000000 | 474.0000000000000000 | 948.0000000000000000 | 0009 | 0009 | A0009
- 524.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
- 574.0000000000000000 | 574.5000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
+ 525.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
+ 573.0000000000000000 | 573.0000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
(12 rows)
-- 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
ANALYZE pht1_e;
-- test partition matching with N-way join
EXPLAIN (COSTS OFF)
-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;
- QUERY PLAN
---------------------------------------------------------------------------------------
- Sort
- Sort Key: t1.c, t3.c
- -> HashAggregate
- Group Key: t1.c, t2.c, t3.c
+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;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
+ GroupAggregate
+ Group Key: t1.c, t2.c, t3.c
+ -> Sort
+ Sort Key: t1.c, t3.c
-> Result
-> Append
-> Hash Join
- Hash Cond: (t1.c = t2.c)
- -> Seq Scan on pht1_p1 t1
- -> Hash
- -> Hash Join
- Hash Cond: (t2.c = ltrim(t3.c, 'A'::text))
+ Hash Cond: (t1.c = ltrim(t3.c, 'A'::text))
+ -> Hash Join
+ Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c))
+ -> Seq Scan on pht1_p1 t1
+ -> Hash
-> Seq Scan on pht2_p1 t2
- -> Hash
- -> Seq Scan on pht1_e_p1 t3
+ -> Hash
+ -> Seq Scan on pht1_e_p1 t3
-> Hash Join
- Hash Cond: (t1_1.c = t2_1.c)
- -> Seq Scan on pht1_p2 t1_1
+ Hash Cond: (ltrim(t3_1.c, 'A'::text) = t1_1.c)
+ -> Seq Scan on pht1_e_p2 t3_1
-> Hash
-> Hash Join
- Hash Cond: (t2_1.c = ltrim(t3_1.c, 'A'::text))
- -> Seq Scan on pht2_p2 t2_1
+ Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
+ -> Seq Scan on pht1_p2 t1_1
-> Hash
- -> Seq Scan on pht1_e_p2 t3_1
+ -> Seq Scan on pht2_p2 t2_1
-> Hash Join
- Hash Cond: (t1_2.c = t2_2.c)
- -> Seq Scan on pht1_p3 t1_2
+ Hash Cond: (ltrim(t3_2.c, 'A'::text) = t1_2.c)
+ -> Seq Scan on pht1_e_p3 t3_2
-> Hash
-> Hash Join
- Hash Cond: (t2_2.c = ltrim(t3_2.c, 'A'::text))
- -> Seq Scan on pht2_p3 t2_2
+ Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
+ -> Seq Scan on pht1_p3 t1_2
-> Hash
- -> Seq Scan on pht1_e_p3 t3_2
+ -> Seq Scan on pht2_p3 t2_2
(33 rows)
-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;
+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;
avg | avg | avg | c | c | c
----------------------+----------------------+-----------------------+------+------+-------
24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
- 74.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
- 124.0000000000000000 | 124.5000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
+ 75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
+ 123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
- 224.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
- 274.0000000000000000 | 274.5000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
+ 225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
+ 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
324.0000000000000000 | 324.0000000000000000 | 648.0000000000000000 | 0006 | 0006 | A0006
- 374.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
- 424.0000000000000000 | 424.5000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
+ 375.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
+ 423.0000000000000000 | 423.0000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
474.0000000000000000 | 474.0000000000000000 | 948.0000000000000000 | 0009 | 0009 | A0009
- 524.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
- 574.0000000000000000 | 574.5000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
+ 525.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
+ 573.0000000000000000 | 573.0000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
(12 rows)
--
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 4b893856cfc..3b2bf3273e8 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -235,7 +235,7 @@ SELECT *, pg_typeof(f1) FROM
explain verbose select '42' union all select '43';
QUERY PLAN
-------------------------------------------------
- Append (cost=0.00..0.04 rows=2 width=32)
+ Append (cost=0.00..0.05 rows=2 width=32)
-> Result (cost=0.00..0.01 rows=1 width=32)
Output: '42'::text
-> Result (cost=0.00..0.01 rows=1 width=32)
@@ -245,7 +245,7 @@ explain verbose select '42' union all select '43';
explain verbose select '42' union all select 43;
QUERY PLAN
------------------------------------------------
- Append (cost=0.00..0.04 rows=2 width=4)
+ Append (cost=0.00..0.05 rows=2 width=4)
-> Result (cost=0.00..0.01 rows=1 width=4)
Output: 42
-> Result (cost=0.00..0.01 rows=1 width=4)
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 4b2e7810601..17772a9300b 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -213,8 +213,8 @@ ANALYZE plt1_e;
-- test partition matching with N-way join
EXPLAIN (COSTS OFF)
-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;
-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;
+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;
+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;
-- joins where one of the relations is proven empty
EXPLAIN (COSTS OFF)
@@ -258,8 +258,8 @@ ANALYZE pht1_e;
-- test partition matching with N-way join
EXPLAIN (COSTS OFF)
-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;
-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;
+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;
+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;
--
-- multiple levels of partitioning
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-29 19:52:28 +0000