Re: index bloat in 8.4-dev

On Mon, 2008-10-06 at 11:10 +0300, Heikki Linnakangas wrote:
> Jeff Davis wrote:
> > I am seeing index bloat in the current head when the indexed values are
> > constantly increasing, and the lower values are being constantly
> > deleted.
> >
> > ...
> > It's possible that this has something to do with the new FSM.
>
> Yep, it clearly has. Looks like I forgot about the index FSMs when I
> added the FSM vacuum code. Index FSMs need to be vacuumed just like the
> heap FSM. Fixed, thanks for the report.

There is some other bug still at work here. I am still seeing some
pretty severe bloat with the same script I attached in the previous
email. This is the current head (including your patch).

Note that these results are after running my script for about an hour
(although you can probably see the effects after 10 minutes), and my
script has a VACUUM after every DELETE.

I see this problem on 8.3.3 now, too. Originally, I suppose my test was
not long enough, but now I see the problem after about 10 minutes of
running.

Regards,
Jeff Davis

-- results from current head
=> select relpages from pg_class where relname = 'foo_i_key';
relpages
----------
35255
(1 row)

=> select relpages from pg_class where relname = 'foo_i_key';
relpages
----------
35255
(1 row)

=> select count(*) from mytest.foo;
count
--------
100000
(1 row)

=> vacuum verbose mytest.foo;
INFO: vacuuming "mytest.foo"
INFO: index "foo_i_key" now contains 101163 row versions in 35255 pages
DETAIL: 0 index row versions were removed.
35029 index pages have been deleted, 35029 are currently reusable.
CPU 0.11s/0.07u sec elapsed 0.20 sec.
INFO: "foo": found 0 removable, 101163 nonremovable row versions in 785
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 98837 unused item pointers.
0 pages are entirely empty.
CPU 0.11s/0.08u sec elapsed 0.21 sec.
VACUUM

=> select min(i), max(i) from mytest.foo;
min | max
-----------+-----------
205100001 | 205200000
(1 row)