Speed up your Serendipity blog running on PostgreSQL

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:

1

SELECT COUNT(ip) AS result FROM serendipity_visitors WHERE ip ='<ip>' and day='2008-11-26';

There’s no index (beside the primary key counter_id) defined on the table serendipity_visitors.

This two commands will fix this problem really fast:

1
2

create index serendipity_visitors_ip on serendipity_visitors(ip);
create index serendipity_visitors_day on serendipity_visitors(day);

After running ANALYZE serendipity_visitors, 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:

1

UPDATE serendipity_visitors_count SET hits = hits+1 WHERE year='2008' AND month='11' AND day='28';

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 month or 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.