From: Robert Haas <robertmhaas@gmail.com>
To: Eger, Patrick <peger@automotive.com>
Cc: Christian Brink <cbrink@r-stream.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Forcing index scan on query produces 16x faster
Date: Wed, 24 Mar 2010 22:16:59 -0400
Message-ID: <603c8f071003241916t8babf14jd8e499a6591be97b@mail.gmail.com> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robertmhaas@gmail.com, peger@automotive.com, cbrink@r-stream.com
Subject: Re: Forcing index scan on query produces 16x faster
In-Reply-To: <603c8f071003241916t8babf14jd8e499a6591be97b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox