Dan Harris <[EMAIL PROTECTED]> writes: > I keep the entire database vacuumed regularly.
How often is "regularly"? We get frequent posts from people who think daily or every 4 hours is often enough. If the table is very busy you can need vacuums as often as every 15 minutes. Also, if you've done occasional massive batch updates like you describe here you may need a VACUUM FULL or alternatively a CLUSTER command to compact the table -- vacuum identifies the free space but if you've doubled the size of your table with a large update that's a lot more free space than you want hanging around waiting to be used. > For example, as I'm writing this, I am running an UPDATE statement that will > affect a small part of the table, and is querying on an indexed boolean > field. ... > update eventactivity set ftindex = false where ftindex = true; ( added the > where clause because I don't want to alter where ftindex is null ) It's definitely worthwhile doing an "EXPLAIN UPDATE..." to see if this even used the index. It sounds like it did a sequential scan. Sequential scans during updates are especially painful. If there isn't free space lying around in the page where the updated record lies then another page has to be used or a new page added. If you're doing a massive update you can exhaust the free space available making the update have to go back and forth between the page being read and the end of the table where pages are being written. > ##### > > vmstat output ( as I am waiting for this to finish ): > procs -----------memory---------- ---swap-- -----io---- --system-- > ----cpu---- > r b swpd free buff cache si so bi bo in cs us sy id > wa > 0 1 5436 2823908 26140 9183704 0 1 2211 540 694 336 9 2 76 > 13 [I assume you ran "vmstat 10" or some other interval and then waited for at least the second line? The first line outputted from vmstat is mostly meaningless] Um. That's a pretty meager i/o rate. Just over 2MB/s. The cpu is 76% idle which sounds fine but that could be one processor pegged at 100% while the others are idle. If this query is the only one running on the system then it would behave just like that. Is it possible you have some foreign keys referencing these records that you're updating? In which case every record being updated might be causing a full table scan on another table (or multiple other tables). If those tables are entirely in cache then it could cause these high cpu low i/o symptoms. Or are there any triggers on this table? -- greg ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match