Thanks for the update, glad to here it's fixed in the latest version (we're
planning on upgrading soon).
Tim
On Thu, Oct 31, 2013 at 11:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> tgarnett@panjiva.com writes:
> > The following bug has been logged on the website:
> > Bug reference: 8571
> > Logged by: Timothy Garnett
> > Email address: tgarnett@panjiva.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@sss.pgh.pa.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
>