1

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

Improve this question
8
  • 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, 2020 at 17:26
  • Mostly from massive number of queries. I think around 1-3% of DML statements.
    – Mikko Rantalainen
    Mar 22, 2020 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, 2020 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, 2020 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, 2020 at 16:47

1 Answer 1

Reset to default
1

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.

Improve this answer
4
  • 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, 2020 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, 2020 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, 2020 at 16:22
  • Year 2022 summary: seq_scan and idx_scan are basically query counters which just report if the query hit index or the whole table at all. If you have any idea about the total query count otherwise, you can ignore those. The idx_tup_fetch and seq_tup_read are the counters you want to use and tell you how much data was actually read from the index or actual data table. You should have sensible number of tuples fetch for each query and if you see too high tuple fetch counts it means you have poor queries or missing indexes. See also: dba.stackexchange.com/a/226481/29183
    – Mikko Rantalainen
    Feb 7, 2022 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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