Skip to main content

PG.L7.INDEX_REDUNDANT — An index whose work another index already does

  • Category: performance
  • Level: 7
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18 and newer

The cost is real and paid on every write​

A B-tree index on (customer_id) beside one on (customer_id, placed_at) answers nothing the second cannot. It is still maintained: every insert writes it, every update touching its column writes it, it occupies its own pages in cache, and it is one more relation for vacuum to walk. The read side gains nothing at all.

Flagged​

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('customer_id');
    $table->timestamp('placed_at');
    $table->index(['customer_id']);
    $table->index(['customer_id', 'placed_at']);
});

Preferred​

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('customer_id');
    $table->timestamp('placed_at');
    $table->index(['customer_id', 'placed_at']);
});

This rule is mostly exclusions, and that is the design​

Its advice is to drop something. The expensive failure is therefore a false positive, not a miss: dropping the wrong index turns a lookup into a sequential scan on production, and the advice came with this tool's name on it.

So a comparison is only ever made between indexes the reading already judged comparable. Each exclusion below is a named catalog skip, not a silent omission — a schema full of partial indexes is told they were not compared, rather than handed a clean report:

  • A partial index indexes a subset of the rows, so it is not the same index at all.
  • An expression index indexes lower(email), which is not email, however similar the two lines look.
  • A non-default operator class answers a different question: text_pattern_ops serves LIKE 'foo%' and the default class does not.
  • Any method other than b-tree. A GIN index over an array is not a slower B-tree; the redundancy question is only meaningful inside one method.
  • An index PostgreSQL marked invalid — the corpse a canceled CREATE INDEX CONCURRENTLY leaves behind.

Four more things that stop a pair from being reported​

A unique index or the primary key is never the victim. Dropping either changes what the schema allows, not what it costs — a different conversation from a performance finding. It may still be the index that covers another: a unique key on (a, b) serves lookups on (a).

An index carrying an INCLUDE payload is never the victim either. Measured: (a) INCLUDE (b) and (a) report the same key columns, because indnkeyatts deliberately excludes the payload. Without that flag this rule would recommend dropping the more useful of the two.

It has to be a strict left prefix. A B-tree on (a, b) serves (a) and does not serve (b). Order is the fact, not a detail.

Two identical indexes produce one finding, not two. Neither is the obvious victim, so the tie is broken by name — arbitrary, but arbitrary deterministically, so the same schema produces the same report every run. Both names appear in the message and you pick.

Removing it​

DROP INDEX CONCURRENTLY orders_customer_idx;

CONCURRENTLY takes no lock that blocks reads or writes. SQLens never runs it — the package writes nothing to a database; the statement is here so a human can run it in a window they chose.

Sources​