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