From: | "ktm(at)rice(dot)edu" <ktm(at)rice(dot)edu> |
---|---|
To: | Steve Singer <ssinger(at)ca(dot)afilias(dot)info> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow bitmap heap scans on pg 9.2 |
Date: | 2013-04-10 17:49:52 |
Message-ID: | 20130410174952.GA32580@aart.rice.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Apr 10, 2013 at 11:56:32AM -0400, Steve Singer wrote:
> On 13-04-10 09:56 AM, ktm(at)rice(dot)edu wrote:
> >On Wed, Apr 10, 2013 at 09:49:55AM -0400, Steve Singer wrote:
>
> >
> >Hi Steve,
> >
> >The one thing that stands out to me is that you are working with 200GB of
> >data on a machine with 4-8GB of ram and you have the random_page_cost set
> >to 2.0. That is almost completely uncached and I would expect a value of
> >10 or more to be closer to reality.
>
> Setting random_page_cost to 15 makes the planner choose the
> nested-loop plan (at least the date range I tried).
>
> I thought that the point of effective cache size was to tell the
> planner high likely it is for a random page to be in cache. With
> 200GB of data for this query and an effective cache size of 3.5 GB I
> would have expected that to be accounted for.
>
For random_page_cost to be that low, the database would need to be
mostly cached. 3.5GB is almost 100X too small to do that unless your
query exhibits a large amount of locality of reference. Values for
random_page_cost between 10 and 20 are very reasonable for disk-bound
I/O scenarios.
Regards,
Ken
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2013-04-10 18:06:16 | Re: slow bitmap heap scans on pg 9.2 |
Previous Message | Steve Singer | 2013-04-10 15:56:32 | Re: slow bitmap heap scans on pg 9.2 |