> -----Original Message----- > From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] > Sent: Wednesday, July 13, 2005 2:10 PM > To: David Esposito > > Plain VACUUM doesn't try very hard to shorten the table physically, so > that's not surprising either. But the internal free space should get > picked up at this point. > > This does not strike me as an explanation for ongoing bloat. There > are always going to be a few tuples not immediately reclaimable, but > normally that just factors in as part of the steady-state overhead. > Your VACUUM VERBOSE traces showed > > DETAIL: 2 dead row versions cannot be removed yet. > DETAIL: 1 dead row versions cannot be removed yet. > > so you're not having any major problem with not-yet-removable rows. > > So I'm still pretty baffled :-( Hmm, if I keep running the following query while the test program is going (giving it a few iterations to rest between executions), the steady-state usage of the indexes seems to go up ... it doesn't happen every time you run the query, but if you do it 10 times, it seems to go up at least once every few times you run it .. And the usage keeps charging upwards long after the UPDATE query finishes (at least 3 or 4 iterations afterwards until it levels off again) ... It would seem like the steady-state should be reached after the first couple of runs and then never creep up any further because there should be enough slack in the index, right? UPDATE bigboy SET creation_date = CURRENT_TIMESTAMP WHERE creation_date BETWEEN CURRENT_TIMESTAMP - INTERVAL '15 seconds' AND CURRENT_TIMESTAMP - INTERVAL '5 seconds'; Is there any way to disassemble an index (either through some fancy SQL query or by running the actual physical file through a tool) to get an idea on where the slack could be accumulating? like somehow be able to determine that all of the oldest pages have a 0.01% population? At this point I realize I'm grasping at straws and you're welcome to give up on my problem at any time ... you've given it a good run ... :-) -dave ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
