agent added an index on a UUID primary key lookup the planner already satisfied with the existing btree

Redundant index on a UUID primary key the planner already satisfied

TL;DR: Drop the duplicate - the primary key's btree already serves UUID lookups, and the extra index only taxes writes and storage. Verify with EXPLAIN that the plan used the PK index all along, then drop the redundant one concurrently.

agent added an index on a UUID primary key lookup the planner already satisfied with the existing btree

Steps

1. List the indexes on the table and compare definitions:

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

Expected: two indexes over the same column(s) - the PRIMARY KEY btree and the agent's new one with a different name.

1. Confirm the query plan never needed the new index. Run EXPLAIN with the new index temporarily invisible:

   BEGIN;
   DROP INDEX new_redundant_index;
   EXPLAIN (ANALYZE, BUFFERS) SELECT ... WHERE id = '...';
   ROLLBACK;

Expected: identical fast plan using the primary key index, with or without the duplicate present.

1. Check the duplicate is not secretly serving a different query shape (e.g. an INCLUDE list or a different sort):

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

Expected: the duplicate shows zero or trivial scans while the PK index carries the load.

1. Drop the redundant index for real:

   DROP INDEX CONCURRENTLY new_redundant_index;

Expected: command succeeds, writes get cheaper, storage shrinks.

1. Re-run the lookup query plan one final time to confirm nothing regressed.

Expected: Index Scan on the primary key, same timing as before.

1. Add a pre-recommendation check to the agent: before suggesting any index, it must list existing indexes on the table and show that none covers the same leading column(s).

Expected: duplicate recommendations stop at the proposal stage.

Use this when

• An agent added an index on a column that already has a primary key or unique index.
• pg_indexes shows two indexes with the same column list under different names.
• The new index has ~zero scans in pg_stat_user_indexes.

Not for this skill when

• The new index has extra INCLUDE columns or a different column order that serves a genuinely different query - that is a covering-index decision, not a duplicate.
• The existing index is on an expression and the new one is on the raw column (or vice versa) - different indexes.
• You are unsure which queries use the new index - check a full query-log cycle first.

Variant phrasings

• "duplicate index postgres same columns different name"
• "agent added index on primary key column"
• "how to find redundant indexes in postgres"
• "planner already uses existing btree, new index unused"

Why it happens

Agents that recommend indexes often check the slow query but not the existing schema. A UUID primary key already has a btree, and a PK equality lookup is the textbook case the planner handles. The agent sees "slow lookup on id" in its trace, misses the PK index in its schema read, and adds a second identical btree. The planner picks one arbitrarily; the other just burns write I/O and disk.

Edge cases

• A non-unique index on the same column as a unique index is still redundant for lookups - drop the non-unique one.
• Partial duplicates (same leading column, different extras) need per-query EXPLAIN checks before dropping.
• On very hot tables, even the brief lock from a non-concurrent DROP matters - use CONCURRENTLY and a quiet window.
• Some ORMs auto-create indexes from model definitions; fix the model too or the duplicate comes back on next migrate.

Provenance

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