Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

Intel(R) Core(TM)2 Duo CPU E6750 @ 2.66GHz


share|improve this question
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

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.