Analyzing index usage with pg_stat in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the pg_stat_user_indexes view in PostgreSQL?

It shows statistics about index usage for user-created indexes, helping you understand how often indexes are scanned or used.

beginner

Which column in pg_stat_user_indexes tells you how many times an index was scanned?

The idx_scan column shows the number of index scans performed using that index.

intermediate

How can you identify unused indexes using pg_stat_user_indexes?

Indexes with idx_scan value of zero have never been used for scanning and might be candidates for removal.

intermediate

What does a high idx_tup_fetch value indicate in index statistics?

It indicates many table rows were fetched through the index scans, showing the index is actively helping queries.

beginner

Why is it important to analyze index usage with pg_stat_user_indexes?

Because unused or rarely used indexes add overhead to writes and storage without benefit, so analyzing helps optimize performance.

Which PostgreSQL view provides statistics about user index usage?

A pg_stat_activity

B pg_stat_user_indexes

C pg_indexes

D pg_locks

pg_stat_user_indexes tracks usage stats for user-created indexes.

If an index has idx_scan = 0, what does it mean?

A The index is heavily used

B The index is being rebuilt

C The index is corrupted

D The index has never been scanned

An idx_scan of zero means no queries have used that index for scanning.

What does a high idx_tup_fetch value indicate?

A Index is locked

B Many index scans failed

C Many rows fetched via index scans

D Index is unused

idx_tup_fetch counts rows fetched through index scans, showing active use.

Why might you want to drop an index with zero scans?

A It wastes storage and slows writes

B It slows down SELECT queries

C It causes deadlocks

D It prevents backups

Unused indexes consume space and slow down data modifications without benefit.

Which command shows index usage statistics in PostgreSQL?

A SELECT * FROM pg_stat_user_indexes;

B SHOW INDEXES;

C EXPLAIN ANALYZE;

D SELECT * FROM pg_locks;

pg_stat_user_indexes is the system view for index usage stats.

Explain how you can use pg_stat_user_indexes to find indexes that might be removed to improve performance.

Look for indexes with no scans and consider their cost.

Describe what information pg_stat_user_indexes provides about index usage and why it is useful.

Think about how index stats relate to query performance.