X-Original-To: pgsql-performance@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 829D0925CA3 for ; Thu, 29 May 2003 10:49:53 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 74886-06 for ; Thu, 29 May 2003 10:49:53 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by developer.postgresql.org (Postfix) with ESMTP id BFF89925CA2 for ; Thu, 29 May 2003 10:49:52 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h4TEnhU6017681; Thu, 29 May 2003 10:49:43 -0400 (EDT) To: "Kevin Schroeder" Cc: pgsql-performance@postgresql.org Subject: Re: Query problem fixed In-reply-to: <006d01c325eb$eb72fdf0$0200a8c0@WORKSTATION> References: <006d01c325eb$eb72fdf0$0200a8c0@WORKSTATION> Comments: In-reply-to "Kevin Schroeder" message dated "Thu, 29 May 2003 09:09:23 -0500" Date: Thu, 29 May 2003 10:49:43 -0400 Message-ID: <17680.1054219783@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/266 X-Sequence-Number: 2045 "Kevin Schroeder" writes: > select row_key, column1, column2, column3, column4, column5 from table1 > where column6 = 1 order by column3 desc limit 21; > I changed the index to > message_index btree (column3, column6) > rather than > message_index btree (column6, column3, column7) That's probably not the best solution. It would be better to leave the index with column6 first and write the query as ... where column6 = 1 order by column6 desc, column3 desc limit 21 This doesn't change the results (since there's only one value of column6 in the output), but it does cause the planner to realize that a backwards scan of the index would produce what you want with no sort step. The results should be essentially instantaneous if you can get the query plan down to Index Scan Backward + Limit. regards, tom lane