Message-ID: <4D2DB8F9.9050609@shopzeus.com>
Date: Wed, 12 Jan 2011 15:21:45 +0100
From: Laszlo Nagy <gandalf@shopzeus.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
MIME-Version: 1.0
To: Florian Weimer <fweimer@bfk.de>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	Daniel Fekete <danieleff@gmail.com>
Subject: Re: Slow query + why bitmap index scan??
References: <4D2D9B1E.7080302@shopzeus.com> <82y66qp7al.fsf@mid.bfk.de>
In-Reply-To: <82y66qp7al.fsf@mid.bfk.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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