Mastering PostgreSQL 9.6

Inspecting buffer usage

However, the plan itself is not the only thing which can cause issues. In many cases, dangerous things are hidden on some other level. Memory and caching can lead to undesired behavior, which is often hard to understand for end users who are not trained to see the problem described in this section.

Here is an example:

test=# CREATE TABLE t_random AS           SELECT *           FROM  generate_series(1, 10000000) AS id           ORDER BY random(); SELECT 10000000 test=# ANALYZE t_random ; ANALYZE 

I have generated a simple table containing 10 million rows and created optimizer statistics. In the next step, a simple query retrieving only a handful of rows is executed:

test=# EXPLAIN (analyze true, buffers true, costs true, timing true)   SELECT ...