Date: Thu, 18 May 2006 17:59:06 +0200
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: why is bitmap index chosen for this query?
Message-ID: <20060518155906.GA6909@uio.no>
Mail-Followup-To: pgsql-performance@postgresql.org
References: <20060518155204.66320.qmail@web30701.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <20060518155204.66320.qmail@web30701.mail.mud.yahoo.com>
User-Agent: Mutt/1.5.11+cvs20060403

On Thu, May 18, 2006 at 08:52:04AM -0700, Stephen Byers wrote:
> Could someone explain the results of the following?

It sounds like PostgreSQL badly overestimates the cost of the index scan.
Does the table perchance fit completely into memory, without
effective_cache_size indicating that?

> Also, what is Recheck Cond?

The bitmap index scan will by default allocate one bit per tuple. If it can't
hold a complete bitmap of every tuple in memory, it will fall back to
allocating one bit per (8 kB) page, since it will have to read the entire
page anyhow, and the most dramatic cost is the seek. However, in the latter
case, it will also get a few extra records that don't match the original
clause, so it will have to recheck the condition ("Recheck Cond") before
outputting the tuples to the parent node.

/* Steinar */
-- 
Homepage: http://www.sesse.net/