From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ben <bench@silentmedia.com>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: quickly getting the top N rows
Date: Thu, 04 Oct 2007 14:52:10 -0400
Message-ID: <3785.1191523930@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.64.0710041126010.30864@localhost.localdomain>
References: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
<20071004141411.9509fe66.wmoran@collaborativefusion.com>
<Pine.LNX.4.64.0710041126010.30864@localhost.localdomain>
Ben <bench@silentmedia.com> writes:
> No, the tables are recently analyzed and there are a couple hundred
> thousand rows in there. But I think I just figured it out.... it's a
> 3-column index, and two columns of that index are the same for every row.
> When I drop those two columns from the ordering restriction, the index
> gets used and things speed up 5 orders of magnitude.
> Maybe the planner is smart enough to think that if a column in the order
> by clause is identical for most rows, then using an index won't help....
> but not smart enough to realize that if said column is at the *end* of the
> order by arguments, after columns which do sort quite well, then it should
> use an index after all.
You're being about as clear as mud here, except that you obviously lied
about what you were doing in your first message. If you have a planner
problem, show us the *exact* query, the *exact* table definition, and
unfaked EXPLAIN ANALYZE output.
regards, tom lane
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: tgl@sss.pgh.pa.us, bench@silentmedia.com, wmoran@collaborativefusion.com
Subject: Re: quickly getting the top N rows
In-Reply-To: <3785.1191523930@sss.pgh.pa.us>
* 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