The recommendation to set low checkpoint_segments during restore is a bit strange. Also, not a single word about checkpoint_timeout (when the configuration should aim for timed checkpoints in the first place).
Haven't read further.
https://www.packtpub.com/big-data-and-business-intelligence/...
Note that, as published, it's a bit dated, but by and large much still is applicable even today. Once you learn the basics the PostgreSQL docs and release notes can catch you up to what's different.
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.