Bruce Momjian <bruce@momjian.us> writes:
>>> https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS
>>>
>>> Random access to mechanical disk storage is normally much more expensive
>>> than four times sequential access. However, a lower default is used
>>> (4.0) because the majority of random accesses to disk, such as indexed
>>> reads, are assumed to be in cache. 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.
This explanation is, in fact, made up out of whole cloth. It has
diddly-squat to do with where the number came from, although maybe
it's an okay rationalization for continuing to use 4.0 on modern
hardware.
Where the number came from is that I did a bunch of testing back in
the late 90s and random_page_cost = 4.0 made the planner's ratios of
seqscan vs indexscan costs match up with observed timings. Of course,
those observations were made with spinning-rust drives, so you'd not
get the same results on SSDs.
> Yes, I can't say there is much research behind the value, and even if
> there was, the assumed hardware is unlikely to be relevant today.
Yes as to the latter, no as to the former.
regards, tom lane
