Observation and diagnostics · Intermediate

Reading PostgreSQL index-usage statistics

Interpret index counters with their windows and units, evaluating retrieval, writes, constraints and infrequent operational work.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Do not drop an index from a zero count alone
  2. Check the observation window and constraint role
  3. Separate scans, tuple counts and size
  4. Compare representative plans and maintenance costs
  5. Ask Logeia about this articleCheck your understanding

Do not drop an index from a zero count alone

PostgreSQL index-usage statistics help identify which indexes participate in retrieval, but a low idx_scan value is a candidate for investigation rather than proof that an index is unnecessary. An infrequent month-end report or incident-response query can depend on it. An index supporting a primary key or unique constraint also has an integrity purpose beyond its ordinary scan count. Establish the database, table, definition, constraint role and observation period before connecting the number to the workload. The useful decision concerns the index’s complete contribution, not whether one counter looks small.

Check the observation window and constraint role

An index can show zero scans during a week of ordinary traffic yet be essential for a monthly batch. Conversely, frequent use does not prove good selectivity or low cost. These are workload examples rather than measurements from a real installation. Check resets, crash history, monitoring start time and the distribution of read traffic across replicas. One observation on the primary does not represent queries executed elsewhere. Choose a window that includes the application’s actual operating cycle, and preserve that window in the evidence supporting any proposed change.

sql
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_relation_size(indexrelid) AS index_bytes
FROM pg_stat_user_indexes ORDER BY idx_scan, index_bytes DESC;

Separate scans, tuple counts and size

Idx_scan, idx_tup_read and idx_tup_fetch count different things: scans, index entries and fetched heap tuples are not interchangeable units. Bitmap and index-only execution paths further prevent a universal one-to-one interpretation. Relation size can be added with pg_relation_size, but neither large size proves waste nor small size eliminates maintenance cost. Evaluate update work, WAL generation and cache occupancy alongside retrieval benefits. Compressing these considerations into one apparent hit rate can obscure both constraint enforcement and differences in access methods. State the exact counter and unit whenever comparing before and after a change.

Compare representative plans and maintenance costs

Inspect representative execution plans together with request frequency and latency to develop alternatives for the index definition. EXPLAIN ANALYZE executes the statement, so use an appropriate test environment for modifying or expensive work. Before removing an index in production, check dependencies, constraint enforcement, recreation time, I/O, locking and recovery options. Statistics supply evidence, not permission for an automatic DROP rule based on a zero counter. After adopting a change, compare reading and writing under matched work and observation periods. Confirm that an apparent reduction in index maintenance did not simply move the cost into slower queries or a disrupted operational task.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 12

Choose a learning path