Incorrect selectivity estimates for NOT on nullable boolean columns

Started by Ilia Evdokimov13 days ago3 messageshackers
Jump to latest
#1Ilia Evdokimov
ilya.evdokimov@tantorlabs.com

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/

Attachments:

t254328_1
v1-0001-Fix-NOT-selectivity-estimates-for-nullable-boolea.patchtext/x-patch; charset=UTF-8; name=v1-0001-Fix-NOT-selectivity-estimates-for-nullable-boolea.patchDownload+19-8
#2Pierre Forstmann
pierre.forstmann@gmail.com
In reply to: Ilia Evdokimov (#1)
Re: Incorrect selectivity estimates for NOT on nullable boolean columns

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

#3solai v
solai.cdac@gmail.com
In reply to: Ilia Evdokimov (#1)
Re: Incorrect selectivity estimates for NOT on nullable boolean columns

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