Re: Why is DEFAULT_FDW_TUPLE_COST so insanely low?

On Tue, 31 Oct 2023 at 03:01, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> I think you just go and change it. Your number is better than what we
> have, and if someone wants to suggest a better number, we can change it
> later.

I did some more experimentation on the actual costs of getting a tuple
from a foreign server.

Using the attached setup, I did:

postgres=# explain (analyze, timing off) SELECT * FROM t;
QUERY PLAN
--------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..144248.48 rows=10000048 width=4) (actual
rows=10000000 loops=1)
Planning Time: 0.077 ms
Execution Time: 385.978 ms

postgres=# explain (analyze, timing off) SELECT * FROM ft;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Foreign Scan on ft (cost=100.00..244348.96 rows=10000048 width=4)
(actual rows=10000000 loops=1)
Planning Time: 0.126 ms
Execution Time: 8335.392 ms

So, let's take the first query and figure out the total cost per
millisecond of execution time. We can then multiply that by the
execution time of the 2nd query to calculate what we might expect the
costs to be for the foreign table scan based on how long it took
compared to the local table scan.

postgres=# select 144248.48/385.978*8335.392;
?column?
-----------------------------
3115119.5824740270171341280

So, the above number is what we expect the foreign table scan to cost
with the assumption that the cost per millisecond is about right for
the local scan. We can then calculate how much we'll need to charge
for a foreign tuple by subtracting the total cost of that query from
our calculated value to calculate how much extra we need to charge, in
total, then divide that by the number of tuples to get actual foreign
tuple cost for this query.

postgres=# select (3115119.58-244348.96)/10000000;
?column?
------------------------
0.28707706200000000000

This is on an AMD 3990x running Linux 6.5 kernel. I tried the same on
an Apple M2 mini and got:

postgres=# select 144247.77/257.763*3052.084;
?column?
-----------------------------
1707988.7759402241595200680

postgres=# select (1707988.78-244347.54)/10000000;
?column?
------------------------
0.14636412400000000000

So the actual foreign tuple cost on the M2 seems about half of what it
is on the Zen 2 machine.

Based on this, I agree with my original analysis that setting
DEFAULT_FDW_TUPLE_COST to 0.2 is about right. Of course, this is a
loopback onto localhost so remote networks likely would benefit from
higher values, but based on this 0.01 is far too low and we should
change it to at least 0.2.

I'd be happy if anyone else would like to try the same experiment to
see if there's some other value of DEFAULT_FDW_TUPLE_COST that might
suit better.

David