On Wed, Apr 10, 2013 at 11:56:32AM -0400, Steve Singer wrote: > On 13-04-10 09:56 AM, ktm@xxxxxxxx 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
