skip to content

In PostgreSQL, which view shows how often each index on the orders table has been used, and how do you read it?

level: juniorimportance: should knowfreq 50%

answer

  1. cumulative statistics, one row per index
  2. a scan counter and a timestamp
  3. entries read versus rows fetched
  4. since the last reset, on this server

basics

~20 s

pg_stat_user_indexes has one row per index: idx_scan counts index scans, last_idx_scan dates the latest, and idx_tup_read and idx_tup_fetch count entries read and table rows fetched. An idx_scan stuck at 0 since the last reset marks a candidate for removal.

solid answer

~40 s

`pg_stat_user_indexes` (the user-table part of `pg_stat_all_indexes`) has one row per index with `relname`, `indexrelname`, `idx_scan`, `last_idx_scan`, `idx_tup_read` and `idx_tup_fetch`. Sort by `idx_scan` and add `pg_relation_size(indexrelid)` to find big indexes that are rarely used. Read the counters with care: they accumulate since the last reset, and a crash, an immediate shutdown, a point-in-time recovery or a start from a base backup resets them all. They are kept per server, so scans served by a read replica never show on the primary. And an index backing a primary key or unique constraint does its job at `idx_scan` 0, because checking uniqueness on insert is not counted as a scan.

code

sql · 10 lines
sql
SELECT s.indexrelname,
       s.idx_scan,
       s.last_idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       i.indisunique
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i ON i.indexrelid = s.indexrelid
WHERE s.schemaname = 'marketplace'
  AND s.relname = 'orders'
ORDER BY s.idx_scan, pg_relation_size(s.indexrelid) DESC;

go deeper

for a junior

Remember pg_stat_user_indexes and its idx_scan column: it shows how many times each index has been scanned since the statistics were last reset.

for a middle

Explain all four counters, how idx_tup_read differs from idx_tup_fetch, and why idx_scan counts index searches rather than query executions.

for a senior

Show the traps before acting: resets after crashes and PITR, per-server counters on replicas, constraint-backing indexes at zero scans, and choosing a window that covers rare reports.

for a principal

Turn usage data into a repeatable review: collect counters from every node over a business cycle, combine them with size and write load, and own the decision process for removing indexes.

## The view PostgreSQL keeps **cumulative statistics**: counters that each server process updates in shared memory and that grow until someone resets them. For indexes they are exposed in `pg_stat_all_indexes`; `pg_stat_user_indexes` is the same view limited to indexes on user tables, and `pg_stat_sys_indexes` covers the system catalogs. Each row describes one index: | Column | Meaning | |---|---| | `relid`, `indexrelid` | OIDs of the table and of the index | | `schemaname`, `relname`, `indexrelname` | names of the schema, the table and the index | | `idx_scan` | number of index scans initiated on this index | | `last_idx_scan` | time of the last scan, taken from the most recent transaction stop time | | `idx_tup_read` | index entries returned by scans on this index | | `idx_tup_fetch` | live table rows fetched by simple index scans using this index | `idx_tup_read` and `idx_tup_fetch` differ even without bitmap scans: the second is smaller when entries point at dead or not-yet-committed rows, or when an index-only scan avoids the table altogether. ## A first usage query The example below lists every index on `marketplace.orders` with its scan count, last use and size, least used first. Size matters as much as the count: a 30 GB index with three scans a month is a very different problem from a 20 MB one. The block-level I/O for the same indexes lives in a sibling view, `pg_statio_user_indexes` (`idx_blks_read`, `idx_blks_hit`). ## Why the numbers can mislead - **Resets.** The counters cover the time since the last reset. `pg_stat_reset()` clears the current database; `pg_stat_reset_single_table_counters(oid)` clears one table or index. A clean shutdown saves the statistics, but starting after an **unclean** shutdown (an immediate shutdown, a crash), from a base backup or through point-in-time recovery resets every counter. - **Per server.** Each server keeps its own counters, a streaming replica included. If reporting queries run on a replica, the indexes they use can show `idx_scan = 0` on the primary. - **Enforcement is not a scan.** An index that backs a `PRIMARY KEY` or `UNIQUE` constraint checks every insert, but that check does not increment `idx_scan`. Such an index can look unused and still be essential. - **Searches, not executions.** `idx_scan` counts index **searches**. A query with `= ANY(array)`, an `OR` list the optimizer turns into an array, or a B-tree skip scan in 18 can perform many searches in one execution, so the count can far exceed the number of queries. - **Bitmap scans.** A bitmap scan increments `idx_tup_read` for each index it uses but adds its row fetches to the table-level counter, not to the index's `idx_tup_fetch`. An index used only through bitmaps can show `idx_tup_fetch = 0`. ## Reading a typical result Suppose the query returns these patterns for `orders`: - `orders_pkey` with `idx_scan` in the millions: the primary-key lookups the application makes on every order page. Nothing to do. - `orders_external_ref_uidx` with `idx_scan = 0` and `indisunique = true`: it enforces a uniqueness rule on every insert, so zero scans says nothing against it. - `orders_status_idx` with `idx_scan = 0`, `last_idx_scan` NULL and 35 GB on disk: no scan has been recorded since the counters began. Check the reset time, the replicas and the monthly jobs before calling it dead weight. - `orders_seller_created_idx` with scans rising between two samples and `last_idx_scan` minutes ago: in active use. `last_idx_scan` is NULL when no scan has been recorded, and it also exposes an index with a large lifetime `idx_scan` whose last use was months ago. `pg_stat_database.stats_reset` shows when counters in the database were last reset by one of the reset functions. ## Turning numbers into a decision 1. Note when the counters were last reset and make sure the window covers a full business cycle, including month-end reports. 2. Check every server that serves reads, not just the primary. 3. Exclude indexes that back constraints, and remember the planner may need an index for rare but critical queries. 4. Only then weigh size and write cost against use; whether a seldom-used or overlapping index should go is a general indexing judgement covered elsewhere. These counters say how often an index was used, not whether it is healthy. Its size and bloat are measured with other tools, and table-level counters such as `seq_scan` in `pg_stat_user_tables` belong to the statistics-views topic.

  • Why might an index show idx_scan = 0 on the primary although queries use it every minute?
    Statistics are per server. If those queries are routed to a streaming replica, the replica counts the scans in its own `pg_stat_user_indexes` and the primary never sees them. Before dropping an index, read the counters on every server that serves reads, and check when each server's counters were last reset.
  • What is the difference between idx_tup_read and idx_tup_fetch?
    `idx_tup_read` counts index entries returned by scans of the index; `idx_tup_fetch` counts live table rows fetched through it by simple index scans. Fetch is lower when entries point at dead rows or when index-only scans skip the table, and bitmap scans add their fetches to the table's counter rather than the index's.

  • pg_stat_user_tables shows how often each individual index is used.
  • An index with idx_scan = 0 on the primary is never used anywhere.
  • Statistics counters survive a crash because they are WAL-logged.
  • A unique index with zero scans is safe to drop.
  • idx_scan counts how many queries used the index, one per execution.