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

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