Message-ID: <494F749A.5090603@shopzeus.com>
Date: Mon, 22 Dec 2008 12:06:02 +0100
From: Laszlo Nagy <gandalf@shopzeus.com>
User-Agent: Thunderbird 2.0.0.18 (X11/20081125)
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Slow table update
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

SQL:

update product set sz_category_id=null where am_style_kw1 is not null 
and sz_category_id is not null

query plan:

"Seq Scan on product  (cost=0.00..647053.30 rows=580224 width=1609)"
"  Filter: ((am_style_kw1 IS NOT NULL) AND (sz_category_id IS NOT NULL))"

Information on the table:

row count ~ 2 million
table size: 4841 MB
toast table size: 277mb
indexes size: 4434 MB

Computer: FreeBSD 7.0 stable, Dual Xeon Quad code 5420 2.5GHZ, 8GB 
memory, 6 ES SATA disks in hw RAID 6 (+2GB write back cache) for the 
database.

Autovacuum is enabled. We also perform "vacuum analyze" on the database, 
each day.

Here are some non-default values from postgresql.conf:

shared_buffers=400MB
maintenance_work_mem = 256MB
max_fsm_pages = 1000000

There was almost no load on the machine (CPU: mostly idle, IO: approx. 
5% total) when we started this update.

Maybe I'm wrong with this, but here is a quick calculation: the RAID 
array should do at least 100MB/sec. Reading the whole table should not 
take more than 1 min. I think about 20% of the rows should have been 
updated. Writting out all changes should not take too much time. I 
believe that this update should have been completed within 2-3 minutes.

In reality, after 2600 seconds I have cancelled the query. We monitored 
disk I/O and it was near 100% all the time.

What is wrong?

Thank you,

   Laszlo