Thread

1. does vacuum rebuild index?

   mikeo <mikeo@spectrumtelecorp.com> — 2000-06-12T21:02:37Z

   hi,
    we have a 34 million row table.  after many inserts, updates, deletes 
   the performance degraded so we vacuumed the table.  the output indicated 
   that the index was also vacuumed but the size didn't change. it was still 
   80m so we dropped and recreated it reducing it to 20m.  no big deal but
   is it common for a vacuum to "miss" the index, so to speak?  i saw that 
   someone else asked a similar question on 6/4 in the general archives but 
   i found no reply to it.  is this a common occurrence or am i doing something 
   wrong or maybe should be doing something different?  this is a 7 column 
   index on an 11 column table. 
     
   
    xxxxxxx1     | varchar(15) |
    xxxxxxx2     | bigint      |
    xxxxxxx3     | varchar(15) |
    xxxxxxx4     | varchar(15) |
    xxxxxxx5     | bigint      |
    xxxxxxx6     | float8      |
    xxxxxxx7     | float8      |
    xxxxxxx8     | float8      |
    xxxxxxx9     | date        |
    xxxxxxx10    | integer     |
    xxxxxxx11    | bigint      |
   
   the index is on columns 1-5,10,11
   
   thanks,
     mikeo
   
   

2. Re: does vacuum rebuild index?

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-12T21:20:12Z

   mikeo <mikeo@spectrumtelecorp.com> writes:
   >  we have a 34 million row table.  after many inserts, updates, deletes 
   > the performance degraded so we vacuumed the table.  the output indicated 
   > that the index was also vacuumed but the size didn't change. it was still 
   > 80m so we dropped and recreated it reducing it to 20m.
   
   This is in the FAQ isn't it?  VACUUM removes unused index entries but it
   doesn't reduce the physical size of the index file.  There's an item on
   the TODO list to fix that, but for now a lot of people are in the habit
   of doing
   	drop indexes;
   	vacuum;
   	recreate indexes;
   
   This is often faster than what vacuum does, so there's been talk of
   changing vacuum to work that way, but there's concern about what happens
   if vacuum crashes before it's rebuilt the indexes... at least with the
   delete-unused-entries approach you aren't left with an incomplete index.
   
   			regards, tom lane