X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C84AB14B281F for ; Wed, 11 Aug 2004 13:18:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15626-01 for ; Wed, 11 Aug 2004 16:18:56 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id DEC6114B27FF for ; Wed, 11 Aug 2004 13:18:54 -0300 (ADT) Received: (qmail 10232 invoked by uid 500); 11 Aug 2004 16:27:01 -0000 Date: Wed, 11 Aug 2004 11:27:01 -0500 From: Bruno Wolff III To: Ulrich Wisser Cc: pgsql-general@postgresql.org, pgsql-performance@postgresql.org Subject: Re: How to know which queries are to be optimised? Message-ID: <20040811162701.GA9992@wolff.to> Mail-Followup-To: pgsql-performance@postgresql.org, ulrich.wisser@relevanttraffic.se References: <4110CFE7.9000202@relevanttraffic.se> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4110CFE7.9000202@relevanttraffic.se> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/459 X-Sequence-Number: 64014 On Wed, Aug 04, 2004 at 14:00:39 +0200, Ulrich Wisser wrote: This topic really belongs on the performance list. I have copied that list and set followups to go there and copy you. > > my web application grows slower and slower over time. After some > profiling I came to the conclusion that my SQL queries are the biggest > time spenders (25 seconds). Obviously I need to optimise my queries and > maybe introduce some new indexes. This sounds like you aren't doing proper maintainance. You need to be vacuuming with a large enough FSM setting. > The problem is, that my application uses dynamic queries. I therefor can > not determine what are the most common queries. > > I have used the postgresql logging ption before. Is there a tool to > analyze the logfile for the most common and/or most time consuming queries? You can log queries that run for at least a specified amount of time. This will be useful in finding what the long running queries are. You can then use explain analyse to see why they are long running.