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

  1. 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
  2. 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".)

  • When the system is under heavy load, does that load come from a massive number of queries or from DML statements? – Gerard H. Pille Mar 21 '20 at 17:26
  • Mostly from massive number of queries. I think around 1-3% of DML statements. – Mikko Rantalainen Mar 22 '20 at 15:30
  • We're seeing some single field on single row updates taking very long time (something like it normally takes less than 0.1 ms and under heavy load randomly taking 20000+ ms). – Mikko Rantalainen Mar 22 '20 at 15:31
  • I'm still trying to figure out if the SSD RAID is being overwhelmed by the IO or if there's some kind of logical problem. – Mikko Rantalainen Mar 22 '20 at 15:33
  • Massive numbers of queries makes me think of a website, not an OLTP system with a limited number of users in-house. Where do these queries originate from? – Gerard H. Pille Mar 22 '20 at 16:47

Whenever an index is used, that counts as an index scan. There is no separate counter for full index scans. You could compare idx_scan to idx_tup_fetch and see how any rows are returned from the index per scan on average. But what is the point? For the most part, it fetches the number of rows it needs to in order to do the job you assigned it. Maybe you are missing an index that could do the job better, but looking at this stat will not tell that that is the case, nor tell you what hypothetical index that might be.

I do a lot of performance investigation and almost never look at those values, unless I'm trying to tune/debug autovac. EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, auto_explain are the right tools for the job.

  • Thanks for quick response. I think I'll keep monitoring idx_tup_fetch instead to get overall image on index usage. I believe that we are exceeding the available IO performance with the current storage devices and new hardware is already on its way. – Mikko Rantalainen Mar 21 '20 at 15:14
  • By the way, how about seq_scan? Is it possible to do partial sequential scan or does that always refer to scanning through whole table? Considering how idx_scan works I would guess that if I use LIMIT in query the seq_scan would be increased even if only LIMIT rows were read. – Mikko Rantalainen Mar 21 '20 at 15:16
  • 1
    LIMIT without ORDER BY is the only thing I can think of that would do a partial seq scan. Experimentally, it does cause seq_scan to increment. – jjanes Mar 21 '20 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.