Re: quickly getting the top N rows

Ben <bench@silentmedia.com> schrieb:

> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2 is doing a full table scan on foo, then sorting
> it, then doing the limit. I have a more complex primary key, but I was

Please show us the output from

EXPLAIN ANALYSE select bar from foo order by bar desc limit 20;

I try it, with 8.1, and i can see an index scan. You have, maybe, wrong
statistics ot not enough (to few) rows in your table.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°