quickly getting the top N rows

From: Ben <bench(at)silentmedia(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: quickly getting the top N rows
Date: 2007-10-04 18:00:30
Message-ID: Pine.LNX.4.64.0710041053590.30864@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Lewis 2007-10-04 18:10:24 Re: quickly getting the top N rows
Previous Message Tore Lukashaugen 2007-10-04 17:51:14 Partitioning in postgres - basic question