[PATCH v1] postgres_fdw: Fix local costing of remote quals after semi-joins

I'd like to add this patch to the upcoming CommitFest, but my PostgreSQL community account is still within the new-account cool-off period, so I currently cannot log in to commitfest.postgresql.org.

Could an admin please expedite the cool-off period for my account?

My Community account: toren.zhang@outlook.com

Thanks
________________________________
发件人: 张 子鸣 <toren.zhang@outlook.com>
发送时间: 2026年9月10日 21:28
收件人: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
主题: [PATCH v1] postgres_fdw: Fix local costing of remote quals after semi-joins

Hi,

The thread that added semi-join pushdown to postgres_fdw also contains
a report of a semi-join pushdown path not being selected with local
estimates. That was identified as a costing issue, with
use_remote_estimate=true mentioned as a workaround [1]https://jpn01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fflat%2Fc9e2a757cf3ac2333714eaf83a9cc184%40postgrespro.ru&amp;data=05%7C02%7C%7C9a1904d440044098d93408df0fbe56b5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639246982314278104%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&amp;sdata=BX%2BSR2N%2FJGboI%2FqhLve9TDz4G2VlfFfTccdqvwCcvrQ%3D&amp;reserved=0&lt;https://www.postgresql.org/message-id/flat/c9e2a757cf3ac2333714eaf83a9cc184@postgrespro.ru&gt;.

While investigating local costing of pushed-down semi-joins, I found
a specific error in estimate_path_cost_size().

For an ordinary join, postgres_fdw estimates the number of rows
surviving the join clauses by applying joinclause_sel to the cross
product of the input relations:

outer_rows * inner_rows * joinclause_sel

This is not correct for JOIN_SEMI. In this case, joinclause_sel is
defined as the fraction of outer rows that have a match in the inner
relation. The corresponding estimate should be:

outer_rows * joinclause_sel

For example, consider a semi-join with 15000 rows on each side, where
all outer rows have a match. The current calculation estimates:

15000 * 15000 * 1.0 = 225000000 rows

The semi-join can actually produce at most 15000 rows. The correct
estimate for this example is:

15000 * 1.0 = 15000 rows

The incorrect value is subsequently used to cost remotely executable
conditions applied to the result of the join. Their run cost is
therefore inflated by a factor equal to the number of inner rows. In
some cases this makes the foreign join path more expensive than a
local semi-join and prevents the join from being pushed down.

The attached patch uses the outer relation's row count when applying
joinclause_sel for JOIN_SEMI. Costing for other join types is left
unchanged. It also adds a regression test covering the affected path
selection.

The postgres_fdw regression and isolation tests pass with the patch.

Please find the patch attached. Comments and suggestions would be
appreciated.

[1]: https://jpn01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fflat%2Fc9e2a757cf3ac2333714eaf83a9cc184%40postgrespro.ru&amp;data=05%7C02%7C%7C9a1904d440044098d93408df0fbe56b5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639246982314278104%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&amp;sdata=BX%2BSR2N%2FJGboI%2FqhLve9TDz4G2VlfFfTccdqvwCcvrQ%3D&amp;reserved=0&lt;https://www.postgresql.org/message-id/flat/c9e2a757cf3ac2333714eaf83a9cc184@postgrespro.ru&gt;
https://jpn01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fflat%2Fc9e2a757cf3ac2333714eaf83a9cc184%40postgrespro.ru&amp;data=05%7C02%7C%7C9a1904d440044098d93408df0fbe56b5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639246982314278104%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&amp;sdata=BX%2BSR2N%2FJGboI%2FqhLve9TDz4G2VlfFfTccdqvwCcvrQ%3D&amp;reserved=0&lt;https://www.postgresql.org/message-id/flat/c9e2a757cf3ac2333714eaf83a9cc184@postgrespro.ru&gt;

Regards,
Ziming Zhang

Hi,

Since v1 did not receive any replies, I thought I would try to explain
the issue more clearly, this time with a demonstration where the plan
actually changes.

Attached is v2. The code change itself is unchanged; I have replaced
the regression test because the v1 test produced the same EXPLAIN
output with and without the fix.

PROBLEM DESCRIPTION

When postgres_fdw estimates locally whether a join is worth pushing
down (use_remote_estimate = off, the default),
estimate_path_cost_size() does:

nrows = fpinfo_i->rows * fpinfo_o->rows;
...
run_cost += nrows * join_cost.per_tuple;
nrows = clamp_row_est(nrows * fpinfo->joinclause_sel);
run_cost += nrows * remote_conds_cost.per_tuple;

For an inner join, joinclause_sel is a probability per cross-product
row pair, so scaling the cross product by it yields the join's output
size. For a semi join, however, joinclause_sel (computed by
clauselist_selectivity(), i.e. eqjoinsel_semi()) represents the
fraction of outer rows that have at least one match in the inner
relation -- a probability per outer row, rather than per row pair.

As a result, multiplying it into the cross product appears to charge
the remote quals on

outer_rows * inner_rows * P(match)

rows, while the semi join can produce at most

outer_rows * P(match)

rows.

This can inflate that cost term by roughly the inner relation's row
count and make semi-join pushdown look more expensive than it actually
is.

This also seems consistent with how foreign_join_ok() already treats
post-join local conditions: they are quals applied to the join output,
rather than predicates whose selectivity is combined with the cross
product.

DEMONSTRATION

Using loopback postgres_fdw with all cost settings at their defaults,
the inner table holds the 100 distinct values 0-99; the outer table
has 200 rows (a = 1..200) whose join column is b = i % 100. Every
outer row has a match, so the planner estimates the semi join's output
at the full 200 outer rows (joinclause_sel = 1), while the cross
product is 200 * 100 = 20,000 rows.

The query also has five pushed-down quals on the outer relation:

SELECT t1.a
FROM ft1 t1
WHERE t1.a >= 0 AND t1.a >= -1 AND t1.a >= -2
AND t1.a >= -3 AND t1.a >= -4
AND EXISTS (SELECT 1 FROM ft2 t2 WHERE t2.b = t1.b);

Without the patch, the planner chooses to fetch both tables and perform
the semi join locally:

Hash Semi Join (cost=224.25..274.50 rows=200)
Hash Cond: (t1.b = t2.b)
-> Foreign Scan on ft1 t1
-> Hash
-> Foreign Scan on ft2 t2

With the patch, the whole semi join is pushed down as one remote query
(abridged):

Foreign Scan (cost=100.00..202.00 rows=200)
Relations: (ft1 t1) SEMI JOIN (ft2 t2)
Remote SQL: SELECT r1.a FROM t1 r1 WHERE ((r1.a >= 0)) AND ...
AND EXISTS (SELECT NULL FROM t2 r2
WHERE ((r1.b = r2.b)))

For comparison, I made the losing pushed-down path visible before the
patch by temporarily disabling the local join strategies; no cost
parameter was changed:

before after
startup 100.00 100.00
other costs 49.50 49.50
join quals on cross
product 50.00 50.00
pushed-down quals 250.00 2.50
------------------------------------------------
total 449.50 202.00

local semi join 274.50 274.50

The total costs above come from EXPLAIN; the individual components
follow from the cost model's inputs. The cross product is 20,000
rows, joinclause_sel is 1, and each of the five quals costs one
cpu_operator_cost (0.0025) per row. Thus the remote quals contribute

20,000 * 5 * 0.0025 = 250.00

to the cost before the fix, instead of

200 * 5 * 0.0025 = 2.50.

The 247.50 difference accounts for the change in the pushed-down path's
total cost, which in this example is enough to change the selected plan
from the 274.50 local semi join to the 202.00 pushed-down path.

The new regression test uses this setup. At default settings it plans
the local semi join without the fix and the pushed-down Foreign Scan
with it. The postgres_fdw regression suite passes with the fix; with
only the code change reverted, this new test fails.

I've registered the patch in the PG20-3 CommitFest.

I would appreciate any comments or suggestions.

Regards,
Ziming Zhang