Mastering PostgreSQL 12 - Third Edition

Inspecting estimates

However, there is something that should always be done: we should make sure that estimates and real numbers are reasonably close together. In some cases, the optimizer will make poor decisions because the estimates are way off for some reason. Sometimes, estimates can be off because the system statistics are not up to date. Therefore, running an ANALYZE clause is definitely a good thing to start with. However, optimizer stats are mostly taken care of by the auto-vacuum daemon, so it is definitely worth considering other options that are causing bad estimates.

Take a look at the following example, which helps us add some data to a table:

test=# CREATE TABLE t_estimate AS        SELECT * FROM generate_series(1, 10000) AS id; ...