Re: Allowing x IS NOT NULL as a btree search condition

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Allowing x IS NOT NULL as a btree search condition
Date: 2009-12-30 23:26:10
Message-ID: 200912302326.nBUNQAN27602@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tom Lane wrote:
> Back when we put in the ability to use "x IS NULL" as a btree search
> condition, we intentionally left out "x IS NOT NULL", on the grounds
> that it is comparable to "x <> something" which is not btree-searchable
> either. However, it occurs to me that we missed a bet here. The NOT
> NULL condition could instead be treated like "x is less than NULL"
> (in a NULLS LAST index) or "x is greater than NULL" (in a NULLS FIRST
> index), which would make it work like a searchable inequality. It's
> still true that except in the case of a mostly-null column, it would
> seldom be worth doing such an indexscan. However, I can see an
> application where an index search condition like this would be highly
> worthwhile: namely, trying to extract a column min or max. Right now,
> if there are a fair number of nulls at the end of the index you're
> interested in, you have to stupidly scan through them --- but if the
> btree code knew about doing this, it could descend the tree
> intelligently and land right on the first or last non-null.
>
> We have already seen field complaints about the performance of
> index-optimized MAX in cases with many nulls, eg
> http://archives.postgresql.org/pgsql-performance/2006-12/msg00099.php
> which fixing this would take care of. This would also affect the
> usefulness of the idea I proposed earlier today about automatically
> updating the histogram bin boundaries when trying to estimate inequality
> selectivity for comparison values near the ends of the range --- if we
> can't rely on the index lookup for max or min to be cheap, doing that
> stops looking quite so attractive.
>
> While I haven't tried to code this yet, I'm guessing that it's just a
> very small addition to the logic we already have. Any objections to
> fixing this up?

Sounds good to me.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Magnus Hagander 2009-12-30 23:30:36 Re: [PATCH] Windows x64 [repost]
Previous Message Bruce Momjian 2009-12-30 23:25:08 Re: A third lock method