Subject: Re: quickly getting the top N rows
From: Mark Lewis <mark.lewis@mir3.com>
To: Ben <bench@silentmedia.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
References: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Thu, 04 Oct 2007 11:10:24 -0700
Message-Id: <1191521424.25943.47.camel@archimedes>
Mime-Version: 1.0

On Thu, 2007-10-04 at 11:00 -0700, Ben wrote:
> 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 hoping the same concept would still apply. Am I doing something wrong, 
> or just expecting something that doesn't exist?

It has to do with the way that NULL values are stored in the index.
This page has details and instructions for how to get it to work:

http://developer.postgresql.org/pgdocs/postgres/indexes-ordering.html

-- Mark Lewis