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.)
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?
Another question. Do you think that increasing shared_mem would make it
faster?
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