Hi!
In one of our applications we have a database function, which
recalculates COGS (cost of good sold) for certain period. This involves
deleting bunch of rows from one table, inserting them again in correct
order and updating them one-by-one (sometimes one row twice) to reflect
current state. The problem is, that this generates an enormous amount of
tuples in that table.
If I'm correct, the dead tuples must be scanned also during table and
index scan, so a lot of dead tuples slows down queries considerably,
especially when the table doesn't fit into shared buffers any more. And
as I'm in transaction, I can't VACUUM to get rid of those tuples. In one
occasion the page count for a table went from 400 to 22000 at the end.
All this made me wonder, why is new tuple created after every update?
One tuple per transaction should be enough, because you always commit or
rollback transaction as whole. And my observations seem to indicate,
that new index tuple is created after column update even if this column
is not indexed.
One tuple per transaction would save a loads of I/O bandwidth, so I
believe there must be a reason why it isn't implemented as such. Or were
my assumptions wrong, that dead tuples must be read from disk?
Tambet