Skip site navigation (1) Skip section navigation (2)

Re: How to know which queries are to be optimised?

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Ulrich Wisser <ulrich(dot)wisser(at)relevanttraffic(dot)se>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: How to know which queries are to be optimised?
Date: 2004-08-11 16:27:01
Message-ID: 20040811162701.GA9992@wolff.to (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
On Wed, Aug 04, 2004 at 14:00:39 +0200,
  Ulrich Wisser <ulrich(dot)wisser(at)relevanttraffic(dot)se> 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.

In response to

Responses

pgsql-performance by date

Next:From: Jesper KroghDate: 2004-08-11 16:29:33
Subject: Re: Storing binary data.
Previous:From: Tom LaneDate: 2004-08-11 16:06:38
Subject: Re: Storing binary data.

pgsql-general by date

Next:From: Tom LaneDate: 2004-08-11 16:28:51
Subject: Re: psql: immediately exit after an error?
Previous:From: Tom LaneDate: 2004-08-11 16:02:07
Subject: Re: Replication options?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group