인덱스 사용 모니터링

Indexes are powerful tools for speeding up queries, but they aren't free. They consume disk space and add overhead to data modifications (INSERT, UPDATE, DELETE).

Monitoring index usage helps us understand if our indexes are actually working for us or just taking up space.

PostgreSQL provides several system views to monitor database activity. For index usage, the pg_stat_user_indexes view is your best friend.

This view tracks statistics for indexes on user-defined tables, giving you insights into how often each index is being scanned.

When you query pg_stat_user_indexes, look out for these columns:

• idx_scan: The number of times the index has been scanned.
• idx_tup_read: The number of index entries returned by scans.
• idx_tup_fetch: The number of live table rows fetched through the index.

These tell you how frequently and effectively an index is being used.

The easiest win in index optimization is identifying indexes that are never used. An index with idx_scan = 0 is a strong candidate for removal.

Removing unused indexes can reduce disk space, speed up writes, and simplify database maintenance.

Let's run a query to find all indexes that have never been scanned since the last statistics reset. Try running this example:

An index might be used (idx_scan > 0) but still be 'underperforming' if it's not chosen by the query planner when it should be, or if it's leading to many sequential scans on the table itself.

To spot these, we need to compare index usage with overall table access patterns.

The pg_stat_user_tables view provides statistics at the table level. Key columns here are:

• seq_scan: Number of sequential scans initiated on this table.
• idx_scan: Number of index scans initiated on this table.

A high seq_scan count on a large table often indicates a missing or ineffective index.

By comparing seq_scan and idx_scan from pg_stat_user_tables, we can identify tables that are frequently being scanned sequentially, even if indexes exist.

A high ratio of sequential scans to index scans on a table suggests potential indexing issues or queries that aren't utilizing available indexes.

This query calculates the percentage of sequential scans for each table. Tables with a high percentage of seq_scan might need attention.

Great job! In this lesson, you learned how to monitor index effectiveness using PostgreSQL's system views.

• pg_stat_user_indexes helps find unused indexes (idx_scan = 0).
• pg_stat_user_tables reveals the balance between sequential and index scans on tables.
• By combining these, you can identify indexes that are candidates for removal or further investigation.

In the next lesson, we'll dive into reindexing and maintaining index health!