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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match