Analyzing index usage with pg_stat in PostgreSQL - Step-by-Step Execution

Concept Flow - Analyzing index usage with pg_stat

Query pg_stat_user_indexes

↓

Check index scan counts

↓

Identify heavily used indexes

↓

Identify unused indexes

↓

Decide to keep or drop indexes

This flow shows how to query PostgreSQL's pg_stat_user_indexes to see which indexes are used and which are not, helping decide index maintenance.

Execution Sample

PostgreSQL

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public';

This query lists index names and how many times each index was scanned in the public schema.

Execution Table

Step	Action	Query Result Sample	Explanation
1	Run query on pg_stat_user_indexes	[{"indexrelname": "users_pkey", "idx_scan": 1500}, {"indexrelname": "orders_idx", "idx_scan": 0}]	Fetch index usage stats for all indexes in public schema
2	Check idx_scan values	users_pkey: 1500, orders_idx: 0	High idx_scan means index is used; zero means unused
3	Identify unused indexes	orders_idx has idx_scan = 0	This index might be a candidate for removal
4	Decide action	Keep users_pkey, consider dropping orders_idx	Based on usage, keep or drop indexes to optimize performance
5	End	-	Analysis complete

💡 All indexes in public schema checked; usage counts analyzed to guide index maintenance decisions

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
indexrelname	N/A	["users_pkey", "orders_idx"]	["users_pkey", "orders_idx"]	["users_pkey", "orders_idx"]	["users_pkey", "orders_idx"]
idx_scan	N/A	[1500, 0]	[1500, 0]	[1500, 0]	[1500, 0]
unused_indexes	N/A	[]	[]	["orders_idx"]	["orders_idx"]

Key Moments - 2 Insights

Why does an index have idx_scan = 0 even if it exists?

An idx_scan of 0 means no queries have used that index since the last stats reset. See execution_table step 2 where orders_idx has 0 scans, indicating it might be unused.

Can an index with high idx_scan still be inefficient?

Yes, idx_scan shows usage count but not efficiency. An index might be used often but still slow queries if not well designed. This is beyond pg_stat_user_indexes and requires deeper analysis.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the idx_scan value for 'users_pkey'?

A 0

B 1500

C 500

D None

💡 Hint

Check the 'Query Result Sample' column in step 2 of execution_table

At which step do we identify unused indexes?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look for the step where idx_scan = 0 is noted for an index

If 'orders_idx' had idx_scan = 100, how would the unused_indexes variable change?

A It would include all indexes

B It would include 'orders_idx'

C It would be empty

D It would be null

💡 Hint

Refer to variable_tracker row 'unused_indexes' and how idx_scan = 0 triggers inclusion

Concept Snapshot

Use pg_stat_user_indexes to check index usage:
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE schemaname = 'public';
idx_scan shows how many times an index was used.
Indexes with idx_scan = 0 may be unused and candidates for removal.
Regularly analyze to optimize database performance.

Full Transcript

This lesson shows how to analyze index usage in PostgreSQL using the pg_stat_user_indexes view. We run a query to get index names and their scan counts. By checking idx_scan values, we identify which indexes are used frequently and which are not used at all. Unused indexes have idx_scan = 0 and might be removed to improve performance. The execution table walks through each step from querying to deciding actions. Variable tracking shows how index names and scan counts are recorded and how unused indexes are identified. Key moments clarify common confusions about idx_scan meaning and index efficiency. The quiz tests understanding of idx_scan values, step identification, and variable changes. The snapshot summarizes the key points for quick reference.