Why is postgresql update query so slow sometimes, even with index

i have a simple update query (foo column type is BOOLEAN (default false)):

update tablename set foo = true where id = 234;

which "id" is set to (primary) key, and if i run "explain analyze" i got:

Index Cond: (id = 234)
Total runtime: 0.358 ms

but still, i have plenty of unexplained queries at slow log (pgfouine), which took more than 200s (?!):

Times exec开发者_运维问答uted: 99, Av. duration (s): 70

can anyone please explain, whats the reason for that? (1.5 mio rows in table, postgresql 8.4)

My first guess would be that you have some other query locking the whole table or the rows that are being updated. Your simple update is the forced to wait for that other operation to complete.

Check, that you don't have any index or constraint on the updated column. If so, then the database might be doing index recalculation or constraint evaluation. These additional tasks are not included in the EXPLAIN ANALYZE result.

Other possibility is that it is slow due to I/O operations. Check this thread about UPDATE performance in Postgres.