Re: Why is DEFAULT_FDW_TUPLE_COST so insanely low?

Looks like the value goes long back to
d0d75c402217421b691050857eb3d7af82d0c770. The comment there adds
"above and beyond cpu_tuple_cost". So certainly it's expected to be
higher than cpu_tuple_cost. I have no memories of this. But looking at
the surrounding code, I think DEFAULT_FDW_STARTUP_COST takes care of
network costs and bandwidths. So DEFAULT_FDW_TUPLE_COST is just
assembling row from bytes on network. That might have been equated to
assembling row from heap buffer.

But I think you are right, it should be comparable to the parallel
tuple cost which at least is IPC like socket. This will also mean that
operations which reduce the number of rows will be favoured and pushed
down. That's what is desired.

--
Best Wishes,
Ashutosh Bapat

On Mon, Oct 30, 2023 at 6:52 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Sun, 29 Oct 2023 at 12:45, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Has anything been done about this issue?
>
> Nothing has been done. I was hoping to get the attention of a few
> people who have dealt more with postgres_fdw in the past.
>
> I've attached a patch with adjusts DEFAULT_FDW_TUPLE_COST and sets it
> to 0.2. I set it to this because my experiment in [1] showed that it
> was about 21x lower than the actual costs (on my machine with a
> loopback fdw connecting to the same instance and database using my
> example query). Given that we have parallel_tuple_cost set to 0.1 by
> default, the network cost of a tuple from an FDW of 0.2 seems
> reasonable to me. Slightly higher is probably also reasonable, but
> given the seeming lack of complaints, I think I'd rather err on the
> low side.
>
> Changing it to 0.2, I see 4 plans change in postgres_fdw's regression
> tests. All of these changes are due to STD_FUZZ_FACTOR causing some
> other plan to win in add_path().
>
> For example the query EXPLAIN (VERBOSE, ANALYZE) SELECT a, sum(b),
> min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY
> 1; the plan switches from a HashAggregate to a GroupAggregate. This is
> because after increasing the DEFAULT_FDW_TUPLE_COST to 0.2 the sorted
> append child (fuzzily) costs the same as the unsorted seq scan path
> and the sorted path wins in add_path due to having better pathkeys.
> The seq scan path is then thrown away and we end up doing the Group
> Aggregate using the sorted append children.
>
> If I change STD_FUZZ_FACTOR to something like 1.0000001 then the plans
> no longer change when I do:
>
> alter server loopback options (add fdw_tuple_cost '0.01');
> <run the query>
> alter server loopback options (drop fdw_tuple_cost);
> <run the query>
>
> Ordinarily, I'd not care too much about that, but I did test the
> performance of one of the plans and the new plan came out slower than
> the old one.
>
> I'm not exactly sure how best to proceed on this in the absence of any feedback.
>
> David
>
> [1] https://postgr.es/m/CAApHDvopVjjfh5c1Ed2HRvDdfom2dEpMwwiu5-f1AnmYprJngA@mail.gmail.com