From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: quickly getting the top N rows
Date: Thu, 4 Oct 2007 20:22:42 +0200
Message-ID: <20071004182242.GB26859@KanotixBox> (raw)
In-Reply-To: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
References: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
Ben <bench@silentmedia.com> schrieb:
> 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
Please show us the output from
EXPLAIN ANALYSE select bar from foo order by bar desc limit 20;
I try it, with 8.1, and i can see an index scan. You have, maybe, wrong
statistics ot not enough (to few) rows in your table.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
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: akretschmer@spamfence.net
Subject: Re: quickly getting the top N rows
In-Reply-To: <20071004182242.GB26859@KanotixBox>
* 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