How to see how much Indexes are used in PostgreSQL
Certifications:
- How to see how much Indexes are used in PostgreSQL - 4 September 2026
- How to find the size of tables in a PostgreSQL database - 2 September 2026
- How to check permissions, Grants and Default Privileges in PostgreSQL - 31 August 2026
In any database, indexes are essential for the speed of SELECT queries. However, each index we create has a cost, it takes up disk space, burdens memory and slows down the INSERT, UPDATE and DELETE operations, since PostgreSQL must also update the index with every change.
To keep our database clean and fast, we must systematically monitor which indexes are actively used and which remain inactive, burdening the system.
The query:
The following query returns all the database indexes (Primary Keys, Unique constraints and Regular indexes), sorted by table.
Calculates the size of each index in GB, the Cache Hit Ratio % (how much the index is served by RAM vs. disk), while also setting automatic Usage Status:
SELECT
i.schemaname AS schema_name,
i.relname AS table_name,
i.indexrelname AS index_name,
CASE
WHEN idx.indisprimary THEN 'PRIMARY KEY'
WHEN idx.indisunique THEN 'UNIQUE'
ELSE 'REGULAR'
END AS index_type,
i.idx_scan AS total_scans,
i.idx_tup_read AS rows_read,
i.idx_tup_fetch AS rows_fetched,
ROUND((pg_relation_size(i.indexrelid)::numeric / 1073741824.0), 3) AS index_size_gb,
CASE
WHEN (io.idx_blks_read + io.idx_blks_hit) > 0
THEN ROUND((100.0 * io.idx_blks_hit / (io.idx_blks_read + io.idx_blks_hit))::numeric, 2)
ELSE 0.00
END AS cache_hit_pct,
CASE
WHEN i.idx_scan = 0 AND NOT idx.indisprimary AND NOT idx.indisunique THEN '❌ UNUSED'
WHEN i.idx_scan = 0 THEN '⚠️ UNUSED (PK/Unique)'
WHEN i.idx_scan < 50 THEN 'ℹ️ LOW USAGE'
ELSE '✅ ACTIVE'
END AS usage_status
FROM pg_stat_user_indexes i
JOIN pg_index idx ON idx.indexrelid = i.indexrelid
LEFT JOIN pg_statio_user_indexes io ON io.indexrelid = i.indexrelid
WHERE i.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY i.relname ASC, idx.indisprimary DESC, i.indexrelname ASC;
What do we look at to draw a conclusion:
ACTIVE (total_scans > 50): The index is used regularly by the application's queries.
UNUSED (total_scans = 0): Regular secondary index that has never been used since its creation or since the last statistics reset. If the table receives frequent INSERT/UPDATE, this index can be deleted (DROP INDEX CONCURRENTLY), as it only offers delay and takes up space.
UNUSED (PK/Unique): Unused index but corresponds to Primary Key ή Unique Constraint. We never delete it., as it ensures the data integrity of the database.
Cache Hit Ratio: Prices close to 100% means that the index fits and is read directly from RAM. Lower percentages indicate that Postgres is forced to read from disk.
