Incorrect selectivity estimates for NOT on nullable boolean columns

From: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Incorrect selectivity estimates for NOT on nullable boolean columns
Date: 2026-10-22 09:44:37
Message-ID: ff15e171-97b7-46eb-8fe8-17b76f9ba17d@tantorlabs.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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/

Attachment Content-Type Size
v1-0001-Fix-NOT-selectivity-estimates-for-nullable-boolea.patch text/x-patch 1.8 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Xuneng Zhou 2026-10-22 09:44:54 The issue of seeking backward in XLogReadDetermineTimeline()
Previous Message Chao Li 2026-10-22 09:20:10 Re: DOCS - CREATE PUBLICATION wording re 'ONLY' for partitioned tables.