Обсуждение: effective_cache_size cfg
Hello List,
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)
thank you
Isabella
> > On Oct 15, 2018, at 12:04 PM, Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca> wrote: > > I am running PGSQL 10.4 on Cent OS Pg instance has ' effective_cache_size' parameter not enabled I would like to learnwhat 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 estimatea query, is this the case) That's merely a hint to the optimizer as to how much file data is likely to be cached by the OS--it doesn't directly affectany PG allocations, rather it might influence PG's choice of steps to carry out a query, which of course could indirectlychange the RAM that PG would use. -- Scott Ribe scott_ribe@elevated-dev.com https://www.linkedin.com/in/scottribe/
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