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://www.postgresql.org/message-id/flat/[email protected] Regards, Ziming Zhang
v1-0001-postgres_fdw-Fix-local-costing-of-remote-quals-af.patch
Description: v1-0001-postgres_fdw-Fix-local-costing-of-remote-quals-af.patch
