DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=ZzDbcjjOnDBJHHQdTpyNRf2uvnM1VJNUhECxCHWSth8Cs5f1wzEM0cd8SL1J5ZF2d7
	mtJMkimjKWksICuHVaWe0byrC/5WGKzNY0t4ULbfdIHxBVjwAgFWZNTBqWz8BiU0gMFU
	pN2CT1pq7Lqq2gp1m+tJ7RjhrIS7vmiRD9+Yw=
MIME-Version: 1.0
In-Reply-To: 
 <1CFD7891521AAB4E8201FB7A78C9D36F05454451@mail-001.corp.automotive.com>
References: <4BA148CF.5080701@r-stream.com>
	<1CFD7891521AAB4E8201FB7A78C9D36F05453FD7@mail-001.corp.automotive.com>
	<603c8f071003241746i4713bc8fgc55357486d3f0bd3@mail.gmail.com>
	<1CFD7891521AAB4E8201FB7A78C9D36F05454451@mail-001.corp.automotive.com>
Date: Wed, 24 Mar 2010 22:16:59 -0400
Message-ID: <603c8f071003241916t8babf14jd8e499a6591be97b@mail.gmail.com>
Subject: Re: Forcing index scan on query produces 16x faster
From: Robert Haas <robertmhaas@gmail.com>
To: "Eger, Patrick" <peger@automotive.com>
Cc: Christian Brink <cbrink@r-stream.com>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Mar 24, 2010 at 8:59 PM, Eger, Patrick <peger@automotive.com> wrote:
> Ok, the wording is a bit unclear in the documentation as to whether it is the cost for an entire *page* of tuples, or actual tuples. So something like the following might give better results for a fully-cached DB?
>
> seq_page_cost = 1.0
> random_page_cost = 1.1 #even memory has random access costs, lack of readahead, TLB misses, etc
> cpu_tuple_cost = 1.0
> cpu_index_tuple_cost = 0.5
> cpu_operator_cost = 0.25
> effective_cache_size = 1000MB
> shared_buffers = 1000MB

Yeah, you can do it that way, by jacking up the cpu_tuple costs.  I
prefer to lower the {random/seq}_page_cost values because it keeps the
cost values in the range I'm used to seeing, but it works out to the
same thing.

I am not sure that there is any benefit from making random_page_cost >
seq_page_cost on a fully cached database.  What does readahead mean in
the context of cached data?  The data isn't likely physically
contiguous in RAM, and I'm not sure it would matter much if it were.
Basically, what random_page_cost > seq_page_cost tends to do is
discourage the use of index scans in borderline cases, so you want to
benchmark it and figure out which way is faster and then tune
accordingly.

...Robert