On Fri Oct 2, 2026 at 6:29 PM CEST, Rui Zhao wrote:
1. I found a regression with a local filter and LIMIT: 2. Local projection costs can also prevent LIMIT pushdown. Using sort_ft above:
Thanks for the review and finding these plan-regressions. It turns out that the underlying problem isn't specific to my patch. With slightly different queries master picks the same kind of bad plans. I did not use your suggested fixes. Instead I changed estimate_path_cost_size() to build up the costs in the order the work happens: remote work, then transfer, then local work. That approach results in less code, and that code is also easier to understand (imo). See the newly attached patchset for details. It contains three preparatory patches before my original patch: 1. Starts using run_cost instead of total_cost to make the fix easier to understand and also fixes a small bug 2. Adds tests to show the bad plans on master 3. Fixes the bad plans in the tests introduced by 2 4. My original patch (I only improved the comments a bit)
From 99262adf7fe0eba689e1ab94ecd61e2d71d06bfe Mon Sep 17 00:00:00 2001 From: Jelte Fennema-Nio <[email protected]> Date: Sat, 3 Oct 2026 21:20:05 -0400 Subject: [PATCH v2 1/4] postgres_fdw: Track run cost instead of total cost when costing paths Instead of tracking total_cost in estimate_path_cost_size(), this now starts tracking run_cost. Only at the end of the function is the total cost calculated by adding the startup_cost and run_cost together. The reason this change is made is to get rid of the error-prone duplication of the same cost calculations for both startup and total cost. In two cases this duplication was forgotten: the local_conds_cost and the local_cost startup costs were never added to the total_cost. The new code makes this kind of bug impossible. Discussion: https://postgr.es/m/[email protected] --- contrib/postgres_fdw/postgres_fdw.c | 56 ++++++++++++++--------------- contrib/postgres_fdw/postgres_fdw.h | 2 +- 2 files changed, 28 insertions(+), 30 deletions(-) diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 2bcff4b26b4..0d8c59500e8 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -845,7 +845,7 @@ postgresGetForeignRelSize(PlannerInfo *root, */ fpinfo->retrieved_rows = -1; fpinfo->rel_startup_cost = -1; - fpinfo->rel_total_cost = -1; + fpinfo->rel_run_cost = -1; /* * If the table or the server is configured to use remote estimates, @@ -3414,7 +3414,7 @@ estimate_path_cost_size(PlannerInfo *root, int width; int disabled_nodes = 0; Cost startup_cost; - Cost total_cost; + Cost run_cost = 0; /* Make sure the core code has set up the relation's reltarget */ Assert(foreignrel->reltarget); @@ -3434,6 +3434,7 @@ estimate_path_cost_size(PlannerInfo *root, PGconn *conn; Selectivity local_sel; QualCost local_cost; + Cost total_cost; List *fdw_scan_tlist = NIL; List *remote_conds; @@ -3479,6 +3480,7 @@ estimate_path_cost_size(PlannerInfo *root, get_remote_estimate(sql.data, conn, &rows, &width, &startup_cost, &total_cost); ReleaseConnection(conn); + run_cost = total_cost - startup_cost; retrieved_rows = rows; @@ -3494,10 +3496,10 @@ estimate_path_cost_size(PlannerInfo *root, /* Add in the eval cost of the locally-checked quals */ startup_cost += fpinfo->local_conds_cost.startup; - total_cost += fpinfo->local_conds_cost.per_tuple * retrieved_rows; + run_cost += fpinfo->local_conds_cost.per_tuple * retrieved_rows; cost_qual_eval(&local_cost, local_param_join_conds, root); startup_cost += local_cost.startup; - total_cost += local_cost.per_tuple * retrieved_rows; + run_cost += local_cost.per_tuple * retrieved_rows; /* * Add in tlist eval cost for each output row. In case of an @@ -3505,22 +3507,18 @@ estimate_path_cost_size(PlannerInfo *root, * expressions will be evaluated remotely, so adjust the costs. */ startup_cost += foreignrel->reltarget->cost.startup; - total_cost += foreignrel->reltarget->cost.startup; - total_cost += foreignrel->reltarget->cost.per_tuple * rows; + run_cost += foreignrel->reltarget->cost.per_tuple * rows; if (IS_UPPER_REL(foreignrel)) { QualCost tlist_cost; cost_qual_eval(&tlist_cost, fdw_scan_tlist, root); startup_cost -= tlist_cost.startup; - total_cost -= tlist_cost.startup; - total_cost -= tlist_cost.per_tuple * rows; + run_cost -= tlist_cost.per_tuple * rows; } } else { - Cost run_cost = 0; - /* * We don't support join conditions in this mode (hence, no * parameterized paths can be made). @@ -3534,7 +3532,7 @@ estimate_path_cost_size(PlannerInfo *root, * underlying scan, join, or grouping each time. Instead, use those * estimates if we have cached them already. */ - if (fpinfo->rel_startup_cost >= 0 && fpinfo->rel_total_cost >= 0) + if (fpinfo->rel_startup_cost >= 0 && fpinfo->rel_run_cost >= 0) { Assert(fpinfo->retrieved_rows >= 0); @@ -3542,7 +3540,7 @@ estimate_path_cost_size(PlannerInfo *root, retrieved_rows = fpinfo->retrieved_rows; width = fpinfo->width; startup_cost = fpinfo->rel_startup_cost; - run_cost = fpinfo->rel_total_cost - fpinfo->rel_startup_cost; + run_cost = fpinfo->rel_run_cost; /* * If we estimate the costs of a foreign scan or a foreign join @@ -3639,8 +3637,8 @@ estimate_path_cost_size(PlannerInfo *root, * 4. Run time cost of applying nonpushable other clauses locally * on the result fetched from the foreign server. */ - run_cost = fpinfo_i->rel_total_cost - fpinfo_i->rel_startup_cost; - run_cost += fpinfo_o->rel_total_cost - fpinfo_o->rel_startup_cost; + run_cost = fpinfo_i->rel_run_cost; + run_cost += fpinfo_o->rel_run_cost; run_cost += nrows * join_cost.per_tuple; nrows = clamp_row_est(nrows * fpinfo->joinclause_sel); run_cost += nrows * remote_conds_cost.per_tuple; @@ -3669,7 +3667,7 @@ estimate_path_cost_size(PlannerInfo *root, * hashed aggregates in cost_agg(). We are not sure which * strategy will be considered at remote side, thus for * simplicity, we put all startup related costs in startup_cost - * and all finalization and run cost are added in total_cost. + * and all finalization and run cost are added in run_cost. */ ofpinfo = (PgFdwRelationInfo *) outerrel->fdw_private; @@ -3736,7 +3734,7 @@ estimate_path_cost_size(PlannerInfo *root, * 2. Run time cost of performing aggregation, per cost_agg() *----- */ - run_cost = ofpinfo->rel_total_cost - ofpinfo->rel_startup_cost; + run_cost = ofpinfo->rel_run_cost; run_cost += outerrel->reltarget->cost.per_tuple * input_rows; run_cost += aggcosts.finalCost.per_tuple * numGroups; run_cost += cpu_tuple_cost * numGroups; @@ -3827,13 +3825,14 @@ estimate_path_cost_size(PlannerInfo *root, } } - total_cost = startup_cost + run_cost; - /* Adjust the cost estimates if we have LIMIT */ if (fpextra && fpextra->has_limit) { + Cost total_cost = startup_cost + run_cost; + adjust_limit_rows_costs(&rows, &startup_cost, &total_cost, fpextra->offset_est, fpextra->count_est); + run_cost = total_cost - startup_cost; retrieved_rows = rows; } } @@ -3851,8 +3850,7 @@ estimate_path_cost_size(PlannerInfo *root, QualCost newcost = fpextra->target->cost; startup_cost += newcost.startup - oldcost.startup; - total_cost += newcost.startup - oldcost.startup; - total_cost += (newcost.per_tuple - oldcost.per_tuple) * rows; + run_cost += (newcost.per_tuple - oldcost.per_tuple) * rows; } /* @@ -3871,7 +3869,7 @@ estimate_path_cost_size(PlannerInfo *root, { fpinfo->retrieved_rows = retrieved_rows; fpinfo->rel_startup_cost = startup_cost; - fpinfo->rel_total_cost = total_cost; + fpinfo->rel_run_cost = run_cost; } /* @@ -3881,9 +3879,8 @@ estimate_path_cost_size(PlannerInfo *root, * (cpu_tuple_cost per retrieved row). */ startup_cost += fpinfo->fdw_startup_cost; - total_cost += fpinfo->fdw_startup_cost; - total_cost += fpinfo->fdw_tuple_cost * retrieved_rows; - total_cost += cpu_tuple_cost * retrieved_rows; + run_cost += fpinfo->fdw_tuple_cost * retrieved_rows; + run_cost += cpu_tuple_cost * retrieved_rows; /* * If we have LIMIT, we should prefer performing the restriction remotely @@ -3905,7 +3902,7 @@ estimate_path_cost_size(PlannerInfo *root, fpextra->limit_tuples < fpinfo->rows) { Assert(fpinfo->rows > 0); - total_cost -= (total_cost - startup_cost) * 0.05 * + run_cost -= run_cost * 0.05 * (fpinfo->rows - fpextra->limit_tuples) / fpinfo->rows; } @@ -3914,7 +3911,7 @@ estimate_path_cost_size(PlannerInfo *root, *p_width = width; *p_disabled_nodes = disabled_nodes; *p_startup_cost = startup_cost; - *p_total_cost = total_cost; + *p_total_cost = startup_cost + run_cost; } /* @@ -6992,7 +6989,8 @@ init_func_stub_fpinfo(const PgFdwRelationInfo *fpinfo_foreign, * local path for the same function is our best estimate of that. */ stub->rel_startup_cost = funcrel->cheapest_total_path->startup_cost; - stub->rel_total_cost = funcrel->cheapest_total_path->total_cost; + stub->rel_run_cost = funcrel->cheapest_total_path->total_cost - + funcrel->cheapest_total_path->startup_cost; return stub; } @@ -7428,7 +7426,7 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype, */ fpinfo->retrieved_rows = -1; fpinfo->rel_startup_cost = -1; - fpinfo->rel_total_cost = -1; + fpinfo->rel_run_cost = -1; /* * Set the string describing this join relation to be used in EXPLAIN @@ -8063,7 +8061,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel, */ fpinfo->retrieved_rows = -1; fpinfo->rel_startup_cost = -1; - fpinfo->rel_total_cost = -1; + fpinfo->rel_run_cost = -1; /* * Set the string describing this grouped relation to be used in EXPLAIN diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index da7da1c2ea9..e334217377f 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -73,7 +73,7 @@ typedef struct PgFdwRelationInfo */ double retrieved_rows; Cost rel_startup_cost; - Cost rel_total_cost; + Cost rel_run_cost; /* Options extracted from catalogs. */ bool use_remote_estimate; -- 2.55.0
From b37e5b4527a10c3f51ae70a60362899db0f4d06e Mon Sep 17 00:00:00 2001 From: Jelte Fennema-Nio <[email protected]> Date: Fri, 2 Oct 2026 14:06:24 -0400 Subject: [PATCH v2 2/4] postgres_fdw: Add tests for costing local work below remote sorts This adds a few new tests for the follow on commit. The reason they are separate is so it's easy to see the plans change in the next commit. When actually committing this should probably be squashed into a single commit. Rui Zhao found most of these problems. Discussion: https://postgr.es/m/[email protected] --- .../postgres_fdw/expected/postgres_fdw.out | 77 +++++++++++++++++++ contrib/postgres_fdw/sql/postgres_fdw.sql | 35 +++++++++ 2 files changed, 112 insertions(+) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 739f43af7bb..6170672d756 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -254,6 +254,83 @@ SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again -- and remote-estimate mode on ft2. ANALYZE ft1; ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true'); +-- We do some work locally on each fetched row. We check the local quals and +-- evaluate the target list. This work is the same whether we sort remotely +-- or locally. So it should not keep us from pushing down the sort or LIMIT. +CREATE FUNCTION local_filter(int) RETURNS boolean +LANGUAGE plpgsql IMMUTABLE COST 10000 AS $$ +BEGIN + RETURN $1 > 0; +END +$$; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1 FROM ft1 WHERE local_filter(c1) ORDER BY c1; + QUERY PLAN +--------------------------------------------------- + Sort + Output: c1 + Sort Key: ft1.c1 + -> Foreign Scan on public.ft1 + Output: c1 + Filter: local_filter(ft1.c1) + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" +(7 rows) + +-- Each of these is cheap enough that it's not postponed until after the sort. +CREATE FUNCTION local_project(int) RETURNS int +LANGUAGE plpgsql IMMUTABLE COST 9 AS $$ +BEGIN + RETURN $1; +END +$$; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT local_project(c1), local_project(c1 + 1), local_project(c1 + 2), + local_project(c1 + 3) +FROM ft1 ORDER BY c1 LIMIT 10; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------- + Limit + Output: (local_project(c1)), (local_project((c1 + 1))), (local_project((c1 + 2))), (local_project((c1 + 3))), c1 + -> Foreign Scan on public.ft1 + Output: local_project(c1), local_project((c1 + 1)), local_project((c1 + 2)), local_project((c1 + 3)), c1 + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST +(5 rows) + +-- The same is true for target list expressions that we evaluate on top of a +-- pushed down aggregate. Without a LIMIT the planner does not postpone even +-- expensive ones until after the sort. +ALTER FUNCTION local_project(int) COST 10000; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT local_project(c2), count(*) FROM ft1 GROUP BY c2 ORDER BY c2; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: (local_project(c2)), (count(*)), c2 + Sort Key: ft1.c2 + -> Foreign Scan + Output: local_project(c2), (count(*)), c2 + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT count(*), c2 FROM "S 1"."T 1" GROUP BY 2 +(7 rows) + +-- The same is true for HAVING quals that we check locally. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c2, count(*) FROM ft1 GROUP BY c2 HAVING local_filter(count(*)::int) +ORDER BY c2; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: c2, (count(*)) + Sort Key: ft1.c2 + -> Foreign Scan + Output: c2, (count(*)) + Filter: local_filter(((count(*)))::integer) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT c2, count(*) FROM "S 1"."T 1" GROUP BY 1 +(8 rows) + +DROP FUNCTION local_filter(int); +DROP FUNCTION local_project(int); -- =================================================================== -- test subscription -- =================================================================== diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index f1ca3204382..8e9186be92b 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -244,6 +244,41 @@ SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again ANALYZE ft1; ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true'); +-- We do some work locally on each fetched row. We check the local quals and +-- evaluate the target list. This work is the same whether we sort remotely +-- or locally. So it should not keep us from pushing down the sort or LIMIT. +CREATE FUNCTION local_filter(int) RETURNS boolean +LANGUAGE plpgsql IMMUTABLE COST 10000 AS $$ +BEGIN + RETURN $1 > 0; +END +$$; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1 FROM ft1 WHERE local_filter(c1) ORDER BY c1; +-- Each of these is cheap enough that it's not postponed until after the sort. +CREATE FUNCTION local_project(int) RETURNS int +LANGUAGE plpgsql IMMUTABLE COST 9 AS $$ +BEGIN + RETURN $1; +END +$$; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT local_project(c1), local_project(c1 + 1), local_project(c1 + 2), + local_project(c1 + 3) +FROM ft1 ORDER BY c1 LIMIT 10; +-- The same is true for target list expressions that we evaluate on top of a +-- pushed down aggregate. Without a LIMIT the planner does not postpone even +-- expensive ones until after the sort. +ALTER FUNCTION local_project(int) COST 10000; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT local_project(c2), count(*) FROM ft1 GROUP BY c2 ORDER BY c2; +-- The same is true for HAVING quals that we check locally. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c2, count(*) FROM ft1 GROUP BY c2 HAVING local_filter(count(*)::int) +ORDER BY c2; +DROP FUNCTION local_filter(int); +DROP FUNCTION local_project(int); + -- =================================================================== -- test subscription -- =================================================================== -- 2.55.0
From dceefcf50112a0654571471a3535ce2971c76aca Mon Sep 17 00:00:00 2001 From: Jelte Fennema-Nio <[email protected]> Date: Fri, 2 Oct 2026 13:27:05 -0400 Subject: [PATCH v2 3/4] postgres_fdw: Separate remote, transfer and local costs Without remote estimates, a sorted foreign path is costed by applying a multiplier to the cost of the unsorted path, including the local work on the fetched rows. That work doesn't depend on where we sort. Still, an expensive local filter, target list expression or HAVING qual could make the planner choose a local Sort or not push down the LIMIT, as the tests in the previous commit show. This fixes that by building up the costs in estimate_path_cost_size() in the order the work happens: remote work, then transfer, then local work. The sort multiplier now only applies to the remote work. Local quals on base relations are now also charged for the retrieved rows instead of for every tuple in the table. Reported-by: Rui Zhao <[email protected]> Discussion: https://postgr.es/m/[email protected] --- .../postgres_fdw/expected/postgres_fdw.out | 63 +++---- contrib/postgres_fdw/postgres_fdw.c | 172 ++++++++---------- contrib/postgres_fdw/postgres_fdw.h | 7 +- 3 files changed, 108 insertions(+), 134 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 6170672d756..5df5cbdf472 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -265,16 +265,13 @@ END $$; EXPLAIN (VERBOSE, COSTS OFF) SELECT c1 FROM ft1 WHERE local_filter(c1) ORDER BY c1; - QUERY PLAN ---------------------------------------------------- - Sort + QUERY PLAN +--------------------------------------------------------------------------- + Foreign Scan on public.ft1 Output: c1 - Sort Key: ft1.c1 - -> Foreign Scan on public.ft1 - Output: c1 - Filter: local_filter(ft1.c1) - Remote SQL: SELECT "C 1" FROM "S 1"."T 1" -(7 rows) + Filter: local_filter(ft1.c1) + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST +(4 rows) -- Each of these is cheap enough that it's not postponed until after the sort. CREATE FUNCTION local_project(int) RETURNS int @@ -287,14 +284,12 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT local_project(c1), local_project(c1 + 1), local_project(c1 + 2), local_project(c1 + 3) FROM ft1 ORDER BY c1 LIMIT 10; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------- - Limit - Output: (local_project(c1)), (local_project((c1 + 1))), (local_project((c1 + 2))), (local_project((c1 + 3))), c1 - -> Foreign Scan on public.ft1 - Output: local_project(c1), local_project((c1 + 1)), local_project((c1 + 2)), local_project((c1 + 3)), c1 - Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST -(5 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------------ + Foreign Scan on public.ft1 + Output: local_project(c1), local_project((c1 + 1)), local_project((c1 + 2)), local_project((c1 + 3)), c1 + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST LIMIT 10::bigint +(3 rows) -- The same is true for target list expressions that we evaluate on top of a -- pushed down aggregate. Without a LIMIT the planner does not postpone even @@ -302,32 +297,26 @@ FROM ft1 ORDER BY c1 LIMIT 10; ALTER FUNCTION local_project(int) COST 10000; EXPLAIN (VERBOSE, COSTS OFF) SELECT local_project(c2), count(*) FROM ft1 GROUP BY c2 ORDER BY c2; - QUERY PLAN ---------------------------------------------------------------------- - Sort - Output: (local_project(c2)), (count(*)), c2 - Sort Key: ft1.c2 - -> Foreign Scan - Output: local_project(c2), (count(*)), c2 - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT count(*), c2 FROM "S 1"."T 1" GROUP BY 2 -(7 rows) + QUERY PLAN +------------------------------------------------------------------------------------------ + Foreign Scan + Output: local_project(c2), (count(*)), c2 + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT count(*), c2 FROM "S 1"."T 1" GROUP BY 2 ORDER BY c2 ASC NULLS LAST +(4 rows) -- The same is true for HAVING quals that we check locally. EXPLAIN (VERBOSE, COSTS OFF) SELECT c2, count(*) FROM ft1 GROUP BY c2 HAVING local_filter(count(*)::int) ORDER BY c2; - QUERY PLAN ---------------------------------------------------------------------- - Sort + QUERY PLAN +------------------------------------------------------------------------------------------ + Foreign Scan Output: c2, (count(*)) - Sort Key: ft1.c2 - -> Foreign Scan - Output: c2, (count(*)) - Filter: local_filter(((count(*)))::integer) - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, count(*) FROM "S 1"."T 1" GROUP BY 1 -(8 rows) + Filter: local_filter(((count(*)))::integer) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT c2, count(*) FROM "S 1"."T 1" GROUP BY 1 ORDER BY c2 ASC NULLS LAST +(5 rows) DROP FUNCTION local_filter(int); DROP FUNCTION local_project(int); diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 0d8c59500e8..101d161cb68 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -3415,10 +3415,28 @@ estimate_path_cost_size(PlannerInfo *root, int disabled_nodes = 0; Cost startup_cost; Cost run_cost = 0; + List *local_param_join_conds = NIL; + QualCost remote_tlist_cost; + QualCost local_cost; + PathTarget *target; /* Make sure the core code has set up the relation's reltarget */ Assert(foreignrel->reltarget); + /* + * We build up the costs in the order in which the work happens. First + * the foreign server computes the result. Then the rows are transferred + * to us. Finally we do some work locally on each fetched row. We keep + * these costs apart because pushed down operations like a sort only apply + * to the remote work. + * + * For an upper relation the foreign server computes the expressions in + * grouped_tlist. We compute the rest of the target list locally on top + * of those. + */ + if (IS_UPPER_REL(foreignrel)) + cost_qual_eval(&remote_tlist_cost, fpinfo->grouped_tlist, root); + /* * If the table or the server is configured to use remote estimates, * connect to the foreign server and execute EXPLAIN to estimate the @@ -3429,11 +3447,9 @@ estimate_path_cost_size(PlannerInfo *root, if (fpinfo->use_remote_estimate) { List *remote_param_join_conds; - List *local_param_join_conds; StringInfoData sql; PGconn *conn; Selectivity local_sel; - QualCost local_cost; Cost total_cost; List *fdw_scan_tlist = NIL; List *remote_conds; @@ -3493,29 +3509,6 @@ estimate_path_cost_size(PlannerInfo *root, local_sel *= fpinfo->local_conds_sel; rows = clamp_row_est(rows * local_sel); - - /* Add in the eval cost of the locally-checked quals */ - startup_cost += fpinfo->local_conds_cost.startup; - run_cost += fpinfo->local_conds_cost.per_tuple * retrieved_rows; - cost_qual_eval(&local_cost, local_param_join_conds, root); - startup_cost += local_cost.startup; - run_cost += local_cost.per_tuple * retrieved_rows; - - /* - * Add in tlist eval cost for each output row. In case of an - * aggregate, some of the tlist expressions such as grouping - * expressions will be evaluated remotely, so adjust the costs. - */ - startup_cost += foreignrel->reltarget->cost.startup; - run_cost += foreignrel->reltarget->cost.per_tuple * rows; - if (IS_UPPER_REL(foreignrel)) - { - QualCost tlist_cost; - - cost_qual_eval(&tlist_cost, fdw_scan_tlist, root); - startup_cost -= tlist_cost.startup; - run_cost -= tlist_cost.per_tuple * rows; - } } else { @@ -3541,24 +3534,6 @@ estimate_path_cost_size(PlannerInfo *root, width = fpinfo->width; startup_cost = fpinfo->rel_startup_cost; run_cost = fpinfo->rel_run_cost; - - /* - * If we estimate the costs of a foreign scan or a foreign join - * with additional post-scan/join-processing steps, the scan or - * join costs obtained from the cache wouldn't yet contain the - * eval costs for the final scan/join target, which would've been - * updated by apply_scanjoin_target_to_paths(); add the eval costs - * now. - */ - if (fpextra && !IS_UPPER_REL(foreignrel)) - { - /* Shouldn't get here unless we have LIMIT */ - Assert(fpextra->has_limit); - Assert(foreignrel->reloptkind == RELOPT_BASEREL || - foreignrel->reloptkind == RELOPT_JOINREL); - startup_cost += foreignrel->reltarget->cost.startup; - run_cost += foreignrel->reltarget->cost.per_tuple * rows; - } } else if (IS_JOIN_REL(foreignrel)) { @@ -3620,7 +3595,6 @@ estimate_path_cost_size(PlannerInfo *root, startup_cost = fpinfo_i->rel_startup_cost + fpinfo_o->rel_startup_cost; startup_cost += join_cost.startup; startup_cost += remote_conds_cost.startup; - startup_cost += fpinfo->local_conds_cost.startup; /* * Run time cost includes: @@ -3633,20 +3607,12 @@ estimate_path_cost_size(PlannerInfo *root, * * 3. Run time cost of applying pushed down other clauses on the * result of join - * - * 4. Run time cost of applying nonpushable other clauses locally - * on the result fetched from the foreign server. */ run_cost = fpinfo_i->rel_run_cost; run_cost += fpinfo_o->rel_run_cost; run_cost += nrows * join_cost.per_tuple; nrows = clamp_row_est(nrows * fpinfo->joinclause_sel); run_cost += nrows * remote_conds_cost.per_tuple; - run_cost += fpinfo->local_conds_cost.per_tuple * retrieved_rows; - - /* Add in tlist eval cost for each output row */ - startup_cost += foreignrel->reltarget->cost.startup; - run_cost += foreignrel->reltarget->cost.per_tuple * rows; } else if (IS_UPPER_REL(foreignrel)) { @@ -3748,18 +3714,16 @@ estimate_path_cost_size(PlannerInfo *root, cost_qual_eval(&remote_cost, fpinfo->remote_conds, root); startup_cost += remote_cost.startup; run_cost += remote_cost.per_tuple * numGroups; - /* Add in the eval cost of the locally-checked quals */ - startup_cost += fpinfo->local_conds_cost.startup; - run_cost += fpinfo->local_conds_cost.per_tuple * retrieved_rows; } - /* Add in tlist eval cost for each output row */ - startup_cost += foreignrel->reltarget->cost.startup; - run_cost += foreignrel->reltarget->cost.per_tuple * rows; + /* Add in eval cost of the remotely computed tlist expressions */ + startup_cost += remote_tlist_cost.startup; + run_cost += remote_tlist_cost.per_tuple * rows; } else { Cost cpu_per_tuple; + QualCost remote_conds_cost; /* Use rows/width estimates made by set_baserel_size_estimates. */ rows = foreignrel->rows; @@ -3773,21 +3737,19 @@ estimate_path_cost_size(PlannerInfo *root, retrieved_rows = Min(retrieved_rows, foreignrel->tuples); /* - * Cost as though this were a seqscan, which is pessimistic. We - * effectively imagine the local_conds are being evaluated - * remotely, too. + * Cost this as a seqscan that evaluates the remote_conds on every + * tuple. That is pessimistic because the remote server might be + * able to use an index for them. */ + cost_qual_eval(&remote_conds_cost, fpinfo->remote_conds, root); + startup_cost = 0; run_cost = 0; run_cost += seq_page_cost * foreignrel->pages; - startup_cost += foreignrel->baserestrictcost.startup; - cpu_per_tuple = cpu_tuple_cost + foreignrel->baserestrictcost.per_tuple; + startup_cost += remote_conds_cost.startup; + cpu_per_tuple = cpu_tuple_cost + remote_conds_cost.per_tuple; run_cost += cpu_per_tuple * foreignrel->tuples; - - /* Add in tlist eval cost for each output row */ - startup_cost += foreignrel->reltarget->cost.startup; - run_cost += foreignrel->reltarget->cost.per_tuple * rows; } /* @@ -3837,33 +3799,18 @@ estimate_path_cost_size(PlannerInfo *root, } } - /* - * If this includes the final sort step, the given target, which will be - * applied to the resulting path, might have different expressions from - * the foreignrel's reltarget (see make_sort_input_target()); adjust tlist - * eval costs. - */ - if (fpextra && fpextra->has_final_sort && - fpextra->target != foreignrel->reltarget) - { - QualCost oldcost = foreignrel->reltarget->cost; - QualCost newcost = fpextra->target->cost; - - startup_cost += newcost.startup - oldcost.startup; - run_cost += (newcost.per_tuple - oldcost.per_tuple) * rows; - } - /* * Cache the retrieved rows and cost estimates for scans, joins, or * groupings without any parameterization, pathkeys, or additional * post-scan/join-processing steps, before adding the costs for - * transferring data from the foreign server. These estimates are useful - * for costing remote joins involving this relation or costing other - * remote operations on this relation such as remote sorts and remote - * LIMIT restrictions, when the costs can not be obtained from the foreign - * server. This function will be called at least once for every foreign - * relation without any parameterization, pathkeys, or additional - * post-scan/join-processing steps. + * transferring data from the foreign server and for the local work on the + * fetched rows. These estimates are useful for costing remote joins + * involving this relation or costing other remote operations on this + * relation such as remote sorts and remote LIMIT restrictions, when the + * costs can not be obtained from the foreign server. This function will + * be called at least once for every foreign relation without any + * parameterization, pathkeys, or additional post-scan/join-processing + * steps. */ if (pathkeys == NIL && param_join_conds == NIL && fpextra == NULL) { @@ -3873,15 +3820,52 @@ estimate_path_cost_size(PlannerInfo *root, } /* - * Add some additional cost factors to account for connection overhead - * (fdw_startup_cost), transferring data across the network - * (fdw_tuple_cost per retrieved row), and local manipulation of the data - * (cpu_tuple_cost per retrieved row). + * Add the cost of the connection overhead (fdw_startup_cost) and of + * transferring each retrieved row across the network (fdw_tuple_cost). */ startup_cost += fpinfo->fdw_startup_cost; run_cost += fpinfo->fdw_tuple_cost * retrieved_rows; + + /* + * Add the cost of the local work. For each retrieved row we pay + * cpu_tuple_cost and check the quals that couldn't be sent to the foreign + * server. For each output row we also evaluate the target list. + */ run_cost += cpu_tuple_cost * retrieved_rows; + cost_qual_eval(&local_cost, local_param_join_conds, root); + local_cost.startup += fpinfo->local_conds_cost.startup; + local_cost.per_tuple += fpinfo->local_conds_cost.per_tuple; + startup_cost += local_cost.startup; + run_cost += local_cost.per_tuple * retrieved_rows; + + /* + * If this includes the final sort step, the given target, which will be + * applied to the resulting path, might have different expressions from + * the foreignrel's reltarget (see make_sort_input_target()). + */ + if (fpextra && fpextra->has_final_sort) + target = fpextra->target; + else + target = foreignrel->reltarget; + startup_cost += target->cost.startup; + run_cost += target->cost.per_tuple * rows; + + /* + * The planner computed the cost of the target as if we evaluate all of + * its expressions locally. That is not true for an upper relation. There + * the foreign server computes the expressions in grouped_tlist and we + * only fetch their values. Their cost is already part of the remote + * cost. It is either included in the EXPLAIN result (when using remote + * estimates) or it was added by the heuristic upper relation costing + * above. So we subtract it here to avoid counting it twice. + */ + if (IS_UPPER_REL(foreignrel)) + { + startup_cost -= remote_tlist_cost.startup; + run_cost -= remote_tlist_cost.per_tuple * rows; + } + /* * If we have LIMIT, we should prefer performing the restriction remotely * rather than locally, as the former avoids extra row fetches from the diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index e334217377f..2122d9d89a5 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -67,9 +67,10 @@ typedef struct PgFdwRelationInfo Cost total_cost; /* - * Estimated number of rows fetched from the foreign server, and costs - * excluding costs for transferring those rows from the foreign server. - * These are only used by estimate_path_cost_size(). + * Estimated number of rows fetched from the foreign server, and costs of + * the work done by the foreign server. The costs exclude transferring + * those rows from the foreign server and the local work on them. These + * are only used by estimate_path_cost_size(). */ double retrieved_rows; Cost rel_startup_cost; -- 2.55.0
From 3be0cc13fb47810538bfcc7f6bee51eb0f746891 Mon Sep 17 00:00:00 2001 From: Jelte Fennema-Nio <[email protected]> Date: Sat, 26 Sep 2026 15:37:08 +0200 Subject: [PATCH v2 4/4] postgres_fdw: Fix costing of remote sorts without remote estimates Without use_remote_estimate, postgres_fdw has no way to know what a remote sort costs. The heuristic used so far was to multiply the path's cost by DEFAULT_FDW_SORT_MULTIPLIER (1.2). Commit f18c944b61 introduced it and its message explains that the intent of that constant was to prefer a remote sort over a local one (if a sort is useful). In practice that doesn't actually work in lots of cases though. The surcharge has no relation to the number of rows being sorted, so for an expensive path that produces few rows, such as an aggregate or a join, it is arbitrarily larger than the cost of actually sorting the output. Since the alternative, a local Sort over the unsorted foreign path, is costed accurately, the pushed-down sort always lost in those cases. See the expected regress output changes in the patch for examples. The later commit ffab494a4d ran into this issue too[1]. It tried to fix this in two ways depending on the situation: 1. By calculating what a local sort would be and using that same value for the remote. 2. By reducing DEFAULT_FDW_SORT_MULTIPLIER to 1.05 in one place in the code, which was noted in the thread as being chosen fairly arbitrarily to improve some plans[1]. This commit generalizes that first approach and uses it for every sorted foreign path, with one slight improvement: Instead of using the full cost of the local sort, it's multiplied by a fraction (0.8). That way the remote and local sort don't tie, but the remote sort is preferred. This answers the open question from [1]: no percentage of the path cost is reasonable, because the surcharge should scale with the sort, not with the path. This changes a bunch of plans in our existing tests for the better: 1. Pushing down a Sort node to the remote side 2. Changing a local merge join on top of remotely sorted scan to a hash join over unsorted remote scan. 3. Changing a local merge append over multiple remotely sorted scans to a hash aggregate over unsorted remote scans. [1]: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] --- .../postgres_fdw/expected/postgres_fdw.out | 264 +++++++++--------- contrib/postgres_fdw/postgres_fdw.c | 151 ++++------ contrib/postgres_fdw/sql/postgres_fdw.sql | 6 +- 3 files changed, 193 insertions(+), 228 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 5df5cbdf472..c56f3b9da30 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2037,21 +2037,18 @@ SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT 40 | 0 | AAA040 (10 rows) --- full outer join + WHERE clause, only matched rows +-- full outer join + WHERE clause, only matched rows. The ORDER BY and LIMIT +-- are pushed down too: without remote estimates, a remote sort should be +-- preferred over a local one. EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Limit + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: t1.c1, t2.c1 - -> Sort - Output: t1.c1, t2.c1 - Sort Key: t1.c1, t2.c1 - -> Foreign Scan - Output: t1.c1, t2.c1 - Relations: (public.ft4 t1) FULL JOIN (public.ft5 t2) - Remote SQL: SELECT r1.c1, r2.c1 FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 = r2.c1) OR (r1.c1 IS NULL))) -(9 rows) + Relations: (public.ft4 t1) FULL JOIN (public.ft5 t2) + Remote SQL: SELECT r1.c1, r2.c1 FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 = r2.c1) OR (r1.c1 IS NULL))) ORDER BY r1.c1 ASC NULLS LAST, r2.c1 ASC NULLS LAST LIMIT 10::bigint OFFSET 10::bigint +(4 rows) SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; c1 | c1 @@ -3011,16 +3008,13 @@ ALTER VIEW v4 OWNER TO regress_view_owner; EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.c1, t1.c3 FROM ft1 t1, unnest(ARRAY[1, 5, 10, 100]::int[]) AS u(id) WHERE t1.c1 = u.id ORDER BY t1.c1; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: t1.c1, t1.c3 - Sort Key: t1.c1 - -> Foreign Scan - Output: t1.c1, t1.c3 - Relations: (public.ft1 t1) INNER JOIN (pg_catalog.unnest() u) - Remote SQL: SELECT r1."C 1", r1.c3 FROM ("S 1"."T 1" r1 INNER JOIN unnest('{1,5,10,100}'::integer[]) f2(c1) ON (((r1."C 1" = f2.c1)))) -(7 rows) + Relations: (public.ft1 t1) INNER JOIN (pg_catalog.unnest() u) + Remote SQL: SELECT r1."C 1", r1.c3 FROM ("S 1"."T 1" r1 INNER JOIN unnest('{1,5,10,100}'::integer[]) f2(c1) ON (((r1."C 1" = f2.c1)))) ORDER BY r1."C 1" ASC NULLS LAST +(4 rows) SELECT t1.c1, t1.c3 FROM ft1 t1, unnest(ARRAY[1, 5, 10, 100]::int[]) AS u(id) WHERE t1.c1 = u.id ORDER BY t1.c1; @@ -3036,16 +3030,13 @@ WHERE t1.c1 = u.id ORDER BY t1.c1; EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.c1 FROM ft1 t1, generate_series(1, 4) AS g(id) WHERE t1.c1 = g.id ORDER BY t1.c1; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ - Sort + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: t1.c1 - Sort Key: t1.c1 - -> Foreign Scan - Output: t1.c1 - Relations: (public.ft1 t1) INNER JOIN (pg_catalog.generate_series() g) - Remote SQL: SELECT r1."C 1" FROM ("S 1"."T 1" r1 INNER JOIN generate_series(1, 4) f2(c1) ON (((r1."C 1" = f2.c1)))) -(7 rows) + Relations: (public.ft1 t1) INNER JOIN (pg_catalog.generate_series() g) + Remote SQL: SELECT r1."C 1" FROM ("S 1"."T 1" r1 INNER JOIN generate_series(1, 4) f2(c1) ON (((r1."C 1" = f2.c1)))) ORDER BY r1."C 1" ASC NULLS LAST +(4 rows) SELECT t1.c1 FROM ft1 t1, generate_series(1, 4) AS g(id) WHERE t1.c1 = g.id ORDER BY t1.c1; @@ -3129,20 +3120,22 @@ FROM ft1 t1, ft6 t2, unnest(ARRAY[1, 5, 10, 100]::int[]) AS u(id) WHERE t1.c1 = u.id AND t2.c1 = u.id ORDER BY t1.c1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ - Merge Join + Sort Output: t1.c1, t2.c1 - Merge Cond: (t1.c1 = u.id) - -> Foreign Scan on public.ft1 t1 - Output: t1.c1 - Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST - -> Sort - Output: t2.c1, u.id - Sort Key: t2.c1 + Sort Key: t1.c1 + -> Hash Join + Output: t1.c1, t2.c1 + Hash Cond: (u.id = t1.c1) -> Foreign Scan Output: t2.c1, u.id Relations: (public.ft6 t2) INNER JOIN (pg_catalog.unnest() u) Remote SQL: SELECT r2.c1, f3.c1 FROM ("S 1"."T 4" r2 INNER JOIN unnest('{1,5,10,100}'::integer[]) f3(c1) ON (((r2.c1 = f3.c1)))) -(13 rows) + -> Hash + Output: t1.c1 + -> Foreign Scan on public.ft1 t1 + Output: t1.c1 + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" +(15 rows) -- Cost-based selection between two foreign servers: ft1 ("S 1"."T 1") has -- 1000 rows, ft6 ("S 1"."T 4") has ~33 rows. The same query shape gets a @@ -3384,16 +3377,13 @@ SELECT r.a, t.n, t.s FROM remote_tbl r, ROWS FROM (unnest(array[3, 6, 9]), generate_series(11, 13)) AS t(n, s) WHERE r.a = t.n ORDER BY r.a; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: r.a, t.n, t.s - Sort Key: r.a - -> Foreign Scan - Output: r.a, t.n, t.s - Relations: (public.remote_tbl r) INNER JOIN (ROWS FROM (pg_catalog.unnest(), pg_catalog.generate_series()) t) - Remote SQL: SELECT r1.a, f2.c1, f2.c2 FROM (public.base_tbl_fn r1 INNER JOIN ROWS FROM (unnest('{3,6,9}'::integer[]), generate_series(11, 13)) f2(c1, c2) ON (((r1.a = f2.c1)))) -(7 rows) + Relations: (public.remote_tbl r) INNER JOIN (ROWS FROM (pg_catalog.unnest(), pg_catalog.generate_series()) t) + Remote SQL: SELECT r1.a, f2.c1, f2.c2 FROM (public.base_tbl_fn r1 INNER JOIN ROWS FROM (unnest('{3,6,9}'::integer[]), generate_series(11, 13)) f2(c1, c2) ON (((r1.a = f2.c1)))) ORDER BY r1.a ASC NULLS LAST +(4 rows) SELECT r.a, t.n, t.s FROM remote_tbl r, ROWS FROM (unnest(array[3, 6, 9]), @@ -3414,16 +3404,13 @@ SELECT t::text, r.a FROM remote_tbl r, ROWS FROM (unnest(array[3, 6, 9]), generate_series(11, 13)) AS t(n, s) WHERE r.a = t.n ORDER BY r.a; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Sort - Output: ((t.*)::text), r.a - Sort Key: r.a - -> Foreign Scan - Output: (t.*)::text, r.a - Relations: (public.remote_tbl r) INNER JOIN (ROWS FROM (pg_catalog.unnest(), pg_catalog.generate_series()) t) - Remote SQL: SELECT CASE WHEN (f2.*)::text IS NOT NULL THEN ROW(f2.c1, f2.c2) END, r1.a FROM (public.base_tbl_fn r1 INNER JOIN ROWS FROM (unnest('{3,6,9}'::integer[]), generate_series(11, 13)) f2(c1, c2) ON (((r1.a = f2.c1)))) -(7 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Foreign Scan + Output: (t.*)::text, r.a + Relations: (public.remote_tbl r) INNER JOIN (ROWS FROM (pg_catalog.unnest(), pg_catalog.generate_series()) t) + Remote SQL: SELECT CASE WHEN (f2.*)::text IS NOT NULL THEN ROW(f2.c1, f2.c2) END, r1.a FROM (public.base_tbl_fn r1 INNER JOIN ROWS FROM (unnest('{3,6,9}'::integer[]), generate_series(11, 13)) f2(c1, c2) ON (((r1.a = f2.c1)))) ORDER BY r1.a ASC NULLS LAST +(4 rows) SELECT t::text, r.a FROM remote_tbl r, ROWS FROM (unnest(array[3, 6, 9]), @@ -3611,21 +3598,23 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT r.a FROM remote_tbl r WHERE EXISTS (SELECT 1 FROM unnest(array[3, 6, 9]) AS t(n) WHERE t.n = r.a) ORDER BY r.a; - QUERY PLAN --------------------------------------------------------------------------------- - Merge Semi Join + QUERY PLAN +----------------------------------------------------------------- + Sort Output: r.a - Merge Cond: (r.a = t.n) - -> Foreign Scan on public.remote_tbl r - Output: r.a, r.b - Remote SQL: SELECT a FROM public.base_tbl_fn ORDER BY a ASC NULLS LAST - -> Sort - Output: t.n - Sort Key: t.n - -> Function Scan on pg_catalog.unnest t + Sort Key: r.a + -> Hash Semi Join + Output: r.a + Hash Cond: (r.a = t.n) + -> Foreign Scan on public.remote_tbl r + Output: r.a, r.b + Remote SQL: SELECT a FROM public.base_tbl_fn + -> Hash Output: t.n - Function Call: unnest('{3,6,9}'::integer[]) -(12 rows) + -> Function Scan on pg_catalog.unnest t + Output: t.n + Function Call: unnest('{3,6,9}'::integer[]) +(14 rows) DROP FOREIGN TABLE remote_tbl; DROP TABLE base_tbl_fn; @@ -4211,16 +4200,13 @@ select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1; -- Ordered-sets within aggregate explain (verbose, costs off) select c2, rank('10'::varchar) within group (order by c6), percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: c2, (rank('10'::character varying) WITHIN GROUP (ORDER BY c6)), (percentile_cont((((c2)::numeric / '10'::numeric))::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision))) - Sort Key: ft1.c2 - -> Foreign Scan - Output: c2, (rank('10'::character varying) WITHIN GROUP (ORDER BY c6)), (percentile_cont((((c2)::numeric / '10'::numeric))::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision))) - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, rank('10'::character varying) WITHIN GROUP (ORDER BY c6 ASC NULLS LAST), percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1") ASC NULLS LAST) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1 HAVING ((percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1") ASC NULLS LAST) < 500::double precision)) -(7 rows) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT c2, rank('10'::character varying) WITHIN GROUP (ORDER BY c6 ASC NULLS LAST), percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1") ASC NULLS LAST) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1 HAVING ((percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1") ASC NULLS LAST) < 500::double precision)) ORDER BY c2 ASC NULLS LAST +(4 rows) select c2, rank('10'::varchar) within group (order by c6), percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2; c2 | rank | percentile_cont @@ -4276,18 +4262,17 @@ alter extension postgres_fdw add function least_accum(anyelement, variadic anyar alter extension postgres_fdw add aggregate least_agg(variadic items anyarray); alter server loopback options (set extensions 'postgres_fdw'); -- Now aggregate will be pushed. Aggregate will display VARIADIC argument. +-- The ORDER BY is pushed down along with it: without remote estimates, a +-- remote sort should be preferred over a local one. explain (verbose, costs off) select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ - Sort + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: c2, (least_agg(VARIADIC ARRAY[c1])) - Sort Key: ft1.c2 - -> Foreign Scan - Output: c2, (least_agg(VARIADIC ARRAY[c1])) - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, public.least_agg(VARIADIC ARRAY["C 1"]) FROM "S 1"."T 1" WHERE ((c2 < 100)) GROUP BY 1 -(7 rows) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT c2, public.least_agg(VARIADIC ARRAY["C 1"]) FROM "S 1"."T 1" WHERE ((c2 < 100)) GROUP BY 1 ORDER BY c2 ASC NULLS LAST +(4 rows) select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; c2 | least_agg @@ -11372,16 +11357,18 @@ ANALYZE fpagg_tab_p3; SET enable_partitionwise_aggregate TO false; EXPLAIN (COSTS OFF) SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; - QUERY PLAN ------------------------------------------------------ - GroupAggregate - Group Key: pagg_tab.a - Filter: (avg(pagg_tab.b) < '22'::numeric) - -> Append - -> Foreign Scan on fpagg_tab_p1 pagg_tab_1 - -> Foreign Scan on fpagg_tab_p2 pagg_tab_2 - -> Foreign Scan on fpagg_tab_p3 pagg_tab_3 -(7 rows) + QUERY PLAN +----------------------------------------------------------- + Sort + Sort Key: pagg_tab.a + -> HashAggregate + Group Key: pagg_tab.a + Filter: (avg(pagg_tab.b) < '22'::numeric) + -> Append + -> Foreign Scan on fpagg_tab_p1 pagg_tab_1 + -> Foreign Scan on fpagg_tab_p2 pagg_tab_2 + -> Foreign Scan on fpagg_tab_p3 pagg_tab_3 +(9 rows) -- Plan with partitionwise aggregates is enabled SET enable_partitionwise_aggregate TO true; @@ -11415,32 +11402,34 @@ SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 O -- Should have all the columns in the target list for the given relation EXPLAIN (VERBOSE, COSTS OFF) SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; - QUERY PLAN --------------------------------------------------------------------------------------------- - Merge Append + QUERY PLAN +------------------------------------------------------------------------ + Sort + Output: t1.a, (count(((t1.*)::pagg_tab))) Sort Key: t1.a - -> GroupAggregate - Output: t1.a, count(((t1.*)::pagg_tab)) - Group Key: t1.a - Filter: (avg(t1.b) < '22'::numeric) - -> Foreign Scan on public.fpagg_tab_p1 t1 - Output: t1.a, t1.*, t1.b - Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1 ORDER BY a ASC NULLS LAST - -> GroupAggregate - Output: t1_1.a, count(((t1_1.*)::pagg_tab)) - Group Key: t1_1.a - Filter: (avg(t1_1.b) < '22'::numeric) - -> Foreign Scan on public.fpagg_tab_p2 t1_1 - Output: t1_1.a, t1_1.*, t1_1.b - Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2 ORDER BY a ASC NULLS LAST - -> GroupAggregate - Output: t1_2.a, count(((t1_2.*)::pagg_tab)) - Group Key: t1_2.a - Filter: (avg(t1_2.b) < '22'::numeric) - -> Foreign Scan on public.fpagg_tab_p3 t1_2 - Output: t1_2.a, t1_2.*, t1_2.b - Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3 ORDER BY a ASC NULLS LAST -(23 rows) + -> Append + -> HashAggregate + Output: t1.a, count(((t1.*)::pagg_tab)) + Group Key: t1.a + Filter: (avg(t1.b) < '22'::numeric) + -> Foreign Scan on public.fpagg_tab_p1 t1 + Output: t1.a, t1.*, t1.b + Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1 + -> HashAggregate + Output: t1_1.a, count(((t1_1.*)::pagg_tab)) + Group Key: t1_1.a + Filter: (avg(t1_1.b) < '22'::numeric) + -> Foreign Scan on public.fpagg_tab_p2 t1_1 + Output: t1_1.a, t1_1.*, t1_1.b + Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2 + -> HashAggregate + Output: t1_2.a, count(((t1_2.*)::pagg_tab)) + Group Key: t1_2.a + Filter: (avg(t1_2.b) < '22'::numeric) + -> Foreign Scan on public.fpagg_tab_p3 t1_2 + Output: t1_2.a, t1_2.*, t1_2.b + Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3 +(25 rows) SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; a | count @@ -11456,23 +11445,24 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; -- When GROUP BY clause does not match with PARTITION KEY. EXPLAIN (COSTS OFF) SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1; - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------- Finalize GroupAggregate Group Key: pagg_tab.b Filter: (sum(pagg_tab.a) < 700) - -> Merge Append + -> Sort Sort Key: pagg_tab.b - -> Partial GroupAggregate - Group Key: pagg_tab.b - -> Foreign Scan on fpagg_tab_p1 pagg_tab - -> Partial GroupAggregate - Group Key: pagg_tab_1.b - -> Foreign Scan on fpagg_tab_p2 pagg_tab_1 - -> Partial GroupAggregate - Group Key: pagg_tab_2.b - -> Foreign Scan on fpagg_tab_p3 pagg_tab_2 -(14 rows) + -> Append + -> Partial HashAggregate + Group Key: pagg_tab.b + -> Foreign Scan on fpagg_tab_p1 pagg_tab + -> Partial HashAggregate + Group Key: pagg_tab_1.b + -> Foreign Scan on fpagg_tab_p2 pagg_tab_1 + -> Partial HashAggregate + Group Key: pagg_tab_2.b + -> Foreign Scan on fpagg_tab_p3 pagg_tab_2 +(15 rows) -- =================================================================== -- access rights and superuser diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 101d161cb68..6d6ab64ce92 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -68,8 +68,15 @@ PG_MODULE_MAGIC_EXT( /* Default CPU cost to process 1 row (above and beyond cpu_tuple_cost). */ #define DEFAULT_FDW_TUPLE_COST 0.2 -/* If no remote estimates, assume a sort costs 20% extra */ -#define DEFAULT_FDW_SORT_MULTIPLIER 1.2 +/* + * If no remote estimates, charge this fraction of a local sort's cost for a + * remote sort. It needs to be clearly below 1 so that a remote sort beats a + * local Sort of the same rows, and clearly above 0 so that a sorted path + * doesn't look free when the ordering is only potentially useful (e.g. for a + * merge join). Within that range the exact value is arbitrary. See + * adjust_foreign_path_cost_for_sort(). + */ +#define DEFAULT_FDW_SORT_COST_FRACTION 0.8 /* * Indexes of FDW-private information stored in fdw_private lists. @@ -524,12 +531,9 @@ static void get_remote_estimate(const char *sql, int *width, Cost *startup_cost, Cost *total_cost); -static void adjust_foreign_grouping_path_cost(PlannerInfo *root, - List *pathkeys, - double retrieved_rows, - double width, +static void adjust_foreign_path_cost_for_sort(PlannerInfo *root, List *pathkeys, + double retrieved_rows, double width, double limit_tuples, - int *p_disabled_nodes, Cost *p_startup_cost, Cost *p_run_cost); static bool ec_member_matches_foreign(PlannerInfo *root, RelOptInfo *rel, @@ -3754,38 +3758,14 @@ estimate_path_cost_size(PlannerInfo *root, /* * Without remote estimates, we have no real way to estimate the cost - * of generating sorted output. It could be free if the query plan - * the remote side would have chosen generates properly-sorted output - * anyway, but in most cases it will cost something. Estimate a value - * high enough that we won't pick the sorted path when the ordering - * isn't locally useful, but low enough that we'll err on the side of - * pushing down the ORDER BY clause when it's useful to do so. + * of generating sorted output, so use a heuristic. See + * adjust_foreign_path_cost_for_sort(). */ if (pathkeys != NIL) - { - if (IS_UPPER_REL(foreignrel)) - { - Assert(foreignrel->reloptkind == RELOPT_UPPER_REL && - fpinfo->stage == UPPERREL_GROUP_AGG); - - /* - * We can only get here when this function is called from - * add_foreign_ordered_paths() or add_foreign_final_paths(); - * in which cases, the passed-in fpextra should not be NULL. - */ - Assert(fpextra); - adjust_foreign_grouping_path_cost(root, pathkeys, - retrieved_rows, width, - fpextra->limit_tuples, - &disabled_nodes, - &startup_cost, &run_cost); - } - else - { - startup_cost *= DEFAULT_FDW_SORT_MULTIPLIER; - run_cost *= DEFAULT_FDW_SORT_MULTIPLIER; - } - } + adjust_foreign_path_cost_for_sort(root, pathkeys, + retrieved_rows, width, + fpextra ? fpextra->limit_tuples : -1.0, + &startup_cost, &run_cost); /* Adjust the cost estimates if we have LIMIT */ if (fpextra && fpextra->has_limit) @@ -3874,11 +3854,10 @@ estimate_path_cost_size(PlannerInfo *root, * restriction (see create_limit_path()), there would be no difference * between the costs of the local restriction and the costs of the remote * restriction estimated above if we don't use remote estimates (except - * for the case where the foreignrel is a grouping relation, the given - * pathkeys is not NIL, and the effects of a bounded sort for that rel is - * accounted for in costing the remote restriction). Tweak the costs of - * the remote restriction to ensure we'll prefer it if LIMIT is a useful - * one. + * for the case where the given pathkeys is not NIL, and the effects of a + * bounded sort are accounted for in costing the remote restriction). + * Tweak the costs of the remote restriction to ensure we'll prefer it if + * LIMIT is a useful one. */ if (!fpinfo->use_remote_estimate && fpextra && fpextra->has_limit && @@ -3936,58 +3915,50 @@ get_remote_estimate(const char *sql, PGconn *conn, } /* - * Adjust the cost estimates of a foreign grouping path to include the cost of - * generating properly-sorted output. + * Adjust the given path costs for having the remote side sort its output, + * when no remote estimates are available. + * + * We can't accurately estimate a remote sort; it might even be free if the + * remote plan happens to produce the order (e.g. a sorted aggregate). What we + * do know is that the alternative is the unsorted path plus a local Sort of + * the same rows. We assume that the remote can sort at least as cheaply as we + * can. So charge a fraction of the cost of a local Sort: enough to beat it, + * not so little that the sorted path looks free. + * + * Like a local Sort, a remote sort is blocking, so all of the input cost + * becomes startup cost. Besides being accurate, that also matters when the + * ordering is merely potentially useful (e.g. for a merge join): the sorted + * path then shares a pathlist with the unsorted path, and add_path() lets + * fuzzily equal costs be decided by pathkeys. A surcharge on the run cost + * alone can be within that fuzz for a small table, so the sorted path would + * prune the unsorted one and force every consumer to sort remotely. With + * the input cost moved to startup, the sorted path is always clearly worse + * on startup cost, so both survive and the consumer gets to choose. */ static void -adjust_foreign_grouping_path_cost(PlannerInfo *root, - List *pathkeys, - double retrieved_rows, - double width, +adjust_foreign_path_cost_for_sort(PlannerInfo *root, List *pathkeys, + double retrieved_rows, double width, double limit_tuples, - int *p_disabled_nodes, - Cost *p_startup_cost, - Cost *p_run_cost) + Cost *p_startup_cost, Cost *p_run_cost) { - /* - * If the GROUP BY clause isn't sort-able, the plan chosen by the remote - * side is unlikely to generate properly-sorted output, so it would need - * an explicit sort; adjust the given costs with cost_sort(). Likewise, - * if the GROUP BY clause is sort-able but isn't a superset of the given - * pathkeys, adjust the costs with that function. Otherwise, adjust the - * costs by applying the same heuristic as for the scan or join case. - */ - if (!grouping_is_sortable(root->processed_groupClause) || - !pathkeys_contained_in(pathkeys, root->group_pathkeys)) - { - Path sort_path; /* dummy for result of cost_sort */ - - cost_sort(&sort_path, - root, - pathkeys, - 0, - *p_startup_cost + *p_run_cost, - retrieved_rows, - width, - 0.0, - work_mem, - limit_tuples); - - *p_startup_cost = sort_path.startup_cost; - *p_run_cost = sort_path.total_cost - sort_path.startup_cost; - } - else - { - /* - * The default extra cost seems too large for foreign-grouping cases; - * add 1/4th of that default. - */ - double sort_multiplier = 1.0 + (DEFAULT_FDW_SORT_MULTIPLIER - - 1.0) * 0.25; - - *p_startup_cost *= sort_multiplier; - *p_run_cost *= sort_multiplier; - } + Cost input_cost = *p_startup_cost + *p_run_cost; + Path sort_path; /* dummy for result of cost_sort */ + + cost_sort(&sort_path, + root, + pathkeys, + 0, + input_cost, + retrieved_rows, + width, + 0.0, + work_mem, + limit_tuples); + + *p_startup_cost = input_cost + + (sort_path.startup_cost - input_cost) * DEFAULT_FDW_SORT_COST_FRACTION; + *p_run_cost = (sort_path.total_cost - sort_path.startup_cost) * + DEFAULT_FDW_SORT_COST_FRACTION; } /* diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 8e9186be92b..0745e36135c 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -681,7 +681,9 @@ RESET enable_memoize; EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; --- full outer join + WHERE clause, only matched rows +-- full outer join + WHERE clause, only matched rows. The ORDER BY and LIMIT +-- are pushed down too: without remote estimates, a remote sort should be +-- preferred over a local one. EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; @@ -1304,6 +1306,8 @@ alter extension postgres_fdw add aggregate least_agg(variadic items anyarray); alter server loopback options (set extensions 'postgres_fdw'); -- Now aggregate will be pushed. Aggregate will display VARIADIC argument. +-- The ORDER BY is pushed down along with it: without remote estimates, a +-- remote sort should be preferred over a local one. explain (verbose, costs off) select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; -- 2.55.0
