Re: BUG #8571: Planner miss-estimates '<null_column> is not true' as not matching any rows

tgarnett(at)panjiva(dot)com writes:
> The following bug has been logged on the website:
> Bug reference: 8571
> Logged by: Timothy Garnett
> Email address: tgarnett(at)panjiva(dot)com
> PostgreSQL version: 9.2.4
> Operating system: Ubuntu Linux x86_64 12.04.3 LTS
> Description:

> The planner seems to be badly miss-estimating the selectivity of '<always
> null col> IS NOT true/false', it does not seem to do so for equivalent
> expressions such as '<col> is null or not <col>' or expressions where the
> selectivity is difficult to determine.

This example works as desired for me in 9.2.5. I think the fix was this:

Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Branch: master [b32a25c3d] 2013-07-24 00:44:09 -0400
Branch: REL9_3_STABLE Release: REL9_3_0 [808d1f812] 2013-07-24 00:44:22 -0400
Branch: REL9_2_STABLE Release: REL9_2_5 [9f8254c18] 2013-07-24 00:44:36 -0400
Branch: REL9_1_STABLE Release: REL9_1_10 [13f11c8a8] 2013-07-24 00:44:46 -0400
Branch: REL9_0_STABLE Release: REL9_0_14 [8e992b018] 2013-07-24 00:44:59 -0400
Branch: REL8_4_STABLE Release: REL8_4_18 [0766904ad] 2013-07-24 00:45:15 -0400

Fix booltestsel() for case where we have NULL stats but not MCV stats.

In a boolean column that contains mostly nulls, ANALYZE might not find
enough non-null values to populate the most-common-values stats,
but it would still create a pg_statistic entry with stanullfrac set.
The logic in booltestsel() for this situation did the wrong thing for
"col IS NOT TRUE" and "col IS NOT FALSE" tests, forgetting that null
values would satisfy these tests (so that the true selectivity would
be close to one, not close to zero). Per bug #8274.

Fix by Andrew Gierth, some comment-smithing by me.

regards, tom lane