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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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>
From | Date | Subject | |
---|---|---|---|
Next Message | Laurette Cisneros | 2002-12-14 00:00:45 | Re: Fwd: Re: [PERFORM] Odd Sort/Limit/Max Problem |
Previous Message | Tom Lane | 2002-12-13 20:24:23 | Re: Odd Sort/Limit/Max Problem |