Speed up your Serendipity blog running on PostgreSQL

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:

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:

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:

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 were 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.