Skip to main content

PG.L7.INDEX_UNUSED — An index nobody has read, as far as resettable counters can say

  • Category: performance
  • Level: 7
  • Confidence: deterministic
  • Downtime class: none
  • Stability: experimental — this rule does not run unless a project opts in
  • Suites: audit
  • Applies to: PostgreSQL 18 and newer

Opt in first​

'stability' => ['experimental'],

Without that line this rule reports nothing at all, and the next section explains why that is the right default rather than caution.

Why it is experimental, and it is not about maturity​

Every other rule in this package answers from the schema, and a schema does not move while you look at it. This one answers from counters — so its verdict depends on when it is asked, which is a direct tension with the third thing this package promises: the same state produces the same result.

The tier is what keeps that tension out of everybody else's run. An experimental rule is not admitted by default, so a project that has not asked for this one gets an audit whose determinism is intact. A project that has asked has knowingly accepted a time-bounded question.

The counter is easy; the window is the whole difficulty​

"This index has never been scanned" says nothing until you know how long the server has been counting.

Measured on PostgreSQL 18.4: on a cluster nobody has reset, pg_stat_database.stats_reset comes back NULL — the ordinary state. A zero count over a window that started at an unrecorded moment is not evidence; it is an absence of evidence wearing the same shape.

So there are three answers and only one is a verdict:

StateAnswer
Statistics unreadable, or the window is unknown or shorter than your minimumundetermined, reason index_usage_window_unknown
The index is not in the reading at allnothing — it was not counted, which is not "counted and never used"
Zero scans over a long enough windowa finding, quoting the count and the window's start

Flagged​

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->string('legacy_ref')->nullable();
    $table->index(['legacy_ref']);
});

Preferred​

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->string('legacy_ref')->nullable();
});

How patient it is, is your decision​

'audit' => [
    'unused_index' => [
        // Whole days the counters must have been running before a zero counts as evidence.
        'min_observation_days' => 30,
    ],
],

Thirty by default: long enough to cover a monthly report nobody runs more often, which is exactly the query most likely to be the sole reader of an index that otherwise looks dead.

Zero is meaningful and not merely permissive. It says report on whatever window the server has — a legitimate setting for somebody who knows when their statistics were last reset, and the only way to get a verdict on a cluster that has never reset them at all.

Two things the counter cannot see​

Statistics are per instance. An index read only on a replica looks unused on the primary. The finding names the instance it was read from and says so outright.

A query that runs monthly has not run yet if the window is shorter than a month. That is the whole reason the minimum exists.

What the finding does and does not print​

It quotes the scan count and the window's start. It does not print the elapsed time — that number moves between two audits of an unchanged database, and these reports are meant to be diffable. The verdict still turns on the clock once, at the threshold; that is inherent to a time-bounded question, and it is why the rule is opt-in.

Never reported​

A primary key or a unique index. "Nobody queried it" is not an argument about a constraint: that index exists to refuse a write, and it has been doing so silently the whole time.

Removing it​

DROP INDEX CONCURRENTLY orders_legacy_ref_index;

SQLens never runs it. The package writes nothing to a database.

Sources​

  • PostgreSQL 18: the statistics collector — pg_stat_user_indexes.idx_scan counts index scans and pg_stat_database.stats_reset records when the counters were last reset. The documentation is explicit that these statistics are per instance and can be reset, which is what makes the window a precondition for reading the counter at all.