There is a table in the db, whose index_scan count from pg_stat_all_tables for this table seems to be too high, there are not that many queries being executed against this table. Wondering how this count can be too high.
- The db is up since 80 days so I assume these are cumulative stats since last startup?
- Could it be possible that a query is using this table joining other tables, and this table is being probed multiple times in loops. Below is a googled part of the plan showing parallel index only scan happened 5 times for the index. I am assuming something of this sort is happening making the index scan count going too high. Please let me know if that might be the case.
-> Parallel Index Only Scan using us_geonames_type_idx on us_geonames (cost=0.43..24401.17 rows=559758 width=4) (actual time=0.036..90.309 rows=447806 loops=5)
- Is there any other possible explanation for this high count. I see updates do increase this count but there are not those many updates. Inserts and deletes do not seem to touch this counter.
-[ RECORD 1 ]-------+------------------------------
relid | 3029143981
schemaname | myschema
relname | mytable
seq_scan | 196
seq_tup_read | 2755962642
idx_scan | 4362625959
idx_tup_fetch | 3579773932
n_tup_ins | 93821564
n_tup_upd | 645310
n_tup_del | 0
n_tup_hot_upd | 21288
n_live_tup | 31153237
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum | 2021-04-24 05:06:56.481349+00
last_autovacuum | 2021-03-04 00:27:26.705849+00
last_analyze | 2021-04-24 05:07:37.589756+00
last_autoanalyze | 2021-03-04 08:55:32.673118+00
vacuum_count | 69
autovacuum_count | 1
analyze_count | 69
autoanalyze_count | 55
db=> select * from pg_stat_all_indexes where relname = 'mytable' and indexrelname = 'mytable_pkey' order by idx_tup_fetch desc nulls last;
-[ RECORD 1 ]-+-----------------------
relid | 3029143926
indexrelid | 3029143974
schemaname | myschema
relname | mytable
indexrelname | mytable_pkey
idx_scan | 3806451145
idx_tup_read | 97277555
idx_tup_fetch | 61522Thanks.
