{"id":6067,"date":"2026-09-04T07:00:00","date_gmt":"2026-09-04T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6067"},"modified":"2026-09-04T10:56:39","modified_gmt":"2026-09-04T07:56:39","slug":"pos-vlepoyme-poso-ginontai-chrisi-ta-indexes-s","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-vlepoyme-poso-ginontai-chrisi-ta-indexes-s\/","title":{"rendered":"How to see how much Indexes are used in PostgreSQL"},"content":{"rendered":"

In any database, indexes are essential for the speed of SELECT<\/code> queries. However, each index we create has a cost, it takes up disk space, burdens memory and slows down the INSERT<\/code>, UPDATE<\/code> and DELETE<\/code> operations, since PostgreSQL must also update the index with every change.<\/p>\n\n\n\n

To keep our database clean and fast, we must systematically monitor which indexes are actively used and which remain inactive, burdening the system.<\/p>\n\n\n\n

The query:<\/h2>\n\n\n\n

The following query returns all the database indexes<\/strong> (Primary Keys, Unique constraints and Regular indexes), sorted by table.<\/p>\n\n\n\n

Calculates the size of each index in GB<\/strong>, the Cache Hit Ratio %<\/strong> (how much the index is served by RAM vs. disk), while also setting automatic Usage Status<\/strong>:<\/p>\n\n\n\n

SELECT \n    i.schemaname AS schema_name,\n    i.relname AS table_name,\n    i.indexrelname AS index_name,\n    \n    CASE \n        WHEN idx.indisprimary THEN 'PRIMARY KEY'\n        WHEN idx.indisunique THEN 'UNIQUE'\n        ELSE 'REGULAR'\n    END AS index_type,\n\n    i.idx_scan AS total_scans,\n    i.idx_tup_read AS rows_read,\n    i.idx_tup_fetch AS rows_fetched,\n\n    ROUND((pg_relation_size(i.indexrelid)::numeric \/ 1073741824.0), 3) AS index_size_gb,\n\n    CASE \n        WHEN (io.idx_blks_read + io.idx_blks_hit) > 0 \n        THEN ROUND((100.0 * io.idx_blks_hit \/ (io.idx_blks_read + io.idx_blks_hit))::numeric, 2)\n        ELSE 0.00 \n    END AS cache_hit_pct,\n\n    CASE \n        WHEN i.idx_scan = 0 AND NOT idx.indisprimary AND NOT idx.indisunique THEN '\u274c UNUSED'\n        WHEN i.idx_scan = 0 THEN '\u26a0\ufe0f UNUSED (PK\/Unique)'\n        WHEN i.idx_scan < 50 THEN '\u2139\ufe0f LOW USAGE'\n        ELSE '\u2705 ACTIVE'\n    END AS usage_status\n\nFROM pg_stat_user_indexes i\nJOIN pg_index idx ON idx.indexrelid = i.indexrelid\nLEFT JOIN pg_statio_user_indexes io ON io.indexrelid = i.indexrelid\nWHERE i.schemaname NOT IN ('pg_catalog', 'information_schema')\nORDER BY i.relname ASC, idx.indisprimary DESC, i.indexrelname ASC;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
What do we look at to draw a conclusion:<\/h2>\n\n\n\n
ACTIVE<\/code> (total_scans > 50<\/code>)<\/strong>: The index is used regularly by the application's queries.<\/p>\n\n\n\n
UNUSED<\/code> (total_scans = 0<\/code>)<\/strong>: Regular secondary index that has never been used<\/strong> since its creation or since the last statistics reset. If the table receives frequent INSERT<\/code>\/UPDATE<\/code>, this index can be deleted (DROP INDEX CONCURRENTLY<\/code>), as it only offers delay and takes up space.<\/p>\n\n\n\n
UNUSED (PK\/Unique)<\/code><\/strong>: Unused index but corresponds to Primary Key<\/strong> the Unique Constraint<\/strong>. We never delete it.<\/strong>, as it ensures the data integrity of the database.<\/p>\n\n\n\n
Cache Hit Ratio<\/code><\/strong>: Prices close to 100%<\/strong> means that the index fits and is read directly from RAM. Lower percentages indicate that Postgres is forced to read from disk.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n