>
> If the table has some sort of FK relations it might be being slowed by
> the need to check a row meant to be deleted has any children.
>
If you look at my SQL, there is only one column to be updated. That
column has no foreign key constraint. (It should have, but we did not
want to add that constraint in order to speed up updates.)
>
>
> Perhaps triggers ?
>
Table "product" has no triggers.
>
>
> If the table is very bloated with lots of dead rows (but you did say
> you vacuum frequently and check the results to make sure they are
> effective?) that would slow it down.
>
I'm not sure how to check if the vacuum was effective. But we have
max_fsm_pages=1000000 in postgresql.conf, and I do not get any errors
from the daily vacuum script, so I presume that the table hasn't got too
many dead rows.
Anyway, the table size is only 4GB. Even if half of the rows are dead,
the update should run quite quickly. Another argument is that when I
"select count(*)" instead of "UPDATE", then I get the result in 10
seconds. I don't think that dead rows can make such a big difference
between reading and writing.
My other idea was that there are so many indexes on this table, maybe
the update is slow because of the indexes? The column being updated has
only one index on it, and that is 200MB. But I have heard somewhere that
because of PostgreSQL's multi version system, sometimes the system needs
to update indexes with columns that are not being updated. I'm not sure.
Might this be the problem?
>
>
> A long running transaction elsewhere that is blocking the delete ? Did
> you check the locks ?
>
Sorry, this was an update. A blocking transaction would never explain
why the disk I/O went up to 100% for 2600 seconds.
L