pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: shared_buffers/effective_cache_size on 96GB server
Date: Thu, 11 Oct 2012 11:17:59 -0700
Message-ID: <50770D57.9000409@agliodbs.com> (raw)
In-Reply-To: <CAMkU=1yBJN13z+p1mQ5JczDdqq40TwfKNRNe-07Sb+HSL7pKjA@mail.gmail.com>
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>

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




view thread (34+ messages)  latest in thread

Message-ID: <50770D57.9000409@agliodbs.com>
Permalink:  ../50770D57.9000409@agliodbs.com/
Also on:    postgresql.org/message-id/50770D57.9000409@agliodbs.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com
  Subject: Re: shared_buffers/effective_cache_size on 96GB server
  In-Reply-To: <50770D57.9000409@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox