bool / vacuum full bug followup part 2

From: Scott Marlowe <scott(dot)marlowe(at)ihs(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: bool / vacuum full bug followup part 2
Date: 2002-05-03 16:49:15
Message-ID: Pine.LNX.4.33.0205031039280.2391-100000@css120.ihs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

OK, I remembered what was different. I had an index the first time. So,
it was the index I was watchin grow and never shrink.

Now this is reproduceable for me.

I dropped and recreated my test index

drop index test_yn_dx ;
create index test_yn_dx on test (yn);
vacuum full;

[postgres(at)css120] oid2name -d scott.marlowe
All tables from database "scott.marlowe":
---------------------------------
16557 = test
126563 = test_yn_dx

cd into the base/oid of my database, then:

du -s 16557 126563
11128 16557
1772 126563

update test set yn=true where yn=true;
UPDATE 50080

[postgres(at)css120 16556]$ du -s 16557 126563
16704 16557
2948 126563

vacuum;

[postgres(at)css120 16556]$ du -s 16557 126563
16704 16557
2948 126563

vacuum full;

[postgres(at)css120 16556]$ du -s 16557 126563
11128 16557
4100 126563

I tried:

vacuum test_yn_dx;
NOTICE: VACUUM: can not process indexes, views or special system tables
NOTICE: VACUUM: table not found
VACUUM

so, then I tried:

reindex index test_yn_dx;
REINDEX

oid2name -d scott.marlowe
All tables from database "scott.marlowe":
---------------------------------
16557 = test
126564 = test_yn_dx

[postgres(at)css120 16556]$ du -s 16557 126564
11128 16557
1772 126564

And reclaimed the space. Is that the official way, short of dropping and
recreating an index to reclaim its space? Is there a plan to make vacuum
reclaim unused space in indexes? Just wondering.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Uros Gruber 2002-05-03 16:53:07 problem with RULEs
Previous Message David Siebert 2002-05-03 16:33:11 Foxpro