Message-ID: <50770D57.9000409@agliodbs.com>
Date: Thu, 11 Oct 2012 11:17:59 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6;
	rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: shared_buffers/effective_cache_size on 96GB server
References: 
 <CADKbJJUTr9ZWkbajR-7_6qaAsWH0=09=2AMo67C_Az-raKZE4w@mail.gmail.com>
	<CAMkU=1wd9hOT3DSShGt+foCxUCPQgj1UY99fbk3BFS_8yy0YiQ@mail.gmail.com>
	<CADKbJJXDiK6TP-gaj2vDuSF7fjoASQ3WecWQev2YVHo6GKNwmQ@mail.gmail.com>
	<20121010210315.GI11892@momjian.us>
	<CAMkU=1yBJN13z+p1mQ5JczDdqq40TwfKNRNe-07Sb+HSL7pKjA@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1yBJN13z+p1mQ5JczDdqq40TwfKNRNe-07Sb+HSL7pKjA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Jeff,

> Does anyone see effective_cache_size make a difference anyway?  If so,
> in what circumstances?

E_C_S, together with random_page_cost, the table and index sizes, the
row estimates and the cpu_* costs, form an equation which estimates the
cost of doing various kinds of scans, particularly index scan vs. table
scan.  If you have an extremely small database (< shared_buffers) or a
very large database ( > 50X RAM ), the setting for E_C_S probably
doesn't matter, but in the fairly common case where some tables and
indexes fit in RAM and some don't, it matters.

> In my hands, queries for which effective_cache_size might come into
> play (for deciding between seq scan and index scan) are instead
> planned as bitmap scans.

You have a very unusual workload, or a very small database.


-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com