DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	b=igdH9V/QjQ9AcJChn3cRwzH8k7wUVE66r4sO/sFeVNDACLhsMfKSxGm/zuUToMDt5k
	jOh8MRojGSqgrTShkoNB0vlHjPuCUzxuzwSAc1Xou/JpPqvdkMedZq8A7VRhMQHXlIa8
	m2ci4W0RNbdf4B1EfCDu/9rXSBJi4Xks738JE=
Message-ID: <4C4C12D1.70601@gmail.com>
Date: Sun, 25 Jul 2010 13:32:49 +0300
From: Liviu Mirea-Ghiban <liviu.mirea@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.2.7) Gecko/20100713 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Slow query using the Cube contrib module.
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

  Hello,

I have a simple table which has a cube column and a cube GiST index. The 
column contains 3-dimensional points (not cubes or intervals). The 
problem is that I'm getting very slow queries when I'm using the index. 
The table has about 130,000 rows and is full-vacuumed after any 
updates/inserts (it is updated only once every 24 hours).


Table definition:
CREATE TABLE picof.photo_colors
(
   photo_id integer NOT NULL,
   color_percent real NOT NULL,
   lab_color picof.cube NOT NULL
)
WITH (
   OIDS=FALSE
);

CREATE INDEX photo_colors_index
   ON picof.photo_colors
   USING gist
   (lab_color);


My query:
SELECT photo_id FROM photo_colors
WHERE lab_color <@ cube_enlarge('0, 0, 0', 10, 3)


Explain analyze:
"Bitmap Heap Scan on photo_colors  (cost=13.40..421.55 rows=135 width=4) 
(actual time=7.958..15.493 rows=14313 loops=1)"
"  Recheck Cond: (lab_color <@ '(-10, -10, -10),(10, 10, 10)'::cube)"
"  ->  Bitmap Index Scan on photo_colors_index  (cost=0.00..13.36 
rows=135 width=0) (actual time=7.556..7.556 rows=14313 loops=1)"
"        Index Cond: (lab_color <@ '(-10, -10, -10),(10, 10, 10)'::cube)"
"Total runtime: 16.849 ms"
(Executed in PostgreSQL 8.4.4 on Windows and CentOS - same query plan)


Now, while it might not seem much, this is part of a bigger query in 
which several such subqueries are being joined. The cost really adds up.

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)?

Thank you in advance!

---
Liviu Mirea