Skip site navigation (1) Skip section navigation (2)

Re: Forcing index scan on query produces 16x faster

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: "Eger, Patrick" <peger(at)automotive(dot)com>
Cc: Christian Brink <cbrink(at)r-stream(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Forcing index scan on query produces 16x faster
Date: 2010-03-25 00:46:49
Message-ID: 603c8f071003241746i4713bc8fgc55357486d3f0bd3@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Mar 17, 2010 at 9:01 PM, Eger, Patrick <peger(at)automotive(dot)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.  Our
> 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 = 1.0
> random_page_cost = 1.01
> cpu_tuple_cost = 0.0001
> cpu_index_tuple_cost = 0.00005
> cpu_operator_cost = 0.000025
> effective_cache_size = 1000MB
> shared_buffers = 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
> =)

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

In response to

Responses

pgsql-performance by date

Next:From: Campbell, LanceDate: 2010-03-25 00:49:10
Subject: memory question
Previous:From: Christian BrinkDate: 2010-03-24 16:06:35
Subject: Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group