Re: postgres_fdw: Fix costing of remote sorts without remote estimates

Hi,

Thank you very much for the work and the excellent explanation of the approach.

Please find a few questions / observations inline,

On Tue, Sep 29, 2026 at 10:10 AM Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> wrote:
>
> tl;dr simpler code that results in better plans
>
> 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.

* Regression tests check plan shape only. However, a visually
inspected better looking plan might not be a faster plan.
* Regression tests might not contain sufficient data size for us to
draw a strong conclusion. Sort cost grows as N·log N while scan cost
grows linearly. Behavior at 10M+ rows could be quite different.
* A corner case would be if the remote has an index on the sort key,
the real sort is nearly free. The new costing charges 80% of a full
sort, which could push the planner away from remote-ordered scans it
used to pick for large base tables.
* The patch in a way calls out that 1.2 and 1.05 were arbitrary. It
then brings in 0.8 without showing why 0.8 is better than 0.6 or 0.95.
Wouldn't the same criticism apply to the new constant.
* The patch mentions that the new plans are better, we should probably
compare the plans with use_remote_estimate = true. The remote planner
is aware of things the local heuristic can't fathom, e.g. Remote
indexes etc. What would happen in these cases ?

>
> 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.

To make a convincing case for this patch, I would,

1. Run the regression and benchmark queries with use_remote_estimate =
true and record those plans. Then show that the new local-estimate
plans match them more often than the old ones do.
2. Try TPC-H or TPC-DS over a sharded postgres_fdw setup. Report how
many plans changed, how many got faster or slower, and by how much.
3. Try different values for the heuristic fraction.
4. Look for worst cases and regressions with the current heuristic value.

Currently I don't have time to test with the above variations. I will
bookmark this work and try out some tests when I get some cycles.

Thank you once again for the work,
Narayanan

>
> [1]: https://postgr.es/m/5C232F39.9060509@lab.ntt.co.jp