From: Laszlo Nagy <gandalf@shopzeus.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
To: Daniel Fekete <danieleff@gmail.com>
Subject: Slow query + why bitmap index scan??
Date: Wed, 12 Jan 2011 13:14:22 +0100
Message-ID: <4D2D9B1E.7080302@shopzeus.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gandalf@shopzeus.com, danieleff@gmail.com
Subject: Re: Slow query + why bitmap index scan??
In-Reply-To: <4D2D9B1E.7080302@shopzeus.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox