Re: Optimizer internals

Mark Lewis <mark(dot)lewis(at)mir3(dot)com> writes:

> On Thu, 2006-06-15 at 14:05 -0400, John Vincent wrote:
> > Now I've been told by our DBA that we should have been able to wholy
> > satisfy that query via the indexes.
>
> DB2 can satisfy the query using only indexes because DB2 doesn't do
> MVCC.

Well it's more subtle than that. DB2 most certainly does provide MVCC
semantics as does Oracle and MSSQL and any other serious SQL implementation.

But there are different ways to implement MVCC and every database makes
decisions that have pros and cons. Postgres's implementation has some big
benefits over others (no rollback segments, no expensive recovery operations,
fast inserts and updates) but it also has disadvantages (periodic vacuums and
indexes don't cover the data).

The distinction you're looking for here is sometimes called "optimistic"
versus "pessimistic" space management. (Not locking, that's something else.)
Postgres is "pessimistic" -- treats every transaction as if it might be rolled
back. Oracle and most others are "optimistic" assumes every transaction will
be committed and stores information elsewhere to implement MVCC And recover in
case it's rolled back. The flip side is that Oracle and others like it have to
do a lot of extra footwork to do if you query data that hasn't been committed
yet. That footwork has performance implications.

--
greg