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

Hi,

It is very kind of you to reply to my comments on the work. This is
very nice work, thank you.

On Thu, Oct 1, 2026 at 8:17 PM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
>
> 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?

You observed correctly. I was unfamiliar with this part of the
codebase and did use LLMs to understand the area better. I posted only
what I understood and was hoping that my comments will give attention
to good work.

Thank you,
Narayanan

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