To: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
cc: pgsql-performance@postgresql.org, Stephen Byers <stephenabyers@yahoo.com>
Subject: Re: why is bitmap index chosen for this query? 
In-reply-to: <20060518164640.GA7291@uio.no> 
References: <20060518155906.GA6909@uio.no>
	<20060518164123.98856.qmail@web30710.mail.mud.yahoo.com>
	<20060518164640.GA7291@uio.no>
Comments: In-reply-to "Steinar H. Gunderson" <sgunderson@bigfoot.com>
	message dated "Thu, 18 May 2006 18:46:40 +0200"
Date: Thu, 18 May 2006 13:34:19 -0400
Message-ID: <16019.1147973659@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes:
> What about the working set? Have you tried running the queries multiple times
> in a row to see if the results change? It might be that your initial bitmap
> scan puts all the relevant bits into cache, which will skew the results.

If the examples were done in the order shown, the seqscan ought to have
pretty well blown out the cache ... but I concur that it'd be
interesting to check whether repeated executions of the same plan show
markedly different times.

Also, is the index order closely correlated to the actual physical
table order?

What is work_mem set to, and does increasing it substantially make the
bitmap scan work any better?

Considering that the query is fetching about half of the table, I'd have
thought that the planner was correct to estimate that bitmap or seqscan
ought to win.  For the plain indexscan to win, the order correlation
must be quite strong, and I'm also guessing that the bitmap scan must
have run into some substantial trouble (like discarding a lot of info
because of lack of work_mem).

IIRC, the planner doesn't currently try to model the effects of a bitmap
scan going into lossy mode, which is something it probably should try to
account for.

			regards, tom lane