Message-ID: <4D2D9B1E.7080302@shopzeus.com>
Date: Wed, 12 Jan 2011 13:14:22 +0100
From: Laszlo Nagy <gandalf@shopzeus.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	Daniel Fekete <danieleff@gmail.com>
Subject: Slow query + why bitmap index scan??
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

This will be simple question to answer. :-) There is a single table:

select count(*) from product_price_history  -- 12982555 rows

This  table has exactly one index and on primary key constraint:

CREATE INDEX idx_product_price_history_id_hdate
   ON product_price_history
   USING btree
   (id, hdate);

ALTER TABLE product_price_history
   ADD CONSTRAINT pk_product_price_history PRIMARY KEY(hid);

No more constraints or indexes defined on this table. Rows are never 
updated or deleted in this table, they are only inserted. It was 
vacuum-ed and reindex-ed today.

Stats on the table:

seq scans=13, index scans=108, table size=3770MB, toast table size=8192 
bytes, indexes size=666MB

This query:

select hid from product_price_history where id=35547581

Returns 759 rows in 8837 msec! How can this be that slow???

The query plan is:

"Bitmap Heap Scan on product_price_history  (cost=13.90..1863.51 
rows=472 width=8)"
"  Recheck Cond: (id = 35547581)"
"  ->  Bitmap Index Scan on idx_product_price_history_id_hdate  
(cost=0.00..13.78 rows=472 width=0)"
"        Index Cond: (id = 35547581)"

I don't understand why PostgreSQL uses bitmap heap scan + bitmap index 
scan? Why not just use an regular index scan? Data in a btree index is 
already sorted. A normal index scan should take no more than a few page 
reads. This sould never take 8 seconds.

Thanks,

    Laszlo