Hi ChenHui and Solai,
Thanks to both of you for reviewing and testing the patch. Attached is v3
with the comment and commit message corrected as ChenHui suggested.
Regards,
Ziming Zhang
From cd77ff213853e3e2a3d45d05df769a1199221880 Mon Sep 17 00:00:00 2001
From: Ziming Zhang <[email protected]>
Date: Sat, 19 Sep 2026 13:39:12 +0800
Subject: [PATCH v3] postgres_fdw: Fix cost estimation for semi join pushdown.
In estimate_path_cost_size(), the number of rows on which the quals
pushed down past a join are evaluated was estimated as the cross
product of the two sides' row counts scaled by joinclause_sel. That
is sensible as long as joinclause_sel is a probability per
cross-product row pair, as it is for inner and outer joins. For a
semi join, however, clauselist_selectivity() returns the fraction of
the outer side's rows that have a match in the inner side, i.e. a
probability per outer row, so the remote quals were charged on
outer_rows * inner_rows * P(match)
rows instead of the semi join's actual result size,
outer_rows * P(match)
inflating that cost term by roughly a factor of the inner side's row
count and biasing the planner against pushing semi joins down (and
against plans built on top of a pushed-down semi join, since the
inflated total cost propagates to parent relations).
Derive the join's output size from the outer side's row count for
JOIN_SEMI instead. This mirrors how postgresGetForeignJoinPaths()
already passes JOIN_INNER rather than the actual jointype when
costing local_conds: conditions applied after a semi join are
evaluated on the join's output rows, like quals in a WHERE clause.
Add a regression test using a low-cardinality join key and a few
pushed-down quals on the outer side: at default settings the planner
chooses a local semi join without the fix and the pushed-down Foreign
Scan with it.
Co-Authored-By: Claude Code <[email protected]>
---
.../postgres_fdw/expected/postgres_fdw.out | 39 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 13 ++++++-
contrib/postgres_fdw/sql/postgres_fdw.sql | 17 ++++++++
3 files changed, 68 insertions(+), 1 deletion(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index a6295674daf..6bc10b56d18 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2258,6 +2258,45 @@ SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM
ft2 t2 WHERE t1.c1 = t2
119
(10 rows)
+-- semi join pushdown must also win the cost comparison when there are
+-- enough other pushed-down quals
+CREATE TABLE "S 1".semi_cost_t1 (a int, b int);
+INSERT INTO "S 1".semi_cost_t1 SELECT i, i % 100 FROM generate_series(1, 200)
i;
+CREATE TABLE "S 1".semi_cost_t2 (b int);
+INSERT INTO "S 1".semi_cost_t2 SELECT i FROM generate_series(0, 99) i;
+CREATE FOREIGN TABLE ft_semi_cost_t1 (a int, b int) SERVER loopback OPTIONS
(schema_name 'S 1', table_name 'semi_cost_t1');
+CREATE FOREIGN TABLE ft_semi_cost_t2 (b int) SERVER loopback OPTIONS
(schema_name 'S 1', table_name 'semi_cost_t2');
+ANALYZE ft_semi_cost_t1;
+ANALYZE ft_semi_cost_t2;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a FROM ft_semi_cost_t1 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 ft_semi_cost_t2 t2
WHERE t2.b = t1.b) ORDER BY t1.a OFFSET 190 LIMIT 10;
+
QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: t1.a
+ Relations: (public.ft_semi_cost_t1 t1) SEMI JOIN (public.ft_semi_cost_t2 t2)
+ Remote SQL: SELECT r1.a FROM "S 1".semi_cost_t1 r1 WHERE ((r1.a >= 0)) AND
((r1.a >= (-1))) AND ((r1.a >= (-2))) AND ((r1.a >= (-3))) AND ((r1.a >= (-4)))
AND EXISTS (SELECT NULL FROM "S 1".semi_cost_t2 r2 WHERE ((r1.b = r2.b))) ORDER
BY r1.a ASC NULLS LAST LIMIT 10::bigint OFFSET 190::bigint
+(4 rows)
+
+SELECT t1.a FROM ft_semi_cost_t1 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 ft_semi_cost_t2 t2
WHERE t2.b = t1.b) ORDER BY t1.a OFFSET 190 LIMIT 10;
+ a
+-----
+ 191
+ 192
+ 193
+ 194
+ 195
+ 196
+ 197
+ 198
+ 199
+ 200
+(10 rows)
+
+DROP FOREIGN TABLE ft_semi_cost_t1;
+DROP FOREIGN TABLE ft_semi_cost_t2;
+DROP TABLE "S 1".semi_cost_t1;
+DROP TABLE "S 1".semi_cost_t2;
-- CROSS JOIN can be pushed down
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET
100 LIMIT 10;
diff --git a/contrib/postgres_fdw/postgres_fdw.c
b/contrib/postgres_fdw/postgres_fdw.c
index c731ee199e2..47abdcaaad7 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3640,7 +3640,18 @@ estimate_path_cost_size(PlannerInfo *root,
run_cost = fpinfo_i->rel_total_cost -
fpinfo_i->rel_startup_cost;
run_cost += fpinfo_o->rel_total_cost -
fpinfo_o->rel_startup_cost;
run_cost += nrows * join_cost.per_tuple;
- nrows = clamp_row_est(nrows * fpinfo->joinclause_sel);
+ /*
+ * For JOIN_SEMI, joinclause_sel is the fraction of the
outer
+ * side's rows that have matches in the inner side (see
+ * postgresGetForeignJoinPaths, where joinclause_sel is
+ * computed), not a probability per cross-product row,
so
+ * derive the join's output size from the outer row
count.
+ */
+ if (fpinfo->jointype == JOIN_SEMI)
+ nrows = fpinfo_o->rows * fpinfo->joinclause_sel;
+ else
+ nrows *= fpinfo->joinclause_sel;
+ nrows = clamp_row_est(nrows);
run_cost += nrows * remote_conds_cost.per_tuple;
run_cost += fpinfo->local_conds_cost.per_tuple *
retrieved_rows;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql
b/contrib/postgres_fdw/sql/postgres_fdw.sql
index eaeb90485e8..3ec0c5e9b03 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -711,6 +711,23 @@ SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2
t2 WHERE t1.c1 = t2.c1)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 =
t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 =
t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
+-- semi join pushdown must also win the cost comparison when there are
+-- enough other pushed-down quals
+CREATE TABLE "S 1".semi_cost_t1 (a int, b int);
+INSERT INTO "S 1".semi_cost_t1 SELECT i, i % 100 FROM generate_series(1, 200)
i;
+CREATE TABLE "S 1".semi_cost_t2 (b int);
+INSERT INTO "S 1".semi_cost_t2 SELECT i FROM generate_series(0, 99) i;
+CREATE FOREIGN TABLE ft_semi_cost_t1 (a int, b int) SERVER loopback OPTIONS
(schema_name 'S 1', table_name 'semi_cost_t1');
+CREATE FOREIGN TABLE ft_semi_cost_t2 (b int) SERVER loopback OPTIONS
(schema_name 'S 1', table_name 'semi_cost_t2');
+ANALYZE ft_semi_cost_t1;
+ANALYZE ft_semi_cost_t2;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a FROM ft_semi_cost_t1 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 ft_semi_cost_t2 t2
WHERE t2.b = t1.b) ORDER BY t1.a OFFSET 190 LIMIT 10;
+SELECT t1.a FROM ft_semi_cost_t1 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 ft_semi_cost_t2 t2
WHERE t2.b = t1.b) ORDER BY t1.a OFFSET 190 LIMIT 10;
+DROP FOREIGN TABLE ft_semi_cost_t1;
+DROP FOREIGN TABLE ft_semi_cost_t2;
+DROP TABLE "S 1".semi_cost_t1;
+DROP TABLE "S 1".semi_cost_t2;
-- CROSS JOIN can be pushed down
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET
100 LIMIT 10;
--
2.34.1
