X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 40F3014B281A for ; Thu, 12 Aug 2004 07:37:57 -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 49931-08 for ; Thu, 12 Aug 2004 10:37:53 +0000 (GMT) Received: from thor.netera.se (thor.netera.se [62.13.47.20]) by svr1.postgresql.org (Postfix) with ESMTP id D4A3614B281C for ; Thu, 12 Aug 2004 07:37:51 -0300 (ADT) Received: from [127.0.0.1] (unknown [213.212.22.34]) by thor.netera.se (Postfix) with ESMTP id 8CC6713AC049; Thu, 12 Aug 2004 12:37:51 +0200 (CEST) Message-ID: <411B4878.4090804@relevanttraffic.se> Date: Thu, 12 Aug 2004 12:37:44 +0200 From: Ulrich Wisser Organization: Relevant Traffic AB User-Agent: Mozilla Thunderbird 0.7.2 (Windows/20040707) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Cc: bruno@wolff.to Subject: Re: [GENERAL] How to know which queries are to be optimised? References: <4110CFE7.9000202@relevanttraffic.se> <20040811162701.GA9992@wolff.to> In-Reply-To: <20040811162701.GA9992@wolff.to> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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/184 X-Sequence-Number: 7820 Hi Bruno, >>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. I do a vacuum full analyze every night. How can I see if my FSM setting is appropriate? >>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. But is there a tool that could compile a summary out of the log? The log grows awefully big after a short time. Thanks /Ulrich