Hi everyone,
I noticed that clause_selectivity_ext() estimates `NOT A` as `1 -
selectivity(A)`, without accounting for NULL values.
For example:
```
CREATE TABLE t (b BOOLEAN);
--- 70% - NULLs, 20% - true, 10% - false
INSERT INTO t
SELECT CASE
WHEN i <= 7000 THEN NULL::boolean
WHEN i <= 9000 THEN TRUE
ELSE FALSE
END
FROM generate_series(1, 10000) AS g(i);
ANALYZE t;
EXPLAIN ANALYZE SELECT * FROM t WHERE NOT b;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..138.00 *rows=8000* width=5) (actual
time=2.278..2.379 *rows=1000.00* loops=1)
Filter: (NOT b)
Rows Removed by Filter: 9000
Buffers: shared read=38
Planning:
Buffers: shared hit=15 read=12 dirtied=1
Planning Time: 0.651 ms
Execution Time: 2.511 ms
(8 rows)
```
Query returns 1000 rows. However, the the estimated rows 8000.
I propose checking whether statistics are available for the argumnet of
NOT. If so, we can use `booltestsel(IS_FALSE)`, which accounts for the
null fraction. Otherwise, we retain the existing estimate.
The plan after fix:
```
EXPLAIN ANALYZE SELECT * FROM t WHERE NOT b;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..138.00 *rows=1000* width=5) (actual
time=0.753..0.830 *rows=1000.00* loops=1)
Filter: (NOT b)
Rows Removed by Filter: 9000
Buffers: shared hit=75 dirtied=1 written=1
Planning:
Buffers: shared hit=21
Planning Time: 0.109 ms
Execution Time: 0.861 ms
(8 rows)
```
Does this approach seem reasonable?
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
From 44527135526087bca6ebed80d010d3334a4ab5ff Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email protected]>
Date: Thu, 22 Oct 2026 12:31:03 +0300
Subject: [PATCH v1] Fix NOT selectivity estimates for nullable boolean
expressions
Use booltestsel(IS_FALSE) when statistics are available for the argument
of NOT, so NULL values are excluded from the estimated matching rows.
Retain the existing recursive estimate when statistics are unavailable.
---
src/backend/optimizer/path/clausesel.c | 26 +++++++++++++++++++-------
1 file changed, 19 insertions(+), 7 deletions(-)
diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c
index 25c4d177ad9..631752dbced 100644
--- a/src/backend/optimizer/path/clausesel.c
+++ b/src/backend/optimizer/path/clausesel.c
@@ -797,13 +797,25 @@ clause_selectivity_ext(PlannerInfo *root,
}
else if (is_notclause(clause))
{
- /* inverse of the selectivity of the underlying clause */
- s1 = 1.0 - clause_selectivity_ext(root,
- (Node *) get_notclausearg((Expr *) clause),
- varRelid,
- jointype,
- sjinfo,
- use_extended_stats);
+ Node *arg = (Node *) get_notclausearg((Expr *) clause);
+ VariableStatData vardata;
+
+ examine_variable(root, arg, varRelid, &vardata);
+ if (HeapTupleIsValid(vardata.statsTuple))
+ {
+ /* Use statistics to select FALSE values, excluding NULLs. */
+ s1 = booltestsel(root, IS_FALSE, arg, varRelid, jointype, sjinfo);
+ }
+ else
+ {
+ /* Otherwise, estimate the underlying clause */
+ s1 = 1.0 - clause_selectivity_ext(root, arg,
+ varRelid,
+ jointype,
+ sjinfo,
+ use_extended_stats);
+ }
+ ReleaseVariableStats(vardata);
}
else if (is_andclause(clause))
{
--
2.34.1
