回复: [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(dot)zhang(at)outlook(dot)com

Thanks
________________________________
发件人: 张 子鸣 <toren(dot)zhang(at)outlook(dot)com>
发送时间: 2026年9月10日 21:28
收件人: pgsql-hackers(at)lists(dot)postgresql(dot)org <pgsql-hackers(at)lists(dot)postgresql(dot)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].

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&data=05%7C02%7C%7C9a1904d440044098d93408df0fbe56b5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639246982314278104%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=BX%2BSR2N%2FJGboI%2FqhLve9TDz4G2VlfFfTccdqvwCcvrQ%3D&reserved=0<https://www.postgresql.org/message-id/flat/c9e2a757cf3ac2333714eaf83a9cc184(at)postgrespro(dot)ru>

Regards,
Ziming Zhang