Message board

How are indexes perfroming on your database?  This comes up quite often in discussion with people trying to understand postgres and perfromance tuning.

Lucky for us, we have access to  pg_stat_user_index which contains metrics about index and usage.  For this example, we will focus on twp columns. idx_tup_read and idx_tup_fetch.

At a high level ...

• idx_tup_read, represents the number of index entries an index scan reads.  This includes values that will be eventually fetched based on the criteria as well as those that do not meet the seacrh criteria.
• idx_tup_fetch tells us the number of rows fetched after scanning the index. In other words it tells us how many of the idx_tup_read were actually used to fetch the rows.

Very similar numbers indicate good use of index with queries.

Large discrepncies should raise concerns.  You typically want to see high number of fetch in comparison to reads.

Higher reads would indicate inefficient use of and index. Most likely realted to a poorly written query.

The following query can be used to see what is happening in your database in regards to the indexes.

This query is somewhat subjective. You can adjust the case statements to reflect what you believe are actionable values. 

WITH t1
   AS ( SELECT *, round (( ( idx_tup_fetch :: NUMERIC / idx_tup_read :: NUMERIC ) * 100) :: NUMERIC, 2) AS fetch_percentage
        FROM   pg_stat_user_indexes
        WHERE  idx_tup_fetch > 0
      )
SELECT *,
     CASE
       WHEN t1.fetch_percentage = 100 THEN 'Excellent'
       WHEN t1.fetch_percentage >= 95 AND t1.fetch_percentage < 100 THEN 'Very good'
       WHEN t1.fetch_percentage >= 85 AND t1.fetch_percentage < 95 THEN 'Good'
       WHEN t1.fetch_percentage >= 70 AND t1.fetch_percentage < 85 THEN 'Ok'
       WHEN t1.fetch_percentage >= 50 AND t1.fetch_percentage < 70 THEN 'Look into this'
       WHEN t1.fetch_percentage >= 45 AND t1.fetch_percentage < 50 THEN 'Looking bad'
       WHEN t1.fetch_percentage < 45 THEN 'Find a scapegoat'
     END AS status
FROM   t1
ORDER  BY t1.relname, t1.fetch_percentage DESC;

Here is a sample output

relid  | indexrelid | schemaname |      relname       |      indexrelname       |  idx_scan  | idx_tup_read | idx_tup_fetch | fetch_percentage |      status       
--------+------------+------------+--------------------+-------------------------+------------+--------------+---------------+------------------+------------------
 16392 |      17469 | public     | ad_files           | ad_files_idx1           |    5883948 |   2132880490 |    2128922345 |            99.81 | Very good
 16392 |      39075 | public     | ad_files           | ad_files_pk             |       2471 |         2463 |          2432 |            98.74 | Very good
 16400 |      17472 | public     | ad_payments        | ad_payments_idx3        |        558 |            3 |             3 |           100.00 | Excellent
 16400 |      17470 | public     | ad_payments        | ad_payment_idx1         |    7449157 |      8243884 |       8188244 |            99.33 | Very good
 16412 |      17473 | public     | ad_replies         | ad_replies_idx1         |    1691298 |      1164035 |       1070166 |            91.94 | Good
 16443 |      39072 | public     | ads                | ads_idx4                |      59929 |      8404695 |       8395907 |            99.90 | Very good
 16443 |      39061 | public     | ads                | ads_idx2                |      79364 |        79365 |         75822 |            95.54 | Very good
 16443 |      39058 | public     | ads                | ads_pk                  |    4170798 |      4591120 |       4379907 |            95.40 | Very good
 16443 |      39060 | public     | ads                | ads_idx1                |     179094 |       286107 |        161659 |            56.50 | Look into this
 16469 |      18109 | public     | albumphotos        | albumphotos_idx1        |    2284634 |      2219254 |       2218448 |            99.96 | Very good
195764 |     195771 | public     | alerts             | alerts_pkey             |       3103 |           16 |            16 |           100.00 | Excellent
195657 |     195668 | public     | article            | article_idx1            |      48660 |       279244 |        278898 |            99.88 | Very good
195669 |     195679 | public     | articlecomments    | articlecomments_idx2    |      27638 |       185356 |        185356 |           100.00 | Excellent
195669 |     195678 | public     | articlecomments    | articlecomments_idx1    |     262710 |       324395 |         10881 |             3.35 | Find a scapegoat
195689 |     195692 | public     | articlesubscribe   | articlesubscribe_pkey   |       1881 |           30 |            30 |           100.00 | Excellent