Re: quickly getting the top N rows - Mailing list pgsql-performance

From Mark Lewis
Subject Re: quickly getting the top N rows
Date
Msg-id 1191521424.25943.47.camel@archimedes
Whole thread Raw
In response to quickly getting the top N rows  (Ben <bench@silentmedia.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Ben
Date:
Subject: quickly getting the top N rows
Next
From: Bill Moran
Date:
Subject: Re: quickly getting the top N rows