Re: Odd Sort/Limit/Max Problem

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