Skip site navigation (1) Skip section navigation (2)

Re: Slow execution time when querying view with WHERE clause

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow execution time when querying view with WHERE clause
Date: 2004-11-24 06:31:23
Message-ID: 4738.1101277883@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Mike Mascari <mascarm(at)mascari(dot)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

In response to

Responses

pgsql-performance by date

Next:From: BBI Edwin PunzalanDate: 2004-11-24 06:52:07
Subject: FW: Index usage
Previous:From: Tom LaneDate: 2004-11-24 06:11:48
Subject: Re: Slow execution time when querying view with WHERE clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group