Thread: index creation on 7.3
If I create an index on a table that needs to be vacuumed in 7.3, will the dead rows get indexed as well?
And a related question: should a vacuum also clean out indexes on 7.3? On Tue, 9 Jan 2007, Ben wrote: > If I create an index on a table that needs to be vacuumed in 7.3, will the > dead rows get indexed as well? > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
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.
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Tue, 2007-01-09 at 17:17, Ben wrote:
> 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.
IIRC, CREATE INDEX will ignore tuples that are "definitely dead", ie,
too old to be visible to any open transaction. It *will* (and must)
index tuples that are recently dead but might still be visible to some
open transaction.
regards, tom lane
On Tue, 2007-01-09 at 23:12, Tom Lane wrote: > Scott Marlowe <smarlowe@g2switchworks.com> writes: > > On Tue, 2007-01-09 at 17:17, Ben wrote: > > 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. > > IIRC, CREATE INDEX will ignore tuples that are "definitely dead", ie, > too old to be visible to any open transaction. It *will* (and must) > index tuples that are recently dead but might still be visible to some > open transaction. So, it depends on one's definition of dead... I don't want to go on the cart...