Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 2633A9FB75E for ; Thu, 4 Oct 2007 17:34:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 18842-07 for ; Thu, 4 Oct 2007 17:34:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from newmail.silentmedia.com (earth.silentmedia.com [216.254.19.12]) by postgresql.org (Postfix) with ESMTP id A1F879FB6CB for ; Thu, 4 Oct 2007 17:34:27 -0300 (ADT) Received: by newmail.silentmedia.com (Postfix, from userid 99) id BA86735E3C5; Thu, 4 Oct 2007 13:34:25 -0700 (PDT) Received: from GRD (GRD [10.125.42.6]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by newmail.silentmedia.com (Postfix) with ESMTP id D1DBF35E3BF; Thu, 4 Oct 2007 13:34:23 -0700 (PDT) Date: Thu, 4 Oct 2007 13:34:22 -0700 (PDT) From: Ben X-X-Sender: bench@localhost.localdomain To: Simon Riggs cc: Tom Lane , pgsql-performance@postgresql.org Subject: Re: quickly getting the top N rows In-Reply-To: <1191529385.4223.231.camel@ebony.site> Message-ID: References: <20071004141411.9509fe66.wmoran@collaborativefusion.com> <3785.1191523930@sss.pgh.pa.us> <1191529385.4223.231.camel@ebony.site> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/46 X-Sequence-Number: 27068 On Thu, 4 Oct 2007, Simon Riggs wrote: > In the first query, Postgres cannot use the index because the sort order > of the index does not match the sort order of the query. When you change > the sort order of the query so that it matches that of the index, then > the index is used. > > If you define your index on (logtime, logkey, clientkey, premiseskey) > rather than on (clientkey, premiseskey, logtime, logkey) you will have a > fast query. Yes, the column order matters. I thought that might explain it, but then I'm surprised that it can still use an index when the first two columns of the index aren't in the query. Wouldn't that mean that it might have to walk the entire index to find matching rows? ....unless it's smart enough to realize that the first two columns will match everything. Which would be cool.