PostgresqlHow-ToBeginner · 4 min read

How to Check Index Usage in PostgreSQL: Simple Guide

To check index usage in PostgreSQL, use the EXPLAIN command before your query to see if indexes are used in the query plan. Additionally, you can query the pg_stat_user_indexes system view to see statistics about index scans and usage.

📐

Syntax

The main ways to check index usage in PostgreSQL are:

EXPLAIN [ANALYZE] your_query; - shows the query plan and whether indexes are used.
SELECT * FROM pg_stat_user_indexes WHERE relname = 'your_table'; - shows index usage statistics for a table.

EXPLAIN shows how PostgreSQL plans to execute a query, including index scans. Adding ANALYZE runs the query and shows actual runtime details.

pg_stat_user_indexes tracks how often indexes are scanned and can help identify unused indexes.

sql

EXPLAIN SELECT * FROM your_table WHERE your_column = 'value';

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'your_table';

💻

Example

This example shows how to check if an index is used when querying a table named employees by filtering on the department column.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT
);

CREATE INDEX idx_department ON employees(department);

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'employees';

Output

Seq Scan on employees (cost=0.00..35.50 rows=5 width=64) (actual time=0.010..0.012 rows=0 loops=1) Planning Time: 0.123 ms Execution Time: 0.020 ms indexrelname | idx_scan --------------+---------- idx_department | 1 (1 row)

⚠️

Common Pitfalls

Common mistakes when checking index usage include:

Not using EXPLAIN ANALYZE, which shows actual usage instead of just the plan.
Forgetting to check pg_stat_user_indexes after running queries, so index scan counts remain zero.
Assuming an index is used just because it exists; PostgreSQL may choose a sequential scan if it thinks it's faster.

Always run queries multiple times and check statistics to confirm index usage.

sql

/* Wrong: Only EXPLAIN without ANALYZE shows estimated plan */
EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

/* Right: Use EXPLAIN ANALYZE to see actual usage */
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

📊

Quick Reference

Command	Purpose
EXPLAIN your_query;	Show estimated query plan including index usage
EXPLAIN ANALYZE your_query;	Show actual query execution with index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'table_name';	Show index scan counts for a table
CREATE INDEX index_name ON table(column);	Create an index to improve query speed

✅

Key Takeaways

Use EXPLAIN ANALYZE to see if PostgreSQL actually uses indexes during query execution.

Check pg_stat_user_indexes to monitor how often indexes are scanned over time.

Indexes exist but may not be used if PostgreSQL finds a sequential scan faster.

Run queries multiple times to update index usage statistics before checking.

Creating indexes on frequently filtered columns helps improve query performance.