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