Re: update query taking too long - Mailing list pgsql-performance

Chris wrote:
> db=# UPDATE email_upd_test SET domainname=substring(email from
> position('@' in email));
> UPDATE 1000000
> Time: 43796.030 ms
>
> I think I'm I/O bound from my very limited understanding of vmstat.

Well, 43 seconds to update 1 million rows suggests your real query
should be complete in a few minutes, even if your real table has more
columns.

Could you check again and just make sure you don't have a foreign key
referencing this table? I suspect a large table without an index on the
referencing column.

If you can't see anything, cancel the long-running query, run VACUUM
FULL VERBOSE on the table, ANALYSE VERBOSE and then try it again.
There's something very odd here.

--
   Richard Huxton
   Archonet Ltd