User-Agent: Microsoft-Entourage/11.3.6.070618
Date: Mon, 10 Sep 2007 13:19:03 -0700
Subject: Re: random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Carlo Stonebanks" <stonec.register@sympatico.ca>,
	pgsql-performance@postgresql.org
Message-ID: <C30AF4C7.4230B%llonergan@greenplum.com>
Thread-Topic: [PERFORM] random_page_costs - are defaults of 4.0
	realistic for SCSI RAID 1
Thread-Index: Acfz59RpEtHTtl/bEdywTwAbY5GgAQ==
In-Reply-To: <fc45mi$1tj9$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Should be a lot higher, something like 10-15 is approximating accurate.

Increasing the number of disks in a RAID actually makes the number higher,
not lower.  Until Postgres gets AIO + the ability to post multiple
concurrent IOs on index probes, random IO does not scale with increasing
disk count, but sequential does, thus the increasing "random page cost" as
the RAID gets faster.

The reason to change the number is to try to discourage the planner from
choosing index scans too aggressively.  We (GP) have implemented something
we call "Adaptive Nested Loop" to replace a nested loop + index scan with a
hash join when the selectivity estimates are off in order to improve this
behavior.  We also run with a "random_page_cost=100" because we generally
run on machines with fast sequential I/O.

- Luke 


On 9/10/07 12:25 PM, "Carlo Stonebanks" <stonec.register@sympatico.ca>
wrote:

> Can anyone answer this for me: Although I realize my client's disk subsystem
> (SCSI/RAID Smart Array E200 controller using RAID 1) is less than
> impressive - is the default setting of 4.0 realistic or could it be lower?
> 
> Thanks!
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate