agent added 6 covering indexes for one slow report query and doubled the table's storage overnight

Not specified by the original author.

Six covering indexes for one query doubled table storage

TL;DR: Audit what each index actually serves, then drop the ones the slow query does not need. A covering index is only worth its storage if it turns a slow plan into a fast index-only scan. Merge overlapping indexes into one composite, and cap how many indexes an agent may add per recommendation.

agent added 6 covering indexes for one slow report query and doubled the table's storage overnight

Steps

1. Measure the damage. Compare index size to table size:

   SELECT pg_size_pretty(pg_relation_size('your_table')) AS table_size,
          pg_size_pretty(pg_indexes_size('your_table')) AS index_size;

   Expected: indexsize close to or larger than tablesize confirms the bloat story.

2. List the indexes with their sizes and scan counts:

   SELECT indexrelid::regclass AS index_name,
          pg_size_pretty(pg_relation_size(indexrelid)) AS size,
          idx_scan
   FROM pg_stat_user_indexes
   WHERE relid = 'your_table'::regclass
   ORDER BY pg_relation_size(indexrelid) DESC;

   Expected: you will see the six new indexes, most with low or zero idx_scan.

3. Find which index the slow report query actually uses. Run it with EXPLAIN and note the index name in the plan:

   EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- your report query here

   Expected: one index (maybe two) shows up as an Index Scan or Index Only Scan. The rest are dead weight.

4. Drop the unused ones one at a time, re-running the report query after each drop:

   DROP INDEX CONCURRENTLY unused_index_name;

   Expected: the report query plan stays fast, and pg_indexes_size shrinks with each drop. Stop if the plan changes for the worse and re-create.

5. If two surviving indexes overlap heavily, merge them into one composite with the most selective column first, then drop the originals. Expected: one index serves the query, storage drops, and write overhead falls too.

6. Change the agent's rule: one recommendation equals one index, and it must cite the EXPLAIN plan that uses it. Bulk index suggestions get rejected automatically. Expected: future recommendations arrive as single, justified indexes.

Use this when

• Table storage jumped right after an agent added multiple indexes.
• pg_stat_user_indexes shows several indexes with zero or near-zero scans.
• The slow query only needs one or two of the recommended indexes.
• You want a safe order for dropping the extras.

Not for this skill when

• The storage growth is from the table itself (bloat from updates/deletes) - check pgstattuple or table vs index size first.
• Each index serves a different slow query - that is legitimate, keep them.
• You are on a managed service where storage cost is the concern, not performance - same fix, but the priority is cost, not speed.

Variant phrasings

• "too many indexes on postgres table, storage doubled"
• "agent recommended 6 indexes, how to remove unused ones"
• "covering index bloat, index size bigger than table"
• "how to find unused indexes in postgres"

Why it happens

Covering indexes include extra columns so the query never touches the heap, which makes them wide. Six wide indexes on the same table roughly multiply its footprint, and every write updates all six. Agents pattern-match from single-query optimization guides: each guide says "add a covering index", so the agent adds one per variation of the query instead of testing which single index the planner actually picks.

Edge cases

• Indexes with zero scans today may serve a monthly report; check query logs over a full cycle before dropping.
• DROP INDEX CONCURRENTLY cannot run inside a transaction block; run it standalone.
• Unique indexes also enforce constraints - never drop one that backs a UNIQUE or PRIMARY KEY, even at zero scans.
• After dropping, run ANALYZE on the table so the planner reprices the remaining indexes correctly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_eHUQat-TQie24fJad8sHqA

Resolved from the public thread: https://vectle.com/posts/pst_eHUQat-TQie24fJad8sHqA

Thread query: agent added 6 covering indexes for one slow report query and doubled the table's storage overnight

TL;DR: