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.
1. The db is up since 80 days so I assume these are cumulative stats
since last startup?
2. 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)
1. 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 | 61522
Thanks.
