Re: No heap lookups on index

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:

> As an Oracle internals person myself, I don't see how making a comparison
> between the specifics of Oracle's MVCC to PostgreSQL's MVCC is relevant to
> this discussion.
>
> As does *MOST* other commercial databases, Oracle's storage manager performs
> an update-in-place whereas PostgreSQL's (for the most part) does not. There
> are several ways to implement update-in-place, and Oracle has chosen their
> own rollback segment methodology which has issues that without tuning, are
> major hassles. I'm not saying that one is better than the other in ALL
> cases, but I and many other Oracle consultants have tuned Oracle
> installations to eliminate the headaches others in this list have
> mentioned. Any knowledgable Oracle person evaluating PostgreSQL that may be
> reading this list is just going to see it as a lot of anti-Oracle discussion
> with no basis in fact.
>
> Regardless, there is NO WAY to perform an apples-to-apples comparison
> between the implementations, locking strategies, etc. as the MVCC
> implementations and goals are completely different.
...

Well it seems there were lots of facts posted. Yes you can avoid headaches
caused by these issues, but we're not really talking about the headaches.

We're comparing the performance costs of what are update-in-place and
non-update-in-place approach. All of the costs named so far are to some degree
fundamental costs of update-in-place. All you can hope to do in tuning a
system is make sure the costs are kept within manageable bounds.

There are fundamental costs to non-update-in-place as well. The table sizes
are bloated by the amount of space used to store older versions and the dead
tuples that haven't been reused yet. Whether this slows down Postgres as much
as having to do a second (or third or fourth) read to a rollback segment is a
valid area for discussion. It's especially interesting to discuss since the
two costs hit different sets of queries unequally.

> If we want to do a comparison on the how/why Oracle's index implementation
> is faster in the context of this situation and how we could make
> PostgreSQL's faster, let's stick to that.

Well the main difference is the MVCC implementation. Talking about Oracle's
index implementation while avoiding mentioning the elephant in the room would
be sort of pointless.

--
greg