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.
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/5C232F39.9060509@lab.ntt.co.jp
