Re: index bloat

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> 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