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.
