From: Tom Lane <tgl@sss.pgh.pa.us>
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?
Date: Thu, 18 May 2006 13:34:19 -0400
Message-ID: <16019.1147973659@sss.pgh.pa.us> (raw)
In-Reply-To: <20060518164640.GA7291@uio.no>
References: <20060518155906.GA6909@uio.no>
<20060518164123.98856.qmail@web30710.mail.mud.yahoo.com>
<20060518164640.GA7291@uio.no>
"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
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: tgl@sss.pgh.pa.us, sgunderson@bigfoot.com, stephenabyers@yahoo.com
Subject: Re: why is bitmap index chosen for this query?
In-Reply-To: <16019.1147973659@sss.pgh.pa.us>
* 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