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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
