Re: simple update queries take a long time - postgres 8.3.1

On 31/03/2008 20:38, mark wrote:

> EXPLAIN ANALYZE update users set number_recieved=number_recieved+1 where
> uid=738889333;
>                                                     QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------
>  Index Scan using idx_uid on users  (cost=0.00..8.46 rows=1 width=1073)
> (actual time=0.094..0.161 rows=1 loops=1)
>    Index Cond: (uid = 738889333)
>  Total runtime: 11479.053 ms

Is there anything going on that might affect the time taken? - triggers,
loads of indices to be updated, etc? From the docs[1]:

"For INSERT, UPDATE, and DELETE commands, the total run time might be
considerably larger, because it includes the time spent processing the
result rows. In these commands, the time for the top plan node
essentially is the time spent computing the new rows and/or locating the
old ones, but it doesn't include the time spent applying the changes.
Time spent firing triggers, if any, is also outside the top plan node,
and is shown separately for each trigger."

Ray.

[1] http://www.postgresql.org/docs/8.3/static/using-explain.html

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------