# Index Optimizer

> Analyzes query patterns and table access to recommend index creation, removal of unused indexes, and index maintenance (REINDEX, ANALYZE).

- Skill: `uitbreidenos/index-optimizer` (Agent Skill)
- Install (CLI): `npx skillmds@latest add uitbreidenos/index-optimizer`
- Raw SKILL.md: https://api.skillmd.com/api/skills/uitbreidenos/index-optimizer/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: UitbreidenOS (https://skillmd.com/u/uitbreidenos)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/uitbreidenos/index-optimizer

---


# Index Optimizer

## When to activate
When queries are slow due to sequential scans on large tables, when adding new features that change query patterns, during quarterly performance reviews, or when unused indexes are consuming disk/memory.

## When NOT to use
Skip for tables under 10,000 rows (sequential scans are faster). Skip if queries already use index-only scans.

## Instructions

1. Identify the slow queries from `pg_stat_statements` or slow query log.
2. Run `EXPLAIN ANALYZE` on each to find sequential scans on large tables.
3. Check existing indexes with `pg_indexes` and `pg_stat_user_indexes`.
4. For each sequential scan: analyze the WHERE/JOIN/ORDER BY columns and recommend a composite index.
5. Check for unused indexes (scans = 0 over 30+ days) and recommend removal.
6. Check for duplicate indexes (same columns, different names).
7. Recommend `REINDEX` if index bloat exceeds 30%.

## Example

**Scenario:** `orders` table, 12M rows, queries filtering by `customer_id` and `status`.

```
INDEX ANALYSIS: orders (12M rows)

Current indexes:
  orders_pkey (id)                    — 2.1M scans ✅
  orders_created_at_idx (created_at)  — 0 scans ❌ UNUSED (47 days)
  orders_email_idx (email)            — 180K scans ✅

Sequential scans detected:
  WHERE customer_id = $1 AND status = $2  → 450ms avg (seq scan)

Recommendations:
  CREATE INDEX orders_customer_status_idx ON orders (customer_id, status);
  -- Expected improvement: 450ms → 2ms

  DROP INDEX orders_created_at_idx;
  -- Saves 1.2GB disk, reduces write amplification

  REINDEX INDEX orders_pkey;
  -- 34% bloat detected, estimated 400MB savings
```