Message-ID: <42dda8dfa0e034b8430b9d2494b42bd63d2a73ff.camel@cybertec.at>
Subject: Re: effective_cache_size cfg
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Ghiurea, Isabella" <Isabella.Ghiurea@nrc-cnrc.gc.ca>,
	"pgsql-admin@postgresql.org"
	 <pgsql-admin@postgresql.org>
Date: Tue, 16 Oct 2018 10:02:47 +0200
In-Reply-To: <1539626643680.56712@nrc-cnrc.gc.ca>
References: <1539626643680.56712@nrc-cnrc.gc.ca>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

Ghiurea, Isabella wrote:
> I am running PGSQL 10.4  on Cent OS  Pg instance has ' effective_cache_size' parameter not
> enabled I would like to learn what will  the optimizer behavior be in this case ?
> ( default is set to 4GB , I was  thinking  PG 's optimizer will try to use all available
> OS RAM when trying to estimate a query, is this the case)

You should set that parameter to the total amount of RAM available for
your database.  This is so that PostgreSQL has an idea how much of
the operating system's file system cache is used for database files.

It will only influence the estimate for nested loop joins, as far
as I know: the bigger the value, the more likely PostgreSQL is to
pick a nested loop join with an index on the inner side (because it
thinks that the index will be in cache pretty soon).

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com