Skip to content

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 so 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:

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.


No Trackbacks


Display comments as Linear | Threaded

depesz on :

Hmm, is there any reason that you decided to add 2 indexes for the first case, instead of one index on 2 fields? It should be faster - at least - I think it should :)
Comments ()

Andreas 'ads' Scherbaum on :

No special reason, i'm just lazy ;-) I played a bit around with several indexes and that's what worked well. One index over both fields should work too.
Comments ()

Mors on :

In my opinion it's still to slow. Try to play with the 'SET statistics' command. If properly used, it can speedup query several times.
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options