Incorrect selectivity estimates for NOT on nullable boolean columns
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/
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, failed
Spec compliant: not tested
Documentation: not tested
Hello,
This patch looks good to me.
Maybe you could add some regression-test coverage ?
I don't think that SQL compliance must be checked or that documentation should be updated.
Regards,
PF.
The new status of this patch is: Ready for Committer
Hi,
I have applied the proposed patch and tested the issue related to incorrect
selectivity estimates for NOT on nullable Boolean columns.
I first reproduced the issue before applying the patch. The test table
contained 10,000 rows (7,000 NULL, 2,000 TRUE, and 1,000 FALSE values).
Before applying the patch:
-
Estimated rows: 8,000
-
Actual rows: 1,000
After applying the patch, I rebuilt and tested PostgreSQL using the same
data and query.
After applying the patch:
-
Estimated rows: 1,000
-
Actual rows: 1,000
-
Rows removed by filter: 9,000
-
Execution time: 0.803 ms
I also tested cases with no NULL values, only NULL values, and without
ANALYZE statistics.
All regression tests passed, and git diff --check reported no issues.
The estimated row count now matches the actual row count in the main test
case.
Regards
solai