Analyzing index usage with pg_stat in PostgreSQL

Introduction

Indexes help databases find data faster. Checking index usage shows which indexes are helping and which are not.

You want to speed up slow database queries by using indexes.

You want to find unused indexes to clean up and save space.

You want to understand how often each index is used in your database.

You want to monitor database performance and optimize it.

You want to learn which queries benefit from indexes.

Syntax

PostgreSQL

SELECT * FROM pg_stat_user_indexes WHERE schemaname = 'public';

pg_stat_user_indexes shows statistics about index usage for user tables.

You can filter by schema name to focus on your tables.

Examples

Shows index names and how many times each index was scanned (used).

PostgreSQL

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

Shows table name, index name, number of index scans, tuples read and fetched using the index.

PostgreSQL

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

Finds indexes that have never been used (zero scans).

PostgreSQL

SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

Sample Program

This query lists the top 5 most used indexes in the public schema by number of scans.

PostgreSQL

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE schemaname = 'public' ORDER BY idx_scan DESC LIMIT 5;

Important Notes

Index usage stats reset when the database restarts.

High idx_scan means the index is used often and likely helpful.

Indexes with idx_scan = 0 might be unnecessary and can be removed.

Summary

Use pg_stat_user_indexes to see how often indexes are used.

Focus on idx_scan column to understand index usage frequency.

Remove unused indexes to improve database performance and save space.