TL;DR: Postgres has pg_stat_user_indexes . It tells you how many times each index was scanned. It does not tell you whether the slow query you're chasing actually used the index you added for it, or whether you're maintaining indexes the planner never picks. I built a 3-file analyzer — a query wrapper, a logs table, a dashboard — and the first time I ran it against my own production database, 20 of my 51 indexes had never been scanned. 78% of my total index disk was being maintained for nothing.

The Gap in Postgres's Stats

Open pg_stat_user_indexes right now:

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

Enter fullscreen mode

Exit fullscreen mode

You'll see one row per index:

idx_scan — how many times the index was used

idx_tup_read — tuples read via the index

idx_tup_fetch — tuples fetched from the heap after the index hit

That's it.

You won't see:

Which queries used which index

Whether the slow query you wrote a CREATE…