Skip site navigation (1) Skip section navigation (2)

Re: Optimizer internals

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
Cc: pgsql-performance(at)lusis(dot)org, PGSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Optimizer internals
Date: 2006-06-16 11:23:26
Message-ID: 87ver18hu9.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Greg StarkDate: 2006-06-16 11:28:35
Subject: Re: SAN performance mystery
Previous:From: PFCDate: 2006-06-16 11:23:15
Subject: Re: Delete operation VERY slow...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group