On: Index bloat reduced in PostgreSQL v14

[jashmatthews]

Tune autovacuum analyze to run every 30mins. Seriously. The query planner needs up to date statistics.

[Dylan16807 → jashmatthews]

Why does it need up to date statistics to decide not to change anything?

I mean, if you could freeze statistics entirely wouldn't that fix this problem?

[jashmatthews → Dylan16807]

Because the contents of the table is changing the statistics are becoming out of date.

[Dylan16807 → jashmatthews]

That doesn't answer the question at all.

The old statistics said to use the index.

If it's still using old statistics, why does the behavior change?

[gmokki → Dylan16807]

Because the user is using values that are no longer covered by the statistics. For example incrementing timestamp or id column. If the stats are from yesterday and they say nothing about the frequency of todays timestamps the query will have to take a pessimistic view of the world. It might be that the data has radically changed since the last stats run, or not. Need to analyze the table to know and make optimal choices.