>> > Which pg version are you using?
>
> I should have mentionned that before sorry: PostgreSQL 8.2
I think there is an awful lot of speculation on this thread about what
your problem is without anywhere near enough investigation. A couple
of seconds for an update is a really long time, unless your server is
absolutely slammed, in which case probably everything is taking a long
time. We need to get some more information on what is happening here.
Approximately how many requests per second are you servicing? Also,
can you:
1. Run EXPLAIN ANALYZE on a representative UPDATE statement and post
the exact query and the output.
2. Run VACUUM VERBOSE on your database and send the last 10 lines or
so of the output.
3. Try your UPDATE statement at a low-traffic time of day and see
whether it's faster than it is at a high-traffic time of day, and by
how much. Or dump your database and reload it on a dev server and see
how fast it runs there.
...Robert