Date: Thu, 4 Oct 2007 20:22:42 +0200
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: quickly getting the top N rows
Message-ID: <20071004182242.GB26859@KanotixBox>
References: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
User-Agent: mutt-ng 1.5.9i (Linux)

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°