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

Re: Odd Sort/Limit/Max Problem

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: josh(at)agliodbs(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: Odd Sort/Limit/Max Problem
Date: 2002-12-13 22:22:08
Message-ID: 1039818128.2391.7.camel@rh72.home.ee (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane kirjutas L, 14.12.2002 kell 01:24:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > Now, none of those times is huge on this test database, but on a larger 
> > database (> 1million rows) the performance problem is much worse.  For some 
> > reason, the backward index scan seems to have to transverse all of the NULLs 
> > before selecting a value.
> 
> Correct.  You lose, if there are a lot of nulls.  Unfortunately, the
> "IS NOT NULL" clause isn't considered an indexable operator and so the
> indexscan has no idea that it shouldn't return the null rows. If it
> could just traverse past them in the index, this example wouldn't be so
> bad, but it goes out and fetches the heap rows before discarding 'em :-(
> 
> > I find this peculiar, as I was under the 
> > impression that NULLs were not indexed.
> 
> Not correct.  btrees index NULLs, as they must do in order to have
> correct behavior for multicolumn indexes.

I've heard this befoe, but this is something I've never understood - why
do you have to index _single_ null's in order to behave correctly for
multi-column index. 

Is it that postgres thinks that tuple of several nulls is the same as
null ? 

Is it just that nulls need to have an ordering and that this fact has
somehow leaked down to actually being stored in the index ?

I don't have anything against nulls being indexed - in a table where
nulls have about the same frequency as other values it may actually be
useful (if indexes were used to find IS NULL tuples)


-- 
Hannu Krosing <hannu(at)tm(dot)ee>

In response to

Responses

pgsql-performance by date

Next:From: Laurette CisnerosDate: 2002-12-14 00:00:45
Subject: Re: Fwd: Re: [PERFORM] Odd Sort/Limit/Max Problem
Previous:From: Tom LaneDate: 2002-12-13 20:24:23
Subject: Re: Odd Sort/Limit/Max Problem

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