DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=NMvbdxS7wtqY8Ti0blheMA3muYm/Kl222cAEIexqGy23MuVqDfqsqQGJ0ULDkIZA5WtwCmyFejN1yqf8ZImbcGubRSYpi02hwO2nxKoqDTe7uWO+jNUZpcTTIGW8GmFbLFjDei/sr/OqDo3X5dnwB/Q24hNhuEMjDLQFL0ktQBI=
Message-ID: <dcc563d10710041332s7050c643x44f31908460732f1@mail.gmail.com>
Date: Thu, 4 Oct 2007 15:32:55 -0500
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: Ben <bench@silentmedia.com>
Subject: Re: quickly getting the top N rows
Cc: pgsql-performance@postgresql.org
In-Reply-To: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>

On 10/4/07, Ben <bench@silentmedia.com> 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?

pg uses an intelligent planner.  It looks at the table, the number of
rows, the distribution of values, and makes a decision whether to use
seq scan or index.  Do you have any evidence that in your case seq
scan is a bad choice?

try this experiment:

psql mydb
=# select * from foo; -- this will prime the db and put the table in
memory if it will fit
=# \timing
=# set enable_seqscan=off;
=# select bar from foo order by bar desc limit 20;
=# set enable_seqscan=on;
=# select bar from foo order by bar desc limit 20;

and compare the times each takes.  run each way a few times to be sure
you're not getting random variance.

On my reporting db with somewhere around 75 million tables, a similar
query 0.894 mS and uses an index scan.  Which is good, because a
sequential scan on that table takes about 15 to 30 minutes.