PostgreSQL 10 High Performance

Estimated costs and real-world costs

Now, if you were paying attention to Chapter 3, Database Hardware Benchmarking, you may just be wondering why the random reads are only considered four times as expensive as the sequential ones. In reality, that ratio is closer to 50:1. Unfortunately, building a robust query cost model isn't as easy as measuring each of the underlying real-world costs the theoretical units model, then setting the costs based on those. One of the most obvious ways that the planner costs don't reflect reality is that the optimizer doesn't know what pages are in the database or the OS cache. Random page reads often result from index scans. And the data associated with the most used indexes is also the most likely to be in ...