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: [email protected] Thanks ________________________________ 发件人: 张 子鸣 <[email protected]> 发送时间: 2026年9月10日 21:28 收件人: [email protected] <[email protected]> 主题: [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/[email protected]> Regards, Ziming Zhang
