Review and Consolidate Index Recommendations

Tier: Core · Workflow area: SQL Authoring & Query Tuning · ~35 minutes

Every engine now emits index advice: Postgres advisors, SQL Server missing-index DMVs, cloud consoles. All of it shares one flaw — recommendations are generated query-by-query with no global view. This playbook uses an LLM for the part advisors skip (cross-query consolidation and write-cost reasoning) and hypopg for the part LLMs cannot do (proof).

When to use

• The advisor list has grown past five recommendationsbelow that, consolidate by eye; above it, overlaps and near-duplicates multiply faster than attention.
• Index sprawl cleanupa mature table with a dozen accreted indexes needs the same pass in reverse: which existing indexes overlap, which are unused, which can merge.
• Before a new workload shipsconsolidating the advisor output from load-testing beats retrofitting indexes after the launch-day incident.

When NOT to use

• Write-heavy tables at the marginon a queue-like table doing 10k writes/sec, the default answer to new indexes is no. Consolidation still helps, but the bar for shipping anything is much higher.
• When statistics are staleadvisors and planners both hallucinate on bad stats. ANALYZE first; half the recommendation list may evaporate.
• As an auto-apply pipelinesome platforms offer automatic index creation. Everything auto-created still deserves this review; unreviewed auto-indexes are how you get 40GB of duplicates.

The recipe

1. 1.

   Export the raw material: recommendations, existing indexes, write profile.

   Copy

   -- Existing indexes with usage counts (find the dead weight too)
   SELECT s.indexrelname, s.idx_scan,
          pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
          i.indexdef
   FROM pg_stat_user_indexes s
   JOIN pg_indexes i ON i.indexname = s.indexrelname
   WHERE s.relname = 'orders'
   ORDER BY s.idx_scan;
   
   -- Write profile: how expensive is a new index here?
   SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd
   FROM pg_stat_user_tables WHERE relname = 'orders';
   

   Add the advisor output itself — pganalyze Index Advisor recommendations, or on SQL Server the missing-index DMV dump (sys.dm_db_missing_index_details joined to _groups and _group_stats).

2. 2.

   Ask for consolidation with rationale, not a bigger list.

   Copy

   You are consolidating index recommendations for PostgreSQL 16.
   Below: (a) advisor recommendations, (b) existing indexes with scan
   counts and sizes, (c) table write rates, (d) the 10 highest-value
   queries touching this table.
   
   Produce a consolidation plan:
   1. MERGE: recommendations that one index can serve (explain the
      key-order choice: equality columns first, then range, then
      INCLUDE — justify against the actual queries).
   2. DROP candidates: existing indexes that are unused or would be
      covered by a merged index.
   3. REJECT: recommendations not worth their write cost — show the
      write-rate numbers in your reasoning.
   4. For every survivor: exact CREATE INDEX DDL, expected serving
      queries, and what it costs (size estimate, write amplification).
   Flag anything where HOT updates would be broken by indexing a
   frequently-updated column.
   

   The HOT-update flag is Postgres-specific gold: indexing a hot-updated column silently converts cheap HOT updates into full index maintenance on every index on the table.

3. 3.

   Screen every surviving proposal with hypothetical indexes.

   Copy

   CREATE EXTENSION IF NOT EXISTS hypopg;
   SELECT hypopg_create_index(
     'CREATE INDEX ON orders (customer_id, status) INCLUDE (ordered_at)');
   
   -- Re-plan each of the queries this index claims to serve:
   EXPLAIN SELECT ... ;   -- planner must actually choose the hypothetical
   
   SELECT indexname FROM hypopg_list_indexes();
   SELECT hypopg_reset();
   

   Record, per proposal, which of its claimed queries actually use it in the plan. An index the planner won't pick is a rejected row, cost-free. A good screen result:

   Copy

   proposal: orders(customer_id, status) INCLUDE (ordered_at)
   claimed queries: Q1, Q4, Q7
   hypopg result:  Q1 ✓ (idx scan, cost 8412 -> 96)
                   Q4 ✓ (idx-only scan)
                   Q7 ✗ (planner keeps seq scan — predicate not sargable)
   verdict: build; Q7 needs a rewrite, not this index
   

4. 4.

   Validate the drop list just as hard. For each drop candidate, confirm zero scans over a full business cycle (including month-end), then drop reversibly:

   Copy

   -- Postgres has no invisible indexes: snapshot usage, then drop with
   -- the recreate statement saved in the ticket.
   SELECT indexrelname, idx_scan FROM pg_stat_user_indexes
   WHERE relname = 'orders';
   DROP INDEX CONCURRENTLY idx_orders_legacy_status;  -- recreate DDL in ticket
   

   On MySQL and Oracle, flip the index invisible first and watch for a cycle — the safest drop is one that was invisible for two weeks with no complaints.

5. 5.

   Ship builds off-peak, one at a time, and re-measure. CREATE INDEX CONCURRENTLY, then after a business cycle pull pg_stat_user_indexes again: every new index must show scans, every merged-away query must show its improved plan. An unused new index goes back out.

Make it yours

• SQL Serverthe missing-index DMVs famously over-suggest and never mention key order subtleties; give the model equality_columns, inequality_columns, included_columns and user_seeks/avg_user_impact per recommendation, ask for the same merge/drop/reject split, and validate with a restored copy since hypothetical-index tooling is awkward; check proposed indexes against sys.dm_db_index_usage_stats duplicates first.
• Oraclecombine SQL Access Advisor output with V$OBJECT_USAGE-style monitoring (ALTER INDEX ... MONITORING USAGE); build survivors as INVISIBLE, enable per-session with OPTIMIZER_USE_INVISIBLE_INDEXES, and flip visible only after plan verification.
• MySQLadvisors are mostly external (pt-index-usage, cloud consoles); the same consolidation prompt works on sys.schema_unused_indexes plus your slow-log digest, and invisible indexes give you both a safe rollout and a safe drop path.

Quality check

• Key order is justified by real predicatesequality-first-then-range is the rule, but the model must cite your actual queries, not recite the rule. Verify against two queries yourself.
• Every kept index has a named owner-query"might help" is not a reason to spend write amplification forever. No serving query in the plan, no index.
• Write cost was priced, not mentionedthe table's tuples-modified-per-day times the number of indexes is the tax. Say the number out loud in the ticket.
• Drops are reversible on paperthe recreate DDL lives in the change ticket before the drop runs. Rebuilding a 200GB index from memory at 2 a.m. is not a plan.