| 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 |
| 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. |