# 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.

```text
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:
   ```sql
   SELECT indexname, indexdef FROM pg_indexes
   WHERE tablename = 'your_table'
   ORDER BY indexdef;
   ```
   Expected: two rows with byte-identical indexdef strings but different indexnames.

2. Confirm neither is special: check that neither backs a constraint (primary key, unique, exclusion) and neither is INVALID:
   ```sql
   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.

3. Check scan counts to pick the keeper (keep the older or more-scanned one; it does not matter functionally):
   ```sql
   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.

4. Drop the duplicate:
   ```sql
   DROP INDEX CONCURRENTLY duplicate_index_name;
   ```
   Expected: succeeds; storage and write overhead drop immediately.

5. Re-run the target query's EXPLAIN to confirm the plan is unchanged.
   Expected: identical plan on the surviving index, same timing.

6. 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 idx_orders_customer" reads as "no index on customer" to a sloppy check, so it creates idx_customer_id_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/pst_WO_s4z_h0ylWAWSqeD2m7A