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
Is it that postgres thinks that tuple of several nulls is the same as
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
pgsql-performance by date
|Next:||From: Laurette Cisneros||Date: 2002-12-14 00:00:45|
|Subject: Re: Fwd: Re: [PERFORM] Odd Sort/Limit/Max Problem|
|Previous:||From: Tom Lane||Date: 2002-12-13 20:24:23|
|Subject: Re: Odd Sort/Limit/Max Problem |