Re: Slow query + why bitmap index scan??

On Wed, Jan 12, 2011 at 03:21:45PM +0100, Laszlo Nagy wrote:
> On 2011-01-12 14:42, Florian Weimer wrote:
>> * Laszlo Nagy:
>>
>>> This query:
>>>
>>> select hid from product_price_history where id=35547581
>>>
>>> Returns 759 rows in 8837 msec! How can this be that slow???
>> If most records are on different heap pages, processing this query
>> requires many seeks. 11ms per seek is not too bad if most of them are
>> cache misses.
> How about this:
>
> select id,hdate from product_price_history where id=35547581 -- 759 rows,
> 8837 ms
> Query time average: 3 sec.
> Query plan:
>
> "Bitmap Heap Scan on product_price_history (cost=13.91..1871.34 rows=474
> width=16)"
> " Recheck Cond: (id = 35547582)"
> " -> Bitmap Index Scan on idx_product_price_history_id_hdate
> (cost=0.00..13.79 rows=474 width=0)"
> " Index Cond: (id = 35547582)"
>
> Why still the heap scan here? All fields in the query are in the index...
> Wouldn't a simple index scan be faster? (This is only a theoretical
> question, just I'm curious.)
>

Because of PostgreSQL's MVCC design, it must visit each heap tuple
to check its visibility as well as look it up in the index.

> My first idea to speed things up is to cluster this table regularly. That
> would convert (most of the) rows into a few pages. Few page reads -> faster
> query. Is it a good idea?
>

Yes, clustering this table would greatly speed up this type of query.

> Another question. Do you think that increasing shared_mem would make it
> faster?

I doubt it.

>
> Currently we have:
>
> shared_mem = 6GB
> work_mem = 512MB
> total system memory=24GB
>
> Total database size about 30GB, but there are other programs running on the
> system, and many other tables.
>
> Thanks,
>
> Laszlo
>

Clustering is your best option until we get indexes with visibility
information.

Cheers,
Ken