3 ms·
> Which is why the statement that effective_cache_size is "the total amount of memory you think Postgres will be able to use" is misleading, as it often leads p
by anarazel 10y ago
> Which is why the statement that effective_cache_size is "the total amount of memory you think Postgres will be able to use" is misleading, as it often leads people to se set effective_cache_size to ~75% of RAM, while in fact they should set it to (75% of RAM)/N where N is the number of concurrently running queries.
Meh. From a practical perspective you usually have inter-backend caching effects. And in my experience a too small e_c_s is much more likely to hurt than a too high one.
- pgaddict 10y agoYou're right there's definitely some inter-backend caching effects, no doubt about that. Still, the common recommendation to set e_c_s to 75% of RAM is a bit excessive (although it's true the blog post does not mention any particular value). Interestingly, my experience with e_c_s overestimates somewhat contradicts yours. The higher the e_c_s value, the more likely index scans are to be chosen. In my experience, the cases when we end up with plans doing lots of random I/O instead of "more sequential plans" due to misestimates, are much worse than the opposite error. Think nestloop vs. other types of joins. Which is why I prefer more conservative e_c_s values.