Re: index creation on 7.3

On Tue, 2007-01-09 at 17:17, Ben wrote:
> And a related question: should a vacuum also clean out indexes on 7.3?

Should, might not. There definitely was a problem in 7.2 with indexes
on ever increasing values where the indexes would grow on one side of
the btree only. In 7.2 I had a table of about 80k in size with an 80
meg index at one point. a reindex will fix that problem. I skipped 7.3
and went to 7.4, where that was fixed. Not sure if 7.3 fixed that
problem or not.

Even with 7.4 and on, if an index becomes sparsely populated you can
have rather bloated indexes. I.e. if you delete 99 out of every 100
entries in a table, and you can fit >100 entries in an index for a row's
index, then the indexes will be 99% empty, but the space they take up
can't be easily collapsed. This might or might not have gotten better
with more recent versions.

> > If I create an index on a table that needs to be vacuumed in 7.3, will the
> > dead rows get indexed as well?

Yes. I think. I'm pretty sure that's still true in 8.2. as well. The
way indexes work is that the index points to the oldest version of a
tuple in existence, said tuple which then points to the next newer
version and so on. So, definitely for old versions of rows that still
exist they would be "indexed" in a manner of speaking. Not sure about
deleted rows. I'm guessing that the db engine can't tell if they're
deleted or not because it can't be sure there's not an older transaction
in progress than the deleted tuple easily.