Slow query + why bitmap index scan??

From: Laszlo Nagy <gandalf(at)shopzeus(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, Daniel Fekete <danieleff(at)gmail(dot)com>
Subject: Slow query + why bitmap index scan??
Date: 2011-01-12 12:14:22
Message-ID: 4D2D9B1E.7080302@shopzeus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Florian Weimer 2011-01-12 13:42:42 Re: Slow query + why bitmap index scan??
Previous Message marc.hsiao 2011-01-12 08:21:12 Re: How to turn autovacuum prevent wrap around run faster?