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 9078B925C61 for ; Thu, 29 May 2003 10:28:37 -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 69385-02 for ; Thu, 29 May 2003 10:28:36 -0400 (EDT) Received: from mail.deltav.hu (unknown [213.163.0.192]) by developer.postgresql.org (Postfix) with ESMTP id 8CF21925C55 for ; Thu, 29 May 2003 10:28:35 -0400 (EDT) Received: from fejleszt4 ([213.163.10.103]) by mail.deltav.hu (Netscape Messaging Server 3.6) with SMTP id AAA12FB for ; Thu, 29 May 2003 16:28:33 +0200 Message-ID: <009f01c325ee$63fbdb00$0403a8c0@fejleszt4> From: =?ISO-8859-1?Q?SZUCS_G=E1bor?= To: References: <006d01c325eb$eb72fdf0$0200a8c0@WORKSTATION> Subject: Re: Query problem fixed Date: Thu, 29 May 2003 16:27:10 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/265 X-Sequence-Number: 2044 The thing I can't really understand why can't the planner find out something like this: 1. Index scan using column6 2. Backward search on subset using column3 Any guru to explain? G. ------------------------------- cut here ------------------------------- ----- Original Message ----- From: "Kevin Schroeder" Sent: Thursday, May 29, 2003 4:09 PM > I figured out how to make the query faster. There should be a mailing list > set up for wasted questions since I always seem to figure out the problem > after I've bugged everyone for help. > > In the query > > 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) > > Since the data was being ordered by column3 it seems to have sped the query > up to 1 ms from 6000ms by making column 3 the first part of the index rather > than the second. > > Kevin