Re: Forcing index scan on query produces 16x faster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-----Original Message-----
From: Robert Haas [mailto:robertmhaas@xxxxxxxxx] 
Sent: Wednesday, March 24, 2010 5:47 PM
To: Eger, Patrick
Cc: Christian Brink; pgsql-performance@xxxxxxxxxxxxxx
Subject: Re:  Forcing index scan on query produces 16x faster

On Wed, Mar 17, 2010 at 9:01 PM, Eger, Patrick <peger@xxxxxxxxxxxxxx> 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux