jgpruitt
commented
1 year ago New view on db with 1,496,370 chunks.
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort (cost=349857.53..349857.53 rows=1 width=140) (actual time=1862.135..1862.137 rows=0 loops=1) │
│ Output: cc.id, cc.schema_name, cc.table_name, k.relfrozenxid, (age(k.relfrozenxid)) │
│ Sort Key: (age(k.relfrozenxid)) DESC │
│ Sort Method: quicksort Memory: 25kB │
│ Buffers: shared hit=196214 │
│ -> Nested Loop (cost=0.98..349857.52 rows=1 width=140) (actual time=1862.131..1862.132 rows=0 loops=1) │
│ Output: cc.id, cc.schema_name, cc.table_name, k.relfrozenxid, age(k.relfrozenxid) │
│ Buffers: shared hit=196214 │
│ -> Nested Loop (cost=0.55..349857.03 rows=1 width=136) (actual time=1862.130..1862.132 rows=0 loops=1) │
│ Output: cc.id, cc.schema_name, cc.table_name, k.relfrozenxid │
│ Inner Unique: true │
│ Buffers: shared hit=196214 │
│ -> Nested Loop (cost=0.00..349854.26 rows=1 width=132) (actual time=1862.130..1862.131 rows=0 loops=1) │
│ Output: k.relfrozenxid, k.relname, n.nspname │
│ Inner Unique: true │
│ Join Filter: (k.relnamespace = n.oid) │
│ Buffers: shared hit=196214 │
│ -> Seq Scan on pg_catalog.pg_class k (cost=0.00..349851.72 rows=1 width=72) (actual time=1862.129..1862.130 rows=0 loops=1) │
│ Output: k.oid, k.relname, k.relnamespace, k.reltype, k.reloftype, k.relowner, k.relam, k.relfilenode, k.reltablespace, k.relpages, k.reltuples, k.relallvisible, k.reltoastrelid, k.relhasindex, k.relisshared, k.relpersistence, k.relkind, k.relnatts, k.relchecks, k.relhasrules, k.relhastriggers, k.relhassubclass, k.relrowsecurity, k.relforcerowsecurity, k.relispopulated, k.relreplident, k.relispartition, k.relrewrite, k.relfrozenxid, k.relminmxid, k.relacl, k.reloptions, k.relpartbound │
│ Filter: ((pg_stat_get_last_autovacuum_time(k.oid) IS NULL) AND (pg_stat_get_last_vacuum_time(k.oid) IS NULL) AND (k.reltuples > '0'::double precision) AND (k.relkind = 'r'::"char") AND (pg_stat_get_live_tuples(k.oid) = 0) AND (age(k.relfrozenxid) > ((current_setting('vacuum_freeze_min_age'::text))::bigint / 5))) │
│ Rows Removed by Filter: 4028468 │
│ Buffers: shared hit=196214 │
│ -> Seq Scan on pg_catalog.pg_namespace n (cost=0.00..2.24 rows=24 width=68) (never executed) │
│ Output: n.oid, n.nspname, n.nspowner, n.nspacl │
│ -> Index Scan using chunk_schema_name_table_name_key on _timescaledb_catalog.chunk cc (cost=0.55..2.77 rows=1 width=132) (never executed) │
│ Output: cc.id, cc.hypertable_id, cc.schema_name, cc.table_name, cc.compressed_chunk_id, cc.dropped, cc.status, cc.osm_chunk │
│ Index Cond: ((cc.schema_name = n.nspname) AND (cc.table_name = k.relname)) │
│ -> Index Scan using chunk_compressed_chunk_id_idx on _timescaledb_catalog.chunk c (cost=0.43..0.47 rows=1 width=4) (never executed) │
│ Output: c.id, c.hypertable_id, c.schema_name, c.table_name, c.compressed_chunk_id, c.dropped, c.status, c.osm_chunk │
│ Index Cond: (c.compressed_chunk_id = cc.id) │
│ Filter: (NOT c.dropped) │
│ Query Identifier: 3407063126880899712 │
│ Planning: │
│ Buffers: shared hit=84 │
│ Planning Time: 1.395 ms │
│ Execution Time: 1862.179 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(36 rows)
Description
We have seen instances in which compresses chunks are missing statistics. Autovacuum ignores tables that are missing statistics. Analyzing these tables does not help since we rarely modify chunks after they are compressed. Therefore, these chunks are ignored until they pass the vacuum_freeze_max_age. This can be bad for performance. So, the vacuum engine makes a second pass looking for these chunks and vacuums them. We only use one worker for these.
See also: https://github.com/timescale/promscale/pull/1804
Merge requirements
Please take into account the following non-code changes that you may need to make with your PR: