Date: Thu, 4 Oct 2007 11:33:39 -0700 (PDT)
From: Ben <bench@silentmedia.com>
To: Bill Moran <wmoran@collaborativefusion.com>
cc: pgsql-performance@postgresql.org
Subject: Re: quickly getting the top N rows
In-Reply-To: <20071004141411.9509fe66.wmoran@collaborativefusion.com>
Message-ID: <Pine.LNX.4.64.0710041126010.30864@localhost.localdomain>
References: <Pine.LNX.4.64.0710041053590.30864@localhost.localdomain>
	<20071004141411.9509fe66.wmoran@collaborativefusion.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

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.