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
Date: Sun, 11 Sep 2005 18:05:03 +1000 (EST)
Message-ID: <Pine.LNX.4.58.0509111757110.4486@linuxworld.com.au> (raw)
In-Reply-To: <4323E016.2060606@markdilger.com>
References: <4323E016.2060606@markdilger.com>
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
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: swm@alcove.com.au, pgsql@markdilger.com
Subject: Re: performance discrepancy indexing one column versus
In-Reply-To: <Pine.LNX.4.58.0509111757110.4486@linuxworld.com.au>
* 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