News for hackers. Stuff that matters.

A comment on Index bloat reduced in PostgreSQL v14. Parent comment

Re:Index bloat reduced in PostgreSQL v14 by jashmatthews on Saturday September 11, 2021 @06:26AM (#28490797)
Tune autovacuum analyze to run every 30mins. Seriously. The query planner needs up to date statistics.
[ Parent | on HN ]
Re:Index bloat reduced in PostgreSQL v14 by Dylan16807 on Saturday September 11, 2021 @06:49AM (#28490879)
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?

[ Parent | on HN ]
Re:Index bloat reduced in PostgreSQL v14 by jashmatthews on Saturday September 11, 2021 @07:13AM (#28490990)
Because the contents of the table is changing the statistics are becoming out of date.
[ Parent | on HN ]
Re:Index bloat reduced in PostgreSQL v14 by Dylan16807 on Saturday September 11, 2021 @01:06PM (#28493626)
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?

[ Parent | on HN ]
Re:Index bloat reduced in PostgreSQL v14 by gmokki on Sunday September 12, 2021 @08:21AM (#28500501)
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.
[ Parent | on HN ]