Re: Updates are slow..

On Wed, 12 Jun 2002 10:04:54 -0400, "Tom Burke" <lists(at)spamex(dot)com>
wrote:
>An index on the email column slows down an update on the dept column,
>for some unkown reason.
> [...]
>eppend=# create index emp_ix03 on emp(email);
>
>After creating this index, the update time jumped up again to 21 mins!
>[...]
>This makes no sense to me because the new index is not related to
>the column being updated at all.

You seem to think that an UPDATE of one column should not affect an
index on another column. However, with PostgreSQL's MVCC (Multi
Version Concurrency Control) an UPDATE behaves like a DELETE followed
by an INSERT, so to speak.

>This is not really an emp table, and in fact ~600K of the email
>addresses are duplicates.

Ouch! Are these real world data? Could you repeat your tests after
making email unique?

First VACUUM ANALYZE; then please time this statement:
UPDATE emp SET email = email || oid;

Then VACUUM ANALYZE and repeat your test. I'm curious what you get.

Servus
Manfred