pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedeffective_cache_size cfg
3+ messages / 3 participants
[nested] [flat]
* effective_cache_size cfg
@ 2018-10-15 18:04 Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
0 siblings, 2 replies; 3+ messages in thread
From: Ghiurea, Isabella @ 2018-10-15 18:04 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: effective_cache_size cfg
@ 2018-10-15 20:03 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Ribe @ 2018-10-15 20:03 UTC (permalink / raw)
To: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>; +Cc: pgsql-admin
>
> 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 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)
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 affect any PG allocations, rather it might influence PG's choice of steps to carry out a query, which of course could indirectly change the RAM that PG would use.
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: effective_cache_size cfg
@ 2018-10-16 08:02 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2018-10-16 08:02 UTC (permalink / raw)
To: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>; pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-10-16 08:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-10-15 18:04 effective_cache_size cfg Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
2018-10-15 20:03 ` Scott Ribe <scott_ribe@elevated-dev.com>
2018-10-16 08:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
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