Not exactly. The dead tuple in the index will be scanned the first time (and its pointed heap tuple as well), then we will mark it dead, then next time we came here, we will know that the index tuple actually points to a uesless tuple, so we will not scan its pointed heap tuple.
But the dead index tuple will still be read from disk next time? Maybe really the performance loss will be neglible, but if most of tuples in your table/index are dead, then it might be significant.
When a block is read from disk, any dead tuples in that block will be read in. Vacuum recovers these.
Consider the often suggested solution for speeding up "select count(*) from table" query: make another table rowcounts and for each of the original tables add insert and delete triggers to update row count in rowcounts table. Actually this is standard denormalization technique, which I use often. For example to ensure that order.total = sum(order_line.total).
This does of course completely destroy concurrency. Since you need to lock the summary table, other clients have to wait until you are done.
Now, if typical inserts into your most active table occur in batches of 3 rows, in one transaction, then row count for this table is updated 3 times during transaction. 3 updates generate 3 tuples, while 2 of them are dead from the very start. You effectively commit 2 useless tuples. After millions of inserts you end up with rowcounts table having 2/3 of dead tuples and queries start to slow down.
Current solution is to vacuum often. My proposal was to create new tuple
only with first update. The next updates in the same transaction would
update the existing tuple, not create a new.
How do you roll back to a savepoint with this model?
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
