Skip to content

Commit cac169d

Browse files
david-rowleydavid-rowley
committed
Increase DEFAULT_FDW_TUPLE_COST from 0.01 to 0.2
0.01 was unrealistically low as it's the same as the default cpu_tuple_cost and 10x cheaper than the default parallel_tuple_cost. It's hard to imagine a situation where fetching a tuple from a foreign server would be cheaper than fetching one from a parallel worker. After some experimentation on a loopback server, somewhere between 0.15 and 0.3 seems more realistic. Here we split the difference and set it to 0.2. This will cause operations that reduce the number of tuples (e.g. aggregation) to be more likely to take place on the foreign server. Adjusting this causes some plan changes in the postgres_fdw regression tests. This is because penalizing each Path with the additional tuple costs causes some dilution of the costs of the other operations being charged for and results in various paths appearing to be closer to the same costs such that add_path's STD_FUZZ_FACTOR is more likely to see two paths as costing (fuzzily) the same. This isn't ideal, but it shouldn't be reason enough to use artificially low costs. Discussion: https://postgr.es/m/CAApHDvopVjjfh5c1Ed2HRvDdfom2dEpMwwiu5-f1AnmYprJngA@mail.gmail.com
1 parent 4210b55 commit cac169d

File tree

2 files changed

+62
-69
lines changed

2 files changed

+62
-69
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 61 additions & 68 deletions
Original file line numberDiff line numberDiff line change
@@ -9729,21 +9729,19 @@ SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE
97299729
-- test FOR UPDATE; partitionwise join does not apply
97309730
EXPLAIN (COSTS OFF)
97319731
SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
9732-
QUERY PLAN
9733-
--------------------------------------------------------------
9732+
QUERY PLAN
9733+
--------------------------------------------------------
97349734
LockRows
9735-
-> Sort
9736-
Sort Key: t1.a
9737-
-> Hash Join
9738-
Hash Cond: (t2.b = t1.a)
9735+
-> Nested Loop
9736+
Join Filter: (t1.a = t2.b)
9737+
-> Append
9738+
-> Foreign Scan on ftprt1_p1 t1_1
9739+
-> Foreign Scan on ftprt1_p2 t1_2
9740+
-> Materialize
97399741
-> Append
97409742
-> Foreign Scan on ftprt2_p1 t2_1
97419743
-> Foreign Scan on ftprt2_p2 t2_2
9742-
-> Hash
9743-
-> Append
9744-
-> Foreign Scan on ftprt1_p1 t1_1
9745-
-> Foreign Scan on ftprt1_p2 t1_2
9746-
(12 rows)
9744+
(10 rows)
97479745

97489746
SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
97499747
a | b
@@ -9778,18 +9776,16 @@ ANALYZE fpagg_tab_p3;
97789776
SET enable_partitionwise_aggregate TO false;
97799777
EXPLAIN (COSTS OFF)
97809778
SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
9781-
QUERY PLAN
9782-
-----------------------------------------------------------
9783-
Sort
9784-
Sort Key: pagg_tab.a
9785-
-> HashAggregate
9786-
Group Key: pagg_tab.a
9787-
Filter: (avg(pagg_tab.b) < '22'::numeric)
9788-
-> Append
9789-
-> Foreign Scan on fpagg_tab_p1 pagg_tab_1
9790-
-> Foreign Scan on fpagg_tab_p2 pagg_tab_2
9791-
-> Foreign Scan on fpagg_tab_p3 pagg_tab_3
9792-
(9 rows)
9779+
QUERY PLAN
9780+
-----------------------------------------------------
9781+
GroupAggregate
9782+
Group Key: pagg_tab.a
9783+
Filter: (avg(pagg_tab.b) < '22'::numeric)
9784+
-> Append
9785+
-> Foreign Scan on fpagg_tab_p1 pagg_tab_1
9786+
-> Foreign Scan on fpagg_tab_p2 pagg_tab_2
9787+
-> Foreign Scan on fpagg_tab_p3 pagg_tab_3
9788+
(7 rows)
97939789

97949790
-- Plan with partitionwise aggregates is enabled
97959791
SET enable_partitionwise_aggregate TO true;
@@ -9823,34 +9819,32 @@ SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 O
98239819
-- Should have all the columns in the target list for the given relation
98249820
EXPLAIN (VERBOSE, COSTS OFF)
98259821
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
9826-
QUERY PLAN
9827-
------------------------------------------------------------------------
9828-
Sort
9829-
Output: t1.a, (count(((t1.*)::pagg_tab)))
9822+
QUERY PLAN
9823+
--------------------------------------------------------------------------------------------
9824+
Merge Append
98309825
Sort Key: t1.a
9831-
-> Append
9832-
-> HashAggregate
9833-
Output: t1.a, count(((t1.*)::pagg_tab))
9834-
Group Key: t1.a
9835-
Filter: (avg(t1.b) < '22'::numeric)
9836-
-> Foreign Scan on public.fpagg_tab_p1 t1
9837-
Output: t1.a, t1.*, t1.b
9838-
Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1
9839-
-> HashAggregate
9840-
Output: t1_1.a, count(((t1_1.*)::pagg_tab))
9841-
Group Key: t1_1.a
9842-
Filter: (avg(t1_1.b) < '22'::numeric)
9843-
-> Foreign Scan on public.fpagg_tab_p2 t1_1
9844-
Output: t1_1.a, t1_1.*, t1_1.b
9845-
Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2
9846-
-> HashAggregate
9847-
Output: t1_2.a, count(((t1_2.*)::pagg_tab))
9848-
Group Key: t1_2.a
9849-
Filter: (avg(t1_2.b) < '22'::numeric)
9850-
-> Foreign Scan on public.fpagg_tab_p3 t1_2
9851-
Output: t1_2.a, t1_2.*, t1_2.b
9852-
Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3
9853-
(25 rows)
9826+
-> GroupAggregate
9827+
Output: t1.a, count(((t1.*)::pagg_tab))
9828+
Group Key: t1.a
9829+
Filter: (avg(t1.b) < '22'::numeric)
9830+
-> Foreign Scan on public.fpagg_tab_p1 t1
9831+
Output: t1.a, t1.*, t1.b
9832+
Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1 ORDER BY a ASC NULLS LAST
9833+
-> GroupAggregate
9834+
Output: t1_1.a, count(((t1_1.*)::pagg_tab))
9835+
Group Key: t1_1.a
9836+
Filter: (avg(t1_1.b) < '22'::numeric)
9837+
-> Foreign Scan on public.fpagg_tab_p2 t1_1
9838+
Output: t1_1.a, t1_1.*, t1_1.b
9839+
Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2 ORDER BY a ASC NULLS LAST
9840+
-> GroupAggregate
9841+
Output: t1_2.a, count(((t1_2.*)::pagg_tab))
9842+
Group Key: t1_2.a
9843+
Filter: (avg(t1_2.b) < '22'::numeric)
9844+
-> Foreign Scan on public.fpagg_tab_p3 t1_2
9845+
Output: t1_2.a, t1_2.*, t1_2.b
9846+
Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3 ORDER BY a ASC NULLS LAST
9847+
(23 rows)
98549848

98559849
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
98569850
a | count
@@ -9866,24 +9860,23 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
98669860
-- When GROUP BY clause does not match with PARTITION KEY.
98679861
EXPLAIN (COSTS OFF)
98689862
SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
9869-
QUERY PLAN
9870-
-----------------------------------------------------------------
9871-
Sort
9872-
Sort Key: pagg_tab.b
9873-
-> Finalize HashAggregate
9874-
Group Key: pagg_tab.b
9875-
Filter: (sum(pagg_tab.a) < 700)
9876-
-> Append
9877-
-> Partial HashAggregate
9878-
Group Key: pagg_tab.b
9879-
-> Foreign Scan on fpagg_tab_p1 pagg_tab
9880-
-> Partial HashAggregate
9881-
Group Key: pagg_tab_1.b
9882-
-> Foreign Scan on fpagg_tab_p2 pagg_tab_1
9883-
-> Partial HashAggregate
9884-
Group Key: pagg_tab_2.b
9885-
-> Foreign Scan on fpagg_tab_p3 pagg_tab_2
9886-
(15 rows)
9863+
QUERY PLAN
9864+
-----------------------------------------------------------
9865+
Finalize GroupAggregate
9866+
Group Key: pagg_tab.b
9867+
Filter: (sum(pagg_tab.a) < 700)
9868+
-> Merge Append
9869+
Sort Key: pagg_tab.b
9870+
-> Partial GroupAggregate
9871+
Group Key: pagg_tab.b
9872+
-> Foreign Scan on fpagg_tab_p1 pagg_tab
9873+
-> Partial GroupAggregate
9874+
Group Key: pagg_tab_1.b
9875+
-> Foreign Scan on fpagg_tab_p2 pagg_tab_1
9876+
-> Partial GroupAggregate
9877+
Group Key: pagg_tab_2.b
9878+
-> Foreign Scan on fpagg_tab_p3 pagg_tab_2
9879+
(14 rows)
98879880

98889881
-- ===================================================================
98899882
-- access rights and superuser

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,7 +57,7 @@ PG_MODULE_MAGIC;
5757
#define DEFAULT_FDW_STARTUP_COST 100.0
5858

5959
/* Default CPU cost to process 1 row (above and beyond cpu_tuple_cost). */
60-
#define DEFAULT_FDW_TUPLE_COST 0.01
60+
#define DEFAULT_FDW_TUPLE_COST 0.2
6161

6262
/* If no remote estimates, assume a sort costs 20% extra */
6363
#define DEFAULT_FDW_SORT_MULTIPLIER 1.2

0 commit comments

Comments
 (0)