Re: Odd Sort/Limit/Max Problem

Tom Lane kirjutas L, 14.12.2002 kell 01:24:
> Josh Berkus <josh@agliodbs.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@tm.ee>