Search Postgresql Archives

postgres index usage count too high

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux