From: Tom Lane <tgl@sss.pgh.pa.us>
To: Yeb Havinga <yebhavinga@gmail.com>
Cc: Liviu Mirea-Ghiban <liviu.mirea@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query using the Cube contrib module.
Date: Tue, 27 Jul 2010 18:18:50 -0400
Message-ID: <16994.1280269130@sss.pgh.pa.us> (raw)
In-Reply-To: <4C4F3C38.7000006@gmail.com>
References: <4C4C12D1.70601@gmail.com>
<4C4F3C38.7000006@gmail.com>
Yeb Havinga <yebhavinga@gmail.com> writes:
> Liviu Mirea-Ghiban wrote:
>> My question is: Why is it doing a Bitmap Heap Scan / Recheck Cond?
>> I've executed dozens of such queries and not once did the rechecking
>> remove any rows. Is there any way to disable it, or do you have any
>> other suggestions for optimizations (because I'm all out of ideas)?
> It's probably because the index nodes store data values with a lossy
> compression, which means that the index scan returns more rows than
> wanted, and that in turn is filtered out by the rescanning.
The recheck expression is only executed if the index reports that it's
not executed the search exactly. If you don't see any difference
between the indexscan and bitmapscan output counts, it's probably
because the index can do the case exactly, so the recheck expression
isn't really getting used. The planner has to include the expression
in the plan anyway, because the decision about lossiness is not known
until runtime. But it's not costing any runtime.
The OP is mistaken to think there's anything wrong with this plan choice
---- more than likely, it's the best available plan. The reason there's
a significant gap between the indexscan runtime and the bitmapscan
runtime is that that's the cost of going and actually fetching all those
rows from the table. The only way to fix that is to buy a faster disk
or get more RAM so that more of the table can be held in memory.
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, yebhavinga@gmail.com, liviu.mirea@gmail.com
Subject: Re: Slow query using the Cube contrib module.
In-Reply-To: <16994.1280269130@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