Synthetic Industry

Troubleshooting guide · updated 2026-10-11

Before you drop an "unused" index: what usage counters can and cannot tell you

Index usage counters reset, miss monthly jobs, cover only one server and say nothing about constraints. How to judge the evidence period and drop safely.

A zero in the counter is not a verdict

Every index costs something: disk space, time on each insert and update, and work for maintenance. So dropping indexes nobody uses is a sensible clean-up. The risk is the word nobody. The evidence for it is a counter, and a counter only shows what happened since it was last reset, on the server you read it from.

PostgreSQL records idx_scan, the number of scans started on an index, in its index statistics view, and from PostgreSQL 16 also last_idx_scan, the time of the last one; on an older version you have the count and the reset dates only. The manual says a clean shutdown keeps the statistics, but all counters are reset when the server starts after an unclean shutdown, which includes a crash, starting from a base backup and point-in-time recovery. So after any of those events, a low count may mean the counters are young, not that the index is idle. Our reading of the manual is that each server counts the scans it runs itself, so queries that go to a read replica or a reporting database do not appear in the primary's counters.

  • Find out when the statistics were last reset and whether the server crashed or was restored since; pg_stat_database.stats_reset alone does not show that, so ask about crashes and restores as well, and check when the server last started.
  • Read the counters on every server that receives queries, the primary and each replica or reporting host, and call an index unused only when all of them show no use.

Jobs that run rarely

An index that supports a weekly report, a monthly close or a yearly archive will look unused over a short window. MySQL's own manual says its unused-index view is most useful once the server has been up and processing long enough for the workload to be representative, and otherwise an index's presence in it may not be meaningful. A monthly close is the kind of job that needs a full month of uptime. The view is built on the Performance Schema, whose tables live in memory and are discarded at server shutdown, so on MySQL the evidence starts again after every restart.

The answer is not to wait forever but to name the cycle. Ask which jobs run weekly, monthly or yearly, list the statements they issue, and check each against the index list before it goes on a drop list. Where the evidence period is shorter than the longest cycle, mark the index watch, not drop.

  • List scheduled jobs and reports with their frequency.
  • Keep statement statistics or the slow query log running across at least one full cycle.
  • On MySQL, note the last restart date and count the evidence period from it.

Counters cannot see constraints, and counts can mislead

A primary key or unique index may show few scans and still be doing essential work: it enforces uniqueness on every insert whether or not any query reads it. A foreign key may rely on an index on the referencing column to keep deletes on the parent table quick. Neither shows up as a scan count. Do not put an index that enforces a constraint on a drop list; DROP INDEX CONCURRENTLY in PostgreSQL does not allow CASCADE, so an index backing a constraint cannot be dropped that way.

Counts can also overstate use. PostgreSQL notes that each index search increments idx_scan, so a statement with an IN list can add many counts for one execution, and the optimizer itself touches indexes to check values outside the recorded range. Do not rank indexes by count alone.

  • Cross-check every candidate against constraints and foreign keys.
  • Look at the statements, not just the totals.

Dropping and rebuilding safely

Save the definition of every index before you drop it, so it can be recreated. In PostgreSQL a plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table, which blocks all access until it finishes; DROP INDEX CONCURRENTLY waits for conflicting transactions instead, but cannot run inside a transaction block. Recreating with CREATE INDEX CONCURRENTLY avoids blocking writes but takes longer and, if it fails, can leave an invalid index that must be dropped and retried.

The index review outcome follows this method: findings ranked with the evidence period stated, up to five approved changes measured on a staging copy with their write cost, and an undo script for each. It does not touch production. Nothing should be dropped there without a restore point you have tested and a window your engineer chooses.

  • Keep the saved definitions with the change record.
  • After dropping, watch the slow query log for new slow statements.

Sources and limits

  • PostgreSQL 18: The cumulative statistics system Checked 2026-10-11.
    • pg_stat_all_indexes reports idx_scan, last_idx_scan and idx_tup_read per index.
    • When a server, including a physical replica, shuts down cleanly its statistics are saved and kept across the restart; starting after an unclean shutdown such as a crash, from a base backup, or after point-in-time recovery resets all statistics counters.
    • Each index search increments idx_scan, so it can greatly exceed the number of executor node executions, for example with IN lists.
  • PostgreSQL 16 release notes: monitoring Checked 2026-10-11.
    • PostgreSQL 16 added statistics on the last sequential and index scans, shown in the pg_stat_*_tables and pg_stat_*_indexes views.
  • MySQL 8.4: sys.schema_unused_indexes Checked 2026-10-11.
    • The view lists indexes for which there are no events, and is most useful when the server has been up and processing long enough that its workload is representative; otherwise an index's presence in it may not be meaningful.
  • MySQL 8.4: The sys schema Checked 2026-10-11.
    • The sys schema is a set of objects, including views, that helps interpret data collected by the Performance Schema.
  • MySQL 8.4: The Performance Schema Checked 2026-10-11.
    • Performance Schema tables are in-memory tables that use no persistent on-disk storage; their contents are repopulated beginning at server startup and discarded at server shutdown.
  • PostgreSQL 18: DROP INDEX Checked 2026-10-11.
    • DROP INDEX CONCURRENTLY avoids blocking reads and writes but cannot run in a transaction block, cannot use CASCADE, and a plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table.
  • PostgreSQL 18: CREATE INDEX Checked 2026-10-11.
    • CREATE INDEX CONCURRENTLY builds without blocking writes, takes longer, and on failure can leave an invalid index that should be dropped and retried.