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, and a comparison that goes unmade is a named catalog skip rather than a silent omission. The skip names exactly that: an index that was compared inside its own universe is not reported as skipped, even when nothing in that universe resembled it.

The skip reports under one of two ids, because the two need opposite reactions. A boundary this rule draws on purpose is AUDIT.CATALOG.NOT_COMPARED, reported as not_applicable. Part of an index that did not arrive in a form the rule can reason about is AUDIT.CATALOG.UNREAD.NOT_UNDERSTOOD, reported as undetermined, because there the question really is open.

  • A partial index, against anything except another index carrying the same condition. Where SQLens can read the shape of the condition, the skip names it — it selects the rows where consumed_at is null — and where it cannot, it prints the predicate as the server spelled it and says no more.

    The one comparison that IS made is between two partial indexes whose conditions normalize to the same thing. They cover exactly the same rows, so the predicate cancels and the columns decide — arithmetic, not a heuristic. It survives a difference in spelling: over a varchar column, WHERE state IN ('open') and WHERE state = ANY (ARRAY['open']) are printed differently by the server and are the same condition.

    The shared condition cancels the condition and nothing else. Two partial indexes under one condition are compared only when no other difference separates them: both b-tree, both under the default operator class, both valid, and neither carrying an expression that did not arrive. The operator class is the one worth naming, because the server prints a key position without it — (email text_pattern_ops) and (email) both read as email, and they are two different indexes.

    The skip for a partial index appears in three cases: the condition could not be read, something besides the condition keeps the index out of its group, or its table carries a partial index under a different condition, which is the pair the implication question below would have to decide. A partial index alone under the only condition on its table was compared against everything a comparison can mean, and is not reported.

    ⚠️ Two cases stay out, and both look like the obvious next step. A different condition is an implication question — does WHERE a IS NULL cover WHERE a IS NULL AND b = 1? — and implication is not built. A partial index against an unconditional one is true as a statement about rows and wrong as advice: the partial index can be orders of magnitude smaller, and somebody kept it that way on purpose, so dropping it is a loss rather than a tidy-up.

  • An expression that does not arrive whole. lower(email) is compared against lower(email) and never against email, because the reading carries the expression itself. What stays out is an expression the reading could not carry: on MySQL, a functional index's expression is recorded beside its key columns rather than among them. A comma or a semicolon inside an expression is not a reason: COALESCE(heading, '') arrives as the one key position it is.

  • Any method other than b-tree, against a b-tree. A GIN index over an array is not a slower B-tree. It is compared against the indexes of its own method and operator class instead, where only an identical column list means the same index twice. Alone there, it has nothing to be a duplicate of and is not reported as skipped.

  • A non-default operator class on a b-tree answers a different question: text_pattern_ops serves LIKE 'foo%' and the default class does not, so such an index is compared against nothing and is reported as not compared. For another method the class is part of the universe instead — a GIN index carrying jsonb_path_ops is compared only against GIN indexes carrying jsonb_path_ops.

  • 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​