Slow query + why bitmap index scan??
От | Laszlo Nagy |
---|---|
Тема | Slow query + why bitmap index scan?? |
Дата | |
Msg-id | 4D2D9B1E.7080302@shopzeus.com обсуждение исходный текст |
Ответы |
Re: Slow query + why bitmap index scan??
|
Список | 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
В списке pgsql-performance по дате отправления: