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

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.