Re: quickly getting the top N rows

From: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
To: Ben <bench(at)silentmedia(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: quickly getting the top N rows
Date: 2007-10-04 18:10:24
Message-ID: 1191521424.25943.47.camel@archimedes
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Bill Moran 2007-10-04 18:14:11 Re: quickly getting the top N rows
Previous Message Ben 2007-10-04 18:00:30 quickly getting the top N rows