From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: effective_cache_size cfg
Date: Tue, 16 Oct 2018 10:02:47 +0200
Message-ID: <42dda8dfa0e034b8430b9d2494b42bd63d2a73ff.camel@cybertec.at> (raw)
In-Reply-To: <1539626643680.56712@nrc-cnrc.gc.ca>
References: <1539626643680.56712@nrc-cnrc.gc.ca>
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
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-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, Isabella.Ghiurea@nrc-cnrc.gc.ca
Subject: Re: effective_cache_size cfg
In-Reply-To: <42dda8dfa0e034b8430b9d2494b42bd63d2a73ff.camel@cybertec.at>
* 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