Re: postgres_fdw: Fix costing of remote sorts without remote estimates

Hi Jelte,

Thanks for separating the remote and local costs. The patch order is
clearer, and both examples from my last mail now get the expected plans.
I found two remaining aggregate-costing cases in 0003.

The attached patches add the examples below as regression tests.
They use ft1 and ft2 from postgres_fdw.sql, and your local_filter and
local_project functions.

1. My 0001 fixes the missing remote projection cost for rows filtered
out by local HAVING.

CREATE FUNCTION remote_value(bigint) RETURNS bigint
LANGUAGE plpgsql IMMUTABLE COST 100 AS $$
BEGIN
RETURN $1;
END
$$;
-- Allow remote_value() to be pushed down to the remote server.
ALTER EXTENSION postgres_fdw ADD FUNCTION remote_value(bigint);
ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');

EXPLAIN (VERBOSE)
SELECT c2, remote_value(sum(c1)), count(*)
FROM ft1 GROUP BY c2 ORDER BY c2;

EXPLAIN (VERBOSE)
SELECT c2, remote_value(sum(c1)), count(*)
FROM ft1 GROUP BY c2
HAVING local_filter(count(*)::int) ORDER BY c2;

Both are Foreign Scans with exactly the same Remote SQL:

SELECT c2, public.remote_value(sum("C 1")), count(*)
FROM "S 1"."T 1" GROUP BY 1 ORDER BY c2 ASC NULLS LAST

Their estimated startup costs are:

v2 v2 + my 0001 + my 0002
without local HAVING 131.23 131.23
with local HAVING 129.48 131.23

The reason is that estimate_path_cost_size() adds
remote_tlist_cost.per_tuple * rows before costing the sort. Here rows
already includes the local HAVING selectivity: only 3 rows are expected
to remain out of the 10 fetched rows.
With remote_value's declared COST of 100, this removes
7 * 100 * cpu_operator_cost = 1.75 from startup cost, although the
remote server still evaluates remote_value() for all 10 fetched rows.
EXPLAIN ANALYZE returned 10 rows in both cases.

0001 uses retrieved_rows for that charge. Its test raises remote_value's
COST from 100 to 200 to check that startup cost increases by
(200 - 100) * 10 * cpu_operator_cost for the 10 fetched rows:

ALTER FUNCTION remote_value(bigint) COST 200;
EXPLAIN (VERBOSE)
SELECT c2, remote_value(sum(c1)), count(*)
FROM ft1 GROUP BY c2
HAVING local_filter(count(*)::int) ORDER BY c2;

With 0001, the increase is charged for all 10 rows as expected.

2. My 0002 fixes duplicate charges for remotely computed expressions
in local projection costs.

CREATE FUNCTION remote_key(int) RETURNS int
LANGUAGE plpgsql IMMUTABLE COST 100 AS $$
BEGIN
RETURN $1;
END
$$;
ALTER EXTENSION postgres_fdw ADD FUNCTION remote_key(int);
ALTER FUNCTION local_project(int) COST 9;

EXPLAIN (VERBOSE)
SELECT local_project(remote_key(c2)), count(*)
FROM ft1 GROUP BY remote_key(c2) ORDER BY remote_key(c2);

EXPLAIN (VERBOSE)
SELECT local_project(remote_key(c2)),
local_project(remote_key(c2) + 1), count(*)
FROM ft1 GROUP BY remote_key(c2) ORDER BY remote_key(c2);

Again, both are Foreign Scans with the same Remote SQL:

SELECT count(*), public.remote_key(c2) FROM "S 1"."T 1"
GROUP BY 2 ORDER BY public.remote_key(c2) ASC NULLS LAST

Their estimated total costs are:

v2 v2 + my 0001 + my 0002
one local projection 383.58 381.08
two local projections 386.33 381.33
increase 2.75 0.25

Compared with one local projection, two local projections should add
only 0.25: 10 * (9 + 1) * cpu_operator_cost. Each of the 10 rows needs
one extra local_project() call, with COST 9, and one integer addition,
with cost 1.

On v2, the increase is 0.25 + 2.50 = 2.75. The extra 2.50 is
10 * 100 * cpu_operator_cost: another charge for remote_key(), whose
result is already fetched. Here 100 is remote_key's declared COST.

The reason is that estimate_path_cost_size() adds the whole target cost,
then subtracts the remote tlist cost once:

run_cost += target->cost.per_tuple * rows;
if (IS_UPPER_REL(foreignrel))
run_cost -= remote_tlist_cost.per_tuple * rows;

Even the first query counts remote_key(c2) twice in the target: once
inside local_project() and once as the grouping/sort output. The remote
tlist cost is subtracted only once. The second local projection adds
another reference, but the deduction stays the same.

0002 adds replace_remote_projection_exprs() to replace every reference
to a fetched expression with a Var in a copy used for costing. It then
costs that copy, charging only the local work.

The two attachments apply on top of v2, in order. Core regression and
the postgres_fdw tests passed.

Regards,
Rui