update product set sz_category_id=null where am_style_kw1 is not null
and sz_category_id is not null
"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
Autovacuum is enabled. We also perform "vacuum analyze" on the database,
Here are some non-default values from postgresql.conf:
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?
pgsql-performance by date
|Next:||From: Laszlo Nagy||Date: 2008-12-22 11:34:32|
|Subject: Re: Slow table update|
|Previous:||From: Gregory Stark||Date: 2008-12-22 10:56:50|
|Subject: Re: dbt-2 tuning results with postgresql-8.3.5|