Why is the postgresql update request so slow sometimes, even with an index

Question

I have a simple update request (the foo column type is BOOLEAN (false by default)):

update tablename set foo = true where id = 234; 

for which "id" is set to the (primary) key, and if I run "explain analysis", I got:

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

but still I have a lot of inexplicable queries with a slow log (pgfouine), which took more than 200 s (?!):

 Times executed: 99, Av. duration (s): 70 

can someone explain what is the reason? (1.5 million rows in a table, postgresql 8.4)

Answer 1

My first guess was that you have another query blocking the entire table or rows that are being updated. A simple update is a forced wait for this other operation to complete.

Answer 2

Verify that you do not have an index or restriction for the updated column. If so, the database can perform an index recount or constraint assessment. These additional tasks are not included in the EXPLAIN ANALYZE result.

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