Re: Odd Sort/Limit/Max Problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: josh(at)agliodbs(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Odd Sort/Limit/Max Problem
Date: 2002-12-13 20:24:23
Message-ID: 23242.1039811063@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 think it would work to instead do something like

select date_resolved from case_clients
where date_resolved < 'infinity'
order by date_resolved desc
limit 1;

since then the indexscan will get a qualifier condition that will allow
it to discard the nulls. In fact, I think this will even prevent
having to traverse past the nulls in the index --- the original form
starts the indexscan at the index end, but this should do a btree
descent search to exactly the place you want. Note that the
where-clause has to match the scan direction (> or >= for ASC, < or <=
for DESC) so that it looks like a "start here" condition to btree.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Hannu Krosing 2002-12-13 22:22:08 Re: Odd Sort/Limit/Max Problem
Previous Message Stephan Szabo 2002-12-13 20:10:20 Re: Odd Sort/Limit/Max Problem