4 ms·

Hey there! Thanks for the feedback. Could you clarify what's incorrect about effective_cache_size? Would love to correct it.

• pgaddict 10y ago
  Well, I'm a bit drunk at the moment, but I'll try explaining anyway ... The documentation says this about effective_cache_size [https://www.postgresql.org/docs/9.5/static/runtime-config-query.html https://www.postgresql.org/docs/9.5/static/runtime-config-qu...]: Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. The important part here is "to a single query". Essentially when computing the cost of an index scan, we have to ask what fraction of blocks (accessed randomly) will be served from a cache (either shared buffers or page cache), and how many will have to access storage. And that depends on how much RAM will be available for a single query, or rather per index scan. 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. Actually, I seem to remember that it's actually "per index scan", but that would only make the difference even more significant. Edit: Nope, I've checked the code and it's actually per query.
  • 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 ago
      You'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.