Re: No heap lookups on index

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> >> Oracle does, but you pay in other ways. Instead of keeping dead tuples
> >> in the main heap, they shuffle them off to an 'undo log'. This has some
> >> downsides:
> >> Rollbacks take *forever*, though this usually isn't much of an issue
> >> unless you need to abort a really big transaction.
> 
> > It's a good point though.  Surely a database should be optimised for the 
> > most common operation - commits, rather than rollbacks?
> 
> The "shuffling off" of the data is expensive in itself, so I'm not sure
> you can argue that the Oracle way is more optimal for commits either.

You pay in Oracle when you read these records too. If there are pending
updates you have to do a second read to the rollback segment to get the old
record. This hits long-running batch queries especially hard since by the time
they finish a large number of the records they're reading could have been
updated and require a second read to the rollback segments.

You also pay if the new value is too big to fit in the same space as the old
record. Then you get to have to follow a pointer to the new location. Oracle
tries to minimize that by intentionally leaving extra free space but that has
costs too.

And lastly rollback segments are of limited size. No matter how big you make
them there's always the risk that a long running query will take long enough
that data it needs will have expired from the rollback segments.

Oh, and note that optimizing for the common case has limits. Rollbacks may be
rare but one of the cases where they are effectively happening is on recovery
after a crash. And that's one process you *really* don't want to take longer
than necessary...

-- 
greg