Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by postgresql.org (Postfix) with ESMTP id 9EAD8475897 for ; Thu, 18 Apr 2002 13:43:00 -0400 (EDT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1) id 16yFvv-000IBI-0X; Thu, 18 Apr 2002 18:43:00 +0100 Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id F41A013B9E; Thu, 18 Apr 2002 18:38:35 +0100 (BST) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: Dav Coleman Subject: Re: SQL Query Optimization Date: Thu, 18 Apr 2002 18:38:36 +0100 X-Mailer: KMail [version 1.4] Cc: pgsql-sql@postgresql.org References: <20020417223051.C24932@danger-island.com> <20020418093527.I24932@danger-island.com> In-Reply-To: <20020418093527.I24932@danger-island.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200204181838.36240.richardh@archonet.com> X-Archive-Number: 200204/246 X-Sequence-Number: 7510 On Thursday 18 April 2002 17:35, Dav Coleman wrote: > I should be more clear, the problem is that the application user can > basically construct the SQL query dynamically > But I can see where I was heading in the wrong direction already. I was > thinking that what I needed was to find theories/algorithms on how to > rewrite the SQL before submitting it to postgresql, and I maybe still > need to do that Sort clauses alphabetically (or whatever makes sense to you) so you always get SELECT * FROM a,b WHERE c AND d rather than "b,a" or "d AND c". That way at least you're not getting variations. > but I guess I also need to EXPLAIN and analyze the Record the queries and times either in PG's log or in the application. > bad vs good forms of the queries so I'll know what makes a 'good' vs > 'bad' query (so I'll get a sense on how to rewrite queries). Perhaps > with that understanding, an algorithm for rewriting the queries will > be apparent. > > I just figured I couldn't be the first person to run into this problem, > but I can't find it mentioned anywhere. After the basics (index on fields involved in joins etc) it all gets a bit specific to the size of the tables/indexes involved and the quirks of the parser. If you logged the query-plan and cost estimates for each query processed it shouldn't be too difficult to automatically add indexes where required and see if it makes any difference. That assumes you have good clean patterns of usage in your queries. We're getting a bit AI there mind. - Richard Huxton