Skip site navigation (1) Skip section navigation (2)

Re: Slow query using the Cube contrib module.

From: Yeb Havinga <yebhavinga(at)gmail(dot)com>
To: Liviu Mirea-Ghiban <liviu(dot)mirea(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow query using the Cube contrib module.
Date: 2010-07-27 20:06:16
Message-ID: 4C4F3C38.7000006@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2010-07-27 20:40:02
Subject: Re: Pooling in Core WAS: Need help in performance tuning.
Previous:From: Greg SpiegelbergDate: 2010-07-27 20:02:08
Subject: Re: how to handle a big table for data log

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group