From: Mark Lewis <mark.lewis@mir3.com>
To: pgsql-performance@lusis.org
Cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Optimizer internals
Date: Thu, 15 Jun 2006 11:33:45 -0700
Message-ID: <1150396425.31200.66.camel@archimedes> (raw)
In-Reply-To: <c841561b0606151105v459d830era158b9eea5f59a7e@mail.gmail.com>
References: <c841561b0606151105v459d830era158b9eea5f59a7e@mail.gmail.com>
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.
Although MVCC is generally a win in terms of making the database easier
to use and applications less brittle, it also means that the database
must inspect the visibility information for each row before it can
answer a query. For most types of queries this isn't a big deal, but
for count(*) type queries, it slows things down.
Since adding the visibility information to indexes would make them
significantly more expensive to use and maintain, it isn't done.
Therefore, each row has to be fetched from the main table anyway.
Since in this particular query you are counting all rows of the
database, PG must fetch each row from the main table regardless, so the
sequential scan is much faster because it avoids traversing the index
and performing random read operations.
-- Mark Lewis
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: mark.lewis@mir3.com, pgsql-performance@lusis.org
Subject: Re: Optimizer internals
In-Reply-To: <1150396425.31200.66.camel@archimedes>
* 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