From: Yeb Havinga <yebhavinga@gmail.com>
To: 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 22:06:16 +0200
Message-ID: <4C4F3C38.7000006@gmail.com> (raw)
In-Reply-To: <4C4C12D1.70601@gmail.com>
References: <4C4C12D1.70601@gmail.com>
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. See the
comments for the 'RECHECK' parameter of CREATE OPERATOR CLASS
(http://www.postgresql.org/docs/8.4/static/sql-createopclass.html). Its
unwise to alter this behaviour without taking a look/modifying the
underlying index implementation. The gist index scann part could perhaps
be made a bit faster by using a smaller blocksize, but I'm not sure if
or how the recheck part can be improved. Maybe rewriting the top query
to not do bitmap heap scans in subqueries or inner loops?
regards,
Yeb Havinga
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: yebhavinga@gmail.com, liviu.mirea@gmail.com
Subject: Re: Slow query using the Cube contrib module.
In-Reply-To: <4C4F3C38.7000006@gmail.com>
* 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