Re: postgres_fdw: Fix costing of remote sorts without remote estimates - Mailing list pgsql-hackers

On Thu, 1 Oct 2026 at 02:12, Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:

meta: Can you please not copy paste your LLM output verbatim?

> * Regression tests check plan shape only. However, a visually
> inspected better looking plan might not be a faster plan.

The logic in postgres_fdw is that more pushdown to the remote is
always better. No need for benchmarks.

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

No, because the local sort would still be more expensive than the remote sort.

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

Yes it's arbitrary, but the exact constant doesn't matter. I tried
different factors. As long as the remote sort is significantly cheaper
than a local sort it will always be picked.

> * 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 ?

We have tests, they don't change. This logic is only used when remote
estimates are unavailable.

On Thu, 1 Oct 2026 at 02:12, Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:
>
> 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@jeltef.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