bool / vacuum full bug followup part 2

bool / vacuum full bug followup part 2

Post by Scott Marlo » Sun, 05 May 2002 02:13:20



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;


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


16704   16557
2948    126563

vacuum;


16704   16557
2948    126563

vacuum full;


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


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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

1. Subject: bool / vacuum full bug followup part 2

TODO updated:

* Certain indexes will not shrink, e.g. indexes on ever-increasing
  columns and indexes with many duplicate keys      

Yes, but we can't expect people to know to use a partial index when they
are indexing a column like bool.  (Our partial index code didn't even
work for several years.)  (And there are valid reasons to index bool,
like if there are only a few true or false values, as was pointed out.)

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

2. SQL VIEW in grid's RecordSource question

3. OLEDB Problem...Pl. help -

4. CASE tool wanted

5. Performance to connect to local AS

6. Subject: bool / vacuum full bug followup

7. bool / vacuum full bug followup

8. bool / vacuum full bug.

9. Still confused about VACUUM vs. VACUUM FULL