Take the 2-minute tour ×

I have some very simple queries that occasionally run very slowly.

The table viewed_sites has about 10 - 20 rows.

Running EXPLAIN ANALYZE always gives a runtime of less than 3 milliseconds.

When the query is run automatically (every 10 seconds) it occasionally takes over a second to run.

The query:

INSERT INTO ga.viewed_sites (site_id) VALUES ('gop2')

The table:

CREATE TABLE viewed_sites (
    site_id character varying(4) NOT NULL,
    last_viewed timestamp with time zone DEFAULT now() NOT NULL

The (occasional) log result:

2010-05-24 15:47:55 UTC LOG: duration: 1044.632 ms statement: INSERT INTO ga.viewed_sites (site_id) VALUES ('gop2')

It's a horribly vague question, but what could be causing this? I suppose it comes down to CPU, RAM, HDD or some combination of the above.

Postgresql 8.3, Ubuntu 8.04



share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Or, the table could be locked. Which seems all things given, more likely. Postgresql is very conservative about locks, but it can happen. A one second wait for HD write, or CPU seems extremely unlikely. And, it doesn't take much ram to do an insert. Check the table pg_locks (and pg_stat_activity to see what their doing), and the function pg_lock_status(), or some of the other tables and functions pertaining to lock status.

share|improve this answer
Is there a way to log table locks? The chances of the lock being active when I look at those tables is slim. –  Johan May 26 '10 at 10:59
add comment

Does the table have a foreign key? If it has to check the foreign key constraint, then it has to go do a table lookup. Maybe that's slowing it down.

http://wiki.postgresql.org has a ton of tips.

share|improve this answer
It does have a foreign key, and a trigger. I disabled both of these and it had no effect. Thanks for the wiki link though. –  Johan May 26 '10 at 8:56
add comment

Your Answer


