From: Greg Stark <gsstark@mit.edu>
To: Mark Lewis <mark.lewis@mir3.com>
Cc: pgsql-performance@lusis.org, PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Optimizer internals
Date: 16 Jun 2006 07:23:26 -0400
Message-ID: <87ver18hu9.fsf@stark.xeocode.com> (raw)
In-Reply-To: <1150396425.31200.66.camel@archimedes>
References: <c841561b0606151105v459d830era158b9eea5f59a7e@mail.gmail.com>
<1150396425.31200.66.camel@archimedes>
Mark Lewis <mark.lewis@mir3.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, mark.lewis@mir3.com
Subject: Re: Optimizer internals
In-Reply-To: <87ver18hu9.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox