We have a busy PostgreSQL 9.5 server with OLTP-like traffic where pg_stat_all_tables.idx_scan
values go up with considerate rate while we're having performance problems.
Does that (idx_scan
increasing) actually mean that
- the system is actually running lots of full scans through indexes (meaning, reading the whole index from the disk if not already in the cache), or
- the system is actually getting some only some tuples ("rows") from those indexes (that is, using the index as intended)?
If the option 1 is true, how to figure out how to get system into state 2? Do I need some additional indexes or are some of my queries bad? All queries get nice performance while the system is under low load but during very high load pretty much any query can get unexpectedly poor execution time.
(The documentation at https://www.postgresql.org/docs/current/monitoring-stats.html only says "Number of index scans initiated on this table".)