agent's 'missing index' fix duplicated an existing index under a different name -- pg_stat showed two identical indexes

Duplicate index under a different name - pg_stat showed two identical indexes

TL;DR: Compare index definitions, confirm the planner only needs one, and drop the duplicate concurrently. Same columns, same predicate, different name equals pure waste: double the write cost and double the storage for zero plan benefit.

agent's 'missing index' fix duplicated an existing index under a different name -- pg_stat showed two identical indexes

Steps

1. Find candidate duplicates by comparing definitions:

   SELECT indexname, indexdef FROM pg_indexes
   WHERE tablename = 'your_table'
   ORDER BY indexdef;

Expected: two rows with byte-identical indexdef strings but different indexnames.

1. Confirm neither is special: check that neither backs a constraint (primary key, unique, exclusion) and neither is INVALID:

   SELECT c.relname, i.indisunique, i.indisprimary, i.indisvalid
   FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
   WHERE i.indrelid = 'your_table'::regclass;

Expected: both are plain non-unique valid indexes - safe to remove one.

1. Check scan counts to pick the keeper (keep the older or more-scanned one; it does not matter functionally):

   SELECT indexrelid::regclass, idx_scan FROM pg_stat_user_indexes
   WHERE relid = 'your_table'::regclass;

Expected: scans split arbitrarily between the twins or sit entirely on one.

1. Drop the duplicate:

   DROP INDEX CONCURRENTLY duplicate_index_name;

Expected: succeeds; storage and write overhead drop immediately.

1. Re-run the target query's EXPLAIN to confirm the plan is unchanged.

Expected: identical plan on the surviving index, same timing.

1. Add a dedupe gate to the agent: every index recommendation must include the output of the pg_indexes listing for the table, with a written statement that no existing index covers the same columns.

Expected: duplicate proposals get caught before any DDL runs.

Use this when

• Two indexes on the same table have identical column lists and predicates.
• An agent's "missing index" fix landed on a table that already had the index under another name.
• You want the safe drop order and the guardrail that prevents recurrence.

Not for this skill when

• The definitions differ in any way (extra INCLUDE column, different predicate, different opclass) - those may serve different queries; evaluate separately.
• One of the twins is a UNIQUE index enforcing a constraint - keep the constraint, drop the plain twin only.
• You are mid-migration with an old and new index intentionally coexisting - drop the old one only after cutover.

Variant phrasings

• "postgres duplicate indexes same columns"
• "how to find redundant indexes pg_stat"
• "two identical indexes different names"
• "agent created index that already existed"

Why it happens

The agent checked for a missing index by name pattern or by asking the planner about the slow query, not by diffing index definitions. "No index named idxorderscustomer" reads as "no index on customer" to a sloppy check, so it creates idxcustomerid_orders - same btree, new name. The planner is happy with either; the table pays for both on every write.

Edge cases

• Expression indexes that look similar but differ in the expression text are not duplicates - compare indexdef exactly.
• Concurrent DROP of the twin briefly takes a lock; on ultra-hot tables do it in a quiet moment.
• Some schema-management tools recreate indexes with generated names on every run; fix the tool config or the duplicate returns.
• After dropping, the surviving twin keeps its own statistics - no ANALYZE strictly needed, but harmless to run.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstWOs4z_h0ylWAWSqeD2m7A