Re: effective_cache_size cfg

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