Date: Sun, 11 Sep 2005 18:05:03 +1000 (EST)
From: Gavin Sherry <swm@alcove.com.au>
To: Mark Dilger <pgsql@markdilger.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance discrepancy indexing one column versus
In-Reply-To: <4323E016.2060606@markdilger.com>
Message-ID: <Pine.LNX.4.58.0509111757110.4486@linuxworld.com.au>
References: <4323E016.2060606@markdilger.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Sun, 11 Sep 2005, Mark Dilger wrote:

> All,
>
> In the psql output below, I would expect the second query to run faster,
> because the b-tree index on two columns knows the values of 'b' for any
> given value of 'a', and hence does not need to fetch a row from the
> actual table.  I am not seeing a speed-up, however, so I expect my
> understanding of the index mechanism is wrong.  Could anyone enlighten
> me?

A common but incorrect assumption. We must consult the underlying table
when we do an index scan so that we can check visibility information. The
reason it is stored there in the table is so that we have only one place
to check for tuple visibility and therefore avoid race conditions.

A brief explanation of this system is described here:
http://www.postgresql.org/docs/8.0/static/mvcc.html.

and this page shows what information we store in the to do visibility
checks:

http://www.postgresql.org/docs/8.0/static/storage-page-layout.html

Thanks,

Gavin