From: Mark Lewis <mark.lewis@mir3.com>
To: Ben <bench@silentmedia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: quickly getting the top N rows
Date: Thu, 04 Oct 2007 11:10:24 -0700
Message-ID: <1191521424.25943.47.camel@archimedes> (raw)
In-Reply-To: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
References: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.lewis@mir3.com, bench@silentmedia.com
Subject: Re: quickly getting the top N rows
In-Reply-To: <1191521424.25943.47.camel@archimedes>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox