Back to Blog

Find Missing Indexes With AI (On a Live Database)

Ask Cursor or Claude to inspect indexes and slow-looking filters on staging — seq scans on big tables, FK columns without indexes, leftover unused indexes. Read-only prompts. You still CREATE INDEX in a migration.

App feels slow. Someone says “add an index.” Nobody remembers what’s already on orders.

Cursor or Claude on staging (or a replica) can inventory indexes and flag obvious gaps — FKs without indexes, filter columns on big tables, duplicates. They should not run CREATE INDEX CONCURRENTLY on production from chat.

Read-only — why. Prefer replica / staging. Ship via migration — preflight it.

What “missing index” means here

Not magic. Catalog + common sense:

  • Foreign key columns often need an index for deletes/joins
  • Columns you always put in WHERE / JOIN on big tables
  • Composite indexes that don’t match real filter order
  • Leftover indexes from dropped features

AI helps you see the catalog and draft a hypothesis. Your slow-query log proves it.

Prompt: inventory

Read-only. Inspect the database.
For the largest tables (by estimated row count if available,
else by name importance: orders, events, sessions, etc.):
1. List existing indexes (name, columns, unique or not)
2. List foreign key columns
3. Flag FK columns that have no supporting index
Show the SQL you used (pg_indexes / information_schema / pg_constraint).

Prompt: match indexes to a slow query

Here's a slow query (or: the query our app runs for <feature>):
<paste SQL>

Using the live schema:
- what indexes exist that might apply?
- what index would you propose (columns + order)?
- any risk it duplicates an existing index?

Do not CREATE INDEX. SELECT/EXPLAIN only if allowed.
Show SQL.

If EXPLAIN isn’t available through the connector, ask for the proposal and run EXPLAIN (ANALYZE, BUFFERS) yourself in psql on staging.

Prompt: unused / redundant suspects

List indexes on <table> and compare column sets.
Flag likely duplicates (same columns, different names)
and indexes that look legacy (names like tmp_, old_).
I will confirm against pg_stat_user_indexes myself —
just produce candidates, don't DROP anything.

Prompt: after you add an index in a migration

Migration added index <name> on <table>(cols).
Verify it exists on staging and show its definition.
Compare EXPLAIN for <query> only if you can do so read-only.
SELECT only.

Hard rules

  • No DDL from chat — even if someone disabled read-only for “just a second”
  • Empty staging → weak advice — refresh before an index review
  • Big CREATE INDEX on prod — concurrent strategy, maintenance window, your DBA habits — not Composer improvisation
  • Verify proposed indexes against real EXPLAIN / metrics (verify answers mindset)

Where this fits with support tickets

If support says “the app is slow,” eng uses this path. Support should not be inventing indexes — they use support lookups for account facts.

Catalog in, hypothesis out, migrate on purpose.

Staging/replica read-only: mcpserver.design (7-day Solo trial). Inventory prompt first.

Related reading:

← Back to Blog