To: Ben <bench@silentmedia.com>
cc: Bill Moran <wmoran@collaborativefusion.com>,
	pgsql-performance@postgresql.org
Subject: Re: quickly getting the top N rows 
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>
Comments: In-reply-to Ben <bench@silentmedia.com>
	message dated "Thu, 04 Oct 2007 11:33:39 -0700"
Date: Thu, 04 Oct 2007 14:52:10 -0400
Message-ID: <3785.1191523930@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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