From: Ben <bench@silentmedia.com>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: quickly getting the top N rows
Date: Thu, 4 Oct 2007 11:33:39 -0700 (PDT)
Message-ID: <Pine.LNX.4.64.0710041126010.30864@localhost.localdomain> (raw)
In-Reply-To: <20071004141411.9509fe66.wmoran@collaborativefusion.com>
References: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
<20071004141411.9509fe66.wmoran@collaborativefusion.com>
On Thu, 4 Oct 2007, Bill Moran wrote:
> However, 2 guesses:
> 1) You never analyzed the table, thus PG has awful statistics and
> doesn't know how to pick a good plan.
> 2) You have so few rows in the table that a seq scan is actually
> faster than an index scan, which is why PG uses it instead.
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.
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: bench@silentmedia.com, wmoran@collaborativefusion.com
Subject: Re: quickly getting the top N rows
In-Reply-To: <Pine.LNX.4.64.0710041126010.30864@localhost.localdomain>
* 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