Mastering PostgreSQL 12 - Third Edition

Inspecting buffer usage

However, the plan itself is not the only thing that 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 that was described in this section.

Here is an example that depicts the random insertion of data into the table. The query will generate some randomly ordered data and add it to a new table:

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

Now, we have generated a simple table containing 10 million rows and created the optimizer statistics.

In the next ...