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:content-transfer-encoding;
	b=HCdS084PeqQZSLYwr2WcoZtbjXQ3zS+NrrIZ2crLgARBC5Ikgw+CkHYaqoBMlj+tgW
	lfr+gxhccLTmVXJ3+5B6cKDL9EuAmkD6vxxOu/kP+IhSucFUGfnrHpKgrG5fiR8Sc62s
	YKGdeXhcCGJ+pFsx42i/UBZjElbvfHa4tl5UA=
MIME-Version: 1.0
In-Reply-To: <4DB6F4BB020000250003CEB9@gw.wicourts.gov>
References: <BANLkTikZhYc+h1=YDJfXwZEZc1tN3_VwCg@mail.gmail.com>
	<4DB6F4BB020000250003CEB9@gw.wicourts.gov>
Date: Wed, 27 Apr 2011 07:23:42 +0800
Message-ID: <BANLkTinzFYAzySYcaT0AWPj++RK3Yi5dZg@mail.gmail.com>
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
From: Sok Ann Yap <sokann@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

On Wed, Apr 27, 2011 at 5:37 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Sok Ann Yap <sokann@gmail.com> wrote:
>
>> So, index scan wins by a very small margin over sequential scan
>> after the tuning. I am a bit puzzled because index scan is more
>> than 3000 times faster in this case, but the estimated costs are
>> about the same. Did I do something wrong?
>
> Tuning is generally needed to get best performance from PostgreSQL.
> Needing to reduce random_page_cost is not unusual in situations
> where a good portion of the active data is in cache (between
> shared_buffers and the OS cache). =C2=A0Please show us your overall
> configuration and give a description of the hardware (how many of
> what kind of cores, how much RAM, what sort of storage system). =C2=A0The
> configuration part can be obtained by running the query on this page
> and pasting the result into your next post:
>
> http://wiki.postgresql.org/wiki/Server_Configuration
>
> There are probably some other configuration adjustments you could do
> to ensure that good plans are chosen.
>
> -Kevin
>

Here's the configuration (this is just a low end laptop):

            name            |
             current_setting
----------------------------+----------------------------------------------=
---------------------------------------------------------------------------=
------
 version                    | PostgreSQL 9.0.4 on x86_64-pc-linux-gnu,
compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.5.2 p1.0, pie-0.4.5)
4.5.2, 64-bit
 checkpoint_segments        | 16
 default_statistics_target  | 10000
 effective_cache_size       | 512MB
 lc_collate                 | en_US.UTF-8
 lc_ctype                   | en_US.UTF-8
 listen_addresses           | *
 log_destination            | syslog
 log_min_duration_statement | 0
 maintenance_work_mem       | 256MB
 max_connections            | 100
 max_stack_depth            | 2MB
 port                       | 5432
 random_page_cost           | 4
 server_encoding            | UTF8
 shared_buffers             | 256MB
 silent_mode                | on
 TimeZone                   | Asia/Kuala_Lumpur
 wal_buffers                | 1MB
 work_mem                   | 32MB
(20 rows)

The thing is, the query I posted was fairly simple (I think), and
PostgreSQL should be able to choose the 3000+ times faster index scan
with the default random_page_cost of 4. If I need to reduce it to 2
when using a 5.4k rpm slow disk, what is random_page_cost =3D 4 good
for?

(Sorry for the double message, I forgot to CC the list in the first one)