DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=smixQAP5eWrfoxUXnEE4ypZi9ScZv1gi2vhI6XdkM1hCW4JcPSHJHtJg6Drctv5yYg
	ZOdakLdL3W683VMJIUYSO7kCs/BkhfwWut1kmwNJd+lBE41/L51ikRr3/MaQQiXKkqqM
	MHkyWIcB+1UJtfiVXaYQDy8K+0cdlu87I3Gas=
Message-ID: <4C4F3C38.7000006@gmail.com>
Date: Tue, 27 Jul 2010 22:06:16 +0200
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Liviu Mirea-Ghiban <liviu.mirea@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Slow query using the Cube contrib module.
References: <4C4C12D1.70601@gmail.com>
In-Reply-To: <4C4C12D1.70601@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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