Postgres Pro
Downloads
Home   >   mailing lists

postgres index usage count too high - Mailing list pgsql-general

From Ayub M
Subject postgres index usage count too high
Date
Msg-id CAOS0qEsUg2GegBnyN5tpbnKxSxxP22d07fc_fZmgfQApgnKh_Q@mail.gmail.com
Whole thread Raw
Responses Re: postgres index usage count too high  (Laurenz Albe <laurenz.albe@cybertec.at>)
List pgsql-general
Tree view

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.

pgsql-general by date:

Previous
From: Chris Stephens
Date:
Subject: Re: pgbouncer configuration
Next
From: Laurenz Albe
Date:
Subject: Re: postgres index usage count too high