Thread: does vacuum rebuild index?

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

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