Re: slow update but have an index

Feite Brekeveld <feite.brekeveld@osiris-it.nl> writes:
> fields. So I made a dump, and hacked the dump into SQL statements like:
> update accounting set status = 'C' where seqno = 1566385;
> ....
> and the other 74,000
> This is awfully slow. How come ? The index on the seqno should give
> speedy access to the record.

I see you've already solved your problem, but for the archives here's
a couple of suggestions:

1. Make sure you actually *are* getting an index scan --- use EXPLAIN
on the query to check.  If not, have you VACUUM ANALYZEd lately?

2. Wrap the series of commands into a single transaction to avoid
per-update transaction overhead:
    BEGIN;
    update ...;
    update ...;
    ...
    COMMIT;
This can save a good deal of disk activity, since each commit forces
fsync.

3. Rethink whether you can't accomplish the same thing in fewer SQL
commands.  The overhead of parsing and planning a query is way more
than the time taken to find and update one single record.  So, the
more work done per command, the better.

            regards, tom lane