3 ms·

Actually, further to your point, the documentation states this explanation of the default for random/seq_page_cost: "The default value can be thought of as mod

Actually, further to your point, the documentation states this explanation of the default for random/seq_page_cost: "The default value can be thought of as modeling random access as 40 times slower than sequential, while expecting 90% of random reads to be cached If you believe a 90% cache rate is an incorrect assumption for your workload, you can increase random_page_cost to better reflect the true cost of random storage reads. Correspondingly, if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, decreasing random_page_cost can be appropriate. Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for random_page_cost." In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment.

wfn 11y ago
> In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment. Fair enough. Though I think they have in mind cache misses in the sense of page faults here? - i.e. "it's not in memory - have to read from disk". (i.e. cache as in Postgres internal cache (+ OS filesystem cache, actually/IIRC). The variable name `random_page_cost` seems to suggest this (load a memory page into memory - from disk). Whereas I had in mind cache as in CPU cache[1] - where sort operation happens which does lots of cache misses (between CPU cache and RAM) - and needs to read from rest of RAM (i.e. random memory access is not uniformly random). It's a nice rabbit hole.. [1]: https://en.wikipedia.org/wiki/CPU_cache https://en.wikipedia.org/wiki/CPU_cache
mvc 11y ago
> If you believe a 90% cache rate is an incorrect assumption for your workload For people wondering as I did about how you might monitor for this, it looks like the data you need is in pg_statio_all_indexes

wfn 11y ago

> In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment. Fair enough. Though I think they have in mind cache misses in the sense of page faults here? - i.e. "it's not in memory - have to read from disk". (i.e. cache as in Postgres internal cache (+ OS filesystem cache, actually/IIRC). The variable name `random_page_cost` seems to suggest this (load a memory page into memory - from disk). Whereas I had in mind cache as in CPU cache[1] - where sort operation happens which does lots of cache misses (between CPU cache and RAM) - and needs to read from rest of RAM (i.e. random memory access is not uniformly random). It's a nice rabbit hole.. [1]: https://en.wikipedia.org/wiki/CPU_cache https://en.wikipedia.org/wiki/CPU_cache

mvc 11y ago

> If you believe a 90% cache rate is an incorrect assumption for your workload For people wondering as I did about how you might monitor for this, it looks like the data you need is in pg_statio_all_indexes