Hi, Bug report #19572 [1] describes a case where the same qual appears in both a JOIN's ON clause and the WHERE clause, causing the planner to misestimate the row count and pick a worse plan (in the report, it flipped JIT on). IIUC this is working as expected: the planner treats each AND'ed clause as independent and multiplies their selectivities, so an identical duplicate gets its selectivity squared.
I hadn't come across this myself, but the reporter mentioned it can show up with ORM-generated SQL, which seems plausible. So I'm wondering whether it's worth handling it in the planner. I'm attaching a PoC that it drops a base-relation restriction clause when an equal() clause is already present. Note the duplicate is only visible after qual pushdown, the ON-clause copy only becomes a baserestrictinfo once distributed, so the check lives in add_base_clause_to_rel(), alongside the existing "skip always-true qual" logic. It only handles exactly identical quals. It could perhaps be generalized to drop implied quals (if qual1 is true, qual2 is always true). I think that predicate_implied_by() in predtest.c already does this kind of proof, though today IIUC it's only used for partial indexes, partition pruning and constraint exclusion, not the base qual list. I'm not sure the general case is worth the added planning time. Thoughts? [1] https://www.postgresql.org/message-id/flat/19572-f770e89412629023%40postgresql.org -- Matheus Alcantara EDB: https://www.enterprisedb.com
From 76d19f644498dd3c32d26cad87ecc7d71d7eea66 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Thu, 30 Jul 2026 09:46:26 -0300 Subject: [PATCH] Prevent duplicate base restriction clauses from being counted twice The planner estimates the selectivity of a restriction list by treating each clause as independent and multiplying the individual selectivities together. When the same qual appears more than once on a relation, this squares its selectivity and underestimates the number of rows the scan will return. That can happen readily when a predicate is present in both a WHERE clause and an inner join's ON clause: both copies get pushed down onto the same base relation, and the resulting row underestimate can propagate up the join tree and lead to a worse plan being chosen. Since two structurally equal clauses have identical selectivity, a duplicate can be dropped without changing the result. Do so in add_base_clause_to_rel(), where the duplicate first becomes visible after qual pushdown, alongside the existing logic that discards always-true quals. The check is deliberately conservative: a clause is only removed when an equal() clause is already present with a matching security level, pushed-down status and pseudoconstant flag, and neither clause is an outer-join clone variant. --- src/backend/optimizer/plan/initsplan.c | 41 ++++++++++++++++++++++ src/test/regress/expected/create_index.out | 6 ++-- src/test/regress/expected/equivclass.out | 12 +++---- 3 files changed, 50 insertions(+), 9 deletions(-) diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index b38422c47a4..b1cd9e7f8a4 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -3406,6 +3406,40 @@ check_redundant_nullability_qual(PlannerInfo *root, Node *clause) return false; } +/* + * baserestrictinfo_contains_dup + * Return true if 'rel' already has a baserestrictinfo whose clause is + * equal() to restrictinfo. + * + * It only treat clauses as duplicates when their security levels, pushed-down + * status and pseudoconstant flags match, and neither is an outer-join clone + * variant (whose serials/relids need to stay in lockstep). + */ +static bool +baserestrictinfo_contains_dup(RelOptInfo *rel, RestrictInfo *restrictinfo) +{ + ListCell *lc; + + if (restrictinfo->has_clone || restrictinfo->is_clone) + return false; + + foreach(lc, rel->baserestrictinfo) + { + RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc); + + if (rinfo->has_clone || rinfo->is_clone) + continue; + + if (restrictinfo->security_level == rinfo->security_level && + restrictinfo->is_pushed_down == rinfo->is_pushed_down && + restrictinfo->pseudoconstant == rinfo->pseudoconstant && + equal(restrictinfo->clause, rinfo->clause)) + return true; + } + + return false; +} + /* * add_base_clause_to_rel * Add 'restrictinfo' as a baserestrictinfo to the base relation denoted @@ -3444,6 +3478,13 @@ add_base_clause_to_rel(PlannerInfo *root, Index relid, if (restriction_is_always_true(root, restrictinfo)) return; + /* + * Don't add the clause if an equal clause is already present, to + * avoid double-counting its selectivity. + */ + if (baserestrictinfo_contains_dup(rel, restrictinfo)) + return; + /* * Substitute the origin qual with constant-FALSE if it is provably * always false. diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 7b2640f0e04..80c5e8bac9a 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2441,10 +2441,10 @@ SELECT unique1 FROM tenk1 WHERE unique1 = ANY('{NULL,NULL,NULL}'); explain (costs off) SELECT unique1 FROM tenk1 WHERE unique1 IS NULL AND unique1 IS NULL; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +---------------------------------------------- Index Only Scan using tenk1_unique1 on tenk1 - Index Cond: ((unique1 IS NULL) AND (unique1 IS NULL)) + Index Cond: (unique1 IS NULL) (2 rows) SELECT unique1 FROM tenk1 WHERE unique1 IS NULL AND unique1 IS NULL; diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out index ad8ab294ff6..95734cd346c 100644 --- a/src/test/regress/expected/equivclass.out +++ b/src/test/regress/expected/equivclass.out @@ -143,12 +143,12 @@ explain (costs off) explain (costs off) select * from ec1, ec2 where ff = x1 and ff = '42'::int8; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------- Nested Loop Join Filter: (ec1.ff = ec2.x1) -> Index Scan using ec1_pkey on ec1 - Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint)) + Index Cond: (ff = '42'::bigint) -> Seq Scan on ec2 (5 rows) @@ -233,12 +233,12 @@ explain (costs off) union all select ff + 4 as x from ec1) as ss1 where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Nested Loop Join Filter: ((((ec1_1.ff + 2) + 1)) = ec1.f1) -> Index Scan using ec1_pkey on ec1 - Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint)) + Index Cond: (ff = '42'::bigint) Filter: (ff = f1) -> Append -> Index Scan using ec1_expr2 on ec1 ec1_1 -- 2.50.1 (Apple Git-155)
