The following bug has been logged on the website:
Bug reference: 15005
Logged by: David Gould
Email address: daveg@sonic.net
PostgreSQL version: 10.1
Operating system: Linux
Description:
ANALYZE can make pg_class.reltuples wildly inaccurate compared to the
actual
row counts for tables that are larger than the default_statistics_target.
Example from one of a clients production instances:
# analyze verbose pg_attribute;
INFO: analyzing "pg_catalog.pg_attribute"
INFO: "pg_attribute": scanned 30000 of 24519424 pages, containing 6475 live
rows and 83 dead rows; 6475 rows in sample, 800983035 estimated total
rows.
This is a large complex database -- pg_attribute actually has about five
million rows and needs about one hundred thouand pages. However it has
become extremely bloated and is taking 25 million pages (192GB!), about
250
times too much. This happened despite aggressive autovacuum settings and a
periodic bloat monitoring script. Since pg_class.reltuples was 800
million,
our bloat monitoring script did not detect that this table was bloated and
autovacuum did not think it needed vacuuming.
When reltuples is very large compared to the actual row count it causes
problems:
- Bad input to the query planner.
- Prevents autovacuum from processing large bloated tables because
autovacuum_scale_factor * reltuples is large enough the threshold is
rarely
reached.
- Decieves bloat checking tools that rely on the relationship of relpages
to reltuples*average_row_size.
-dg