In the last days I monitored the PostgreSQL logfile on my webserver to find slow queries in the Serendipity blog software. I was hunting another bug in the “events” module, therefore I decided it’s woth the additional work and maybe I can tweak some settings to speed up the response time a bit.
As a starter I changed the log_min_duration_statement to
250, which results in logging every query with a runtime
> 0.25 seconds.
Surprise, surprise: a big performance killer showed up:
There’s no index (beside the primary key
counter_id) defined on the table
This two commands will fix this problem really fast:
EXPLAIN showed that PostgreSQL is using both indexes and now the query runtime is below
250 milliseconds (it was between 1 and 3 seconds before).
Another perfect candidate is the
serendipity_visitors_count table. This query showed up with a long runtime:
Again no index defined on this table by default. After playing a bit with several indexes I found out that creating an additional index on the
day column is most effective:
create index serendipity_visitors_count_day on serendipity_visitors_count(day);
Indexes defined on the
year column are not used by the planner, at least not in my blog database.
That’s all, no more “slow queries” left, at least not in my log. Maybe I will lower
log_min_duration_statement in the next days.