Hey there! Thanks for the feedback. Could you clarify what's incorrect about effective_cache_size? Would love to correct it.
The documentation says this about effective_cache_size [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.
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.
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.