Message-ID: <494F8DFC.1000001@shopzeus.com>
Date: Mon, 22 Dec 2008 13:54:20 +0100
From: Laszlo Nagy <gandalf@shopzeus.com>
User-Agent: Thunderbird 2.0.0.18 (X11/20081125)
MIME-Version: 1.0
To: Gregory Williamson <Gregory.Williamson@digitalglobe.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
CC: Daniel Fekete <danieleff@gmail.com>
Subject: Re: Slow table update
References: <494F749A.5090603@shopzeus.com> <494F7B48.6070204@shopzeus.com>
	<8B319E5A30FF4A48BE7EEAAF609DB233021F389A@COMAIL01.digitalglobe.com>
In-Reply-To: 
 <8B319E5A30FF4A48BE7EEAAF609DB233021F389A@COMAIL01.digitalglobe.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


>
> 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