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=auVQNMcZgeD+6GrdqXh/uFJ5LVOh68zNr2VSrkLrSuNOBj0aQNgBm4J95abAR8vj3G
	1sHqZnUg9Q3/TA7hyD2fzQ4zoT7axxbhTJ3/qu/37BnFzXz0Tt6s9QZrDU9iuHyH183l
	4Yqiht22p/uXzBgB3bl/92ptR7KLs7SzzE+dU=
MIME-Version: 1.0
In-Reply-To: 
 <1CFD7891521AAB4E8201FB7A78C9D36F05453FD7@mail-001.corp.automotive.com>
References: <4BA148CF.5080701@r-stream.com>
	<1CFD7891521AAB4E8201FB7A78C9D36F05453FD7@mail-001.corp.automotive.com>
Date: Wed, 24 Mar 2010 20:46:49 -0400
Message-ID: <603c8f071003241746i4713bc8fgc55357486d3f0bd3@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
Content-Transfer-Encoding: quoted-printable

On Wed, Mar 17, 2010 at 9:01 PM, Eger, Patrick <peger@automotive.com> wrote=
:
> I'm running 8.4.2 and have noticed a similar heavy preference for
> sequential scans and hash joins over index scans and nested loops. =A0Our
> database is can basically fit in cache 100% so this may not be
> applicable to your situation, but the following params seemed to help
> us:
>
> seq_page_cost =3D 1.0
> random_page_cost =3D 1.01
> cpu_tuple_cost =3D 0.0001
> cpu_index_tuple_cost =3D 0.00005
> cpu_operator_cost =3D 0.000025
> effective_cache_size =3D 1000MB
> shared_buffers =3D 1000MB
>
>
> Might I suggest the Postgres developers reconsider these defaults for
> 9.0 release, or perhaps provide a few sets of tuning params for
> different workloads in the default install/docs? The cpu_*_cost in
> particular seem to be way off afaict. I may be dead wrong though, fwiw
> =3D)

The default assume that the database is not cached in RAM.  If it is,
you want to lower seq_page_cost and random_page_cost to something much
smaller, and typically make them equal.  I often recommend 0.005, but
I know others have had success with higher values.

Ultimately it would be nice to have a better model of how data gets
cached in shared_buffers and the OS buffer cache, but that is not so
easy.

...Robert