pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mike Mascari <mascarm@mascari.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow execution time when querying view with WHERE clause
Date: Wed, 24 Nov 2004 01:31:23 -0500
Message-ID: <4738.1101277883@sss.pgh.pa.us> (raw)
In-Reply-To: <41A42409.7080704@mascari.com>
References: <41A25C3B.3040608@mascari.com>
	<28237.1101232445@sss.pgh.pa.us>
	<41A42409.7080704@mascari.com>

Mike Mascari <mascarm@mascari.com> writes:
> Tom Lane wrote:
>> Why is it so completely off about the selectivity of the IS NULL clause?

> I think this is a bug in ANALYZE not constructing statistics for columns 
> whose data is entirely NULL:

Um ... doh ... analyze.c about line 1550:

    /* We can only compute valid stats if we found some non-null values. */
    if (nonnull_cnt > 0)
       ...

There's a bit of an epistemological issue here: if we didn't actually
find any nonnull values in our sample, is it legitimate to assume that
the column is entirely null?  On the other hand, if we find only "3" in
our sample we will happily assume the column contains only "3", so I
dunno why we are discriminating against null.  This seems like a case
that just hasn't come up before.

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <4738.1101277883@sss.pgh.pa.us>
Permalink:  ../4738.1101277883@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4738.1101277883@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, mascarm@mascari.com
  Subject: Re: Slow execution time when querying view with WHERE clause
  In-Reply-To: <4738.1101277883@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox