Getting ChatGPT to Write Accurate Database Index Suggestions Without Missing Compound Cases

You ask ChatGPT to review a slow query and suggest indexes. It gives you four single-column indexes, you add them, and the query is still slow. The real problem is a compound index where column order matters — and the model never mentioned it. This is the most common failure mode when using AI for database performance work, and it has a reliable fix.

What You'll Learn

• Why ChatGPT defaults to single-column indexes and how to break that habit
• A prompt structure that forces compound index reasoning and column ordering
• How to ask for partial and covering indexes without guessing
• How to feed EXPLAIN output back into the conversation for validation
• The gotchas to watch for before you run any AI-generated index DDL in production

Prerequisites

This guide assumes you're working with PostgreSQL (most examples) or MySQL, and that you're comfortable reading a basic query plan. You don't need to be a DBA, but you should know what a sequential scan is and roughly why it's slow on large tables. You'll also need access to ChatGPT (GPT-4 or later; GPT-3.5 is too weak for this kind of multi-step reasoning).

How ChatGPT Thinks About Indexes (and Where It Falls Short)

ChatGPT's training data contains enormous amounts of SQL documentation, StackOverflow answers, and blog posts about indexing. That makes it surprisingly good at recognizing that a WHERE clause column probably needs an index. The problem is that the most common advice in that training data is also the most basic: "Add an index on the column you filter by."

Compound indexes get far less coverage in typical SQL tutorials, and the nuance around column ordering (high-selectivity columns first, or match the query's filter-then-sort pattern) is almost never explained clearly in a single passage the model can absorb cleanly. So it pattern-matches to the simple case.

There's another subtlety: ChatGPT doesn't have your actual table statistics. It can't know that status has only three distinct values while user_id has millions. Without that context, it will often suggest leading with the low-selectivity column, which defeats the purpose of the index entirely.

This is similar to how the model handles other database-adjacent problems — for example, when getting ChatGPT to write accurate database transaction logic, it also needs explicit context about isolation levels rather than guessing from the query shape alone. The fix in both cases is the same: give the model the context it can't infer.

The Prompt Structure That Gets Compound Indexes Right

A vague prompt like "suggest indexes for this query" produces vague output. You need a structured prompt that gives ChatGPT the facts it's missing and asks explicit questions it would otherwise skip.

Here's a template that works well:

You are a PostgreSQL performance expert. I will give you:
1. A slow query
2. The table schema (DDL)
3. Approximate row counts and cardinality notes for key columns
4. The current EXPLAIN ANALYZE output

Your job:
- Identify every index that could help this query, including compound indexes
- For each compound index, specify the exact column order and explain why that order is correct
- Note whether any existing indexes are redundant or harmful
- Flag any case where a partial index or covering index is more appropriate than a full index
- Do NOT suggest single-column indexes if a compound index would serve the same predicate

--- Query ---
[paste query here]

--- Schema ---
[paste CREATE TABLE here]

--- Cardinality notes ---
status: 3 distinct values (~equal distribution)
user_id: ~2M distinct values
created_at: continuous timestamp, range queries common

--- EXPLAIN ANALYZE output ---
[paste output here]

The cardinality notes section is the most important addition most people skip. Without it, you're asking ChatGPT to guess distribution from column names alone, which it does poorly.

Forcing ChatGPT to Reason About Column Order

Even with the structured prompt above, ChatGPT can still slip into listing columns in the order they appear in the WHERE clause rather than the order that produces the best index. Add an explicit constraint to the prompt to prevent this.

After your schema block, include:

Column ordering rule: For each compound index you suggest, explain the column order decision using these criteria:
1. Equality predicates before range predicates
2. Higher-selectivity columns before lower-selectivity ones, unless overridden by rule 1
3. If an ORDER BY or GROUP BY can be satisfied by the index, note the sort direction for each column

If two orderings are defensible, list both and explain the trade-off.

This pushes the model into structured reasoning rather than pattern-matching. You'll see responses that actually cite selectivity and sort direction instead of just listing columns.

Here's a concrete example. Suppose you have this query:

SELECT id, amount, created_at
FROM orders
WHERE status = 'pending'
  AND user_id = 12345
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC;

Without guidance, ChatGPT often suggests CREATE INDEX ON orders(status, user_id, created_at). With the column ordering rule injected, it should arrive at (user_id, status, created_at DESC) — equality predicates first, highest selectivity leading among equality columns, and the sort direction on the range column to avoid a sort step. That's a meaningfully better index.

Getting Partial and Covering Index Suggestions

Partial indexes (indexes with a WHERE clause) are one of PostgreSQL's most useful features for write-heavy tables, and ChatGPT almost never suggests them unprompted. The same goes for covering indexes using INCLUDE.

Add this block to your prompt to surface both:

After listing standard indexes, check:
- Can any index be made partial? (i.e., does the query always filter on a stable low-cardinality value like status = 'active'?)
  If so, write the partial index DDL and quantify the size benefit.
- Can any index be made covering? (i.e., does the SELECT list contain columns not in the WHERE or ORDER BY?)
  If so, add an INCLUDE clause and list which columns it covers.
- Note: any partial index DDL must use CREATE INDEX CONCURRENTLY on a live table.

The last line matters. ChatGPT sometimes generates bare CREATE INDEX statements for partial indexes without the CONCURRENTLY keyword, which will take an ACCESS EXCLUSIVE lock on your table. The prompt reminder is a cheap guard. You should also be aware of this risk more broadly — as discussed in getting ChatGPT to write accurate SQL migrations without locking production tables, AI-generated DDL needs explicit production-safety checks.

Asking ChatGPT to Validate Its Own Suggestions Against EXPLAIN Output

The most productive use of ChatGPT for indexing is a two-pass conversation. In the first pass, you get the index suggestions. In the second pass, you paste back the new EXPLAIN ANALYZE output after adding an index and ask ChatGPT to confirm it's being used correctly.

Prompt for the second pass:

Here is the EXPLAIN ANALYZE output after adding the index you suggested.
Check:
1. Is the planner using the new index, or is it still doing a sequential scan? If so, why?
2. Is the index being used for both filtering and sorting, or only one of them?
3. Is the actual row estimate close to the planned estimate? A large mismatch suggests stale statistics — tell me if I should run ANALYZE.
4. Are there any remaining sequential scans on large row counts that a second index could address?

--- New EXPLAIN ANALYZE ---
[paste output here]

This second pass catches two common failure modes: the planner ignoring a new index because table statistics are stale, and an index being used only partially (for filtering but not sorting). ChatGPT is good at reading EXPLAIN output when you paste it directly — it just needs to be told what to look for.

Common Pitfalls When Using AI for Index Advice

Accepting index suggestions without checking existing indexes first

ChatGPT doesn't know what indexes already exist unless you tell it. If you paste only the query and not the full schema including existing index definitions, it will suggest indexes you already have, or worse, suggest a new index that's made redundant by an existing one. Always include the output of \d tablename (PostgreSQL) or SHOW INDEX FROM tablename (MySQL) in your prompt.

Treating every suggestion as production-ready DDL

Index creation on large tables is a write-blocking operation unless you use CONCURRENTLY. ChatGPT will sometimes generate correct index DDL and sometimes forget the keyword entirely. Never run AI-generated index DDL without reviewing it for CONCURRENTLY, and verify it in a staging environment first. This is in the same category of risk as database connection pool configs — the model can get the logic right while missing an operational detail that breaks production.

Ignoring index bloat on high-write tables

More indexes means more write overhead. ChatGPT will rarely volunteer this trade-off. If your table has a high insert/update rate, ask explicitly: "Given that this table receives approximately X writes per second, which of these indexes would cause the most write amplification, and is that trade-off worth it?" Giving a concrete write rate forces the model to reason about cost, not just query benefit.

Over-indexing on low-selectivity columns

If ChatGPT suggests a standalone index on a column like is_deleted (boolean) or status (three values), push back. Ask: "Is this index actually selective enough for the planner to use it, or will it be ignored in favor of a sequential scan?" The model will usually give you a correct answer when directly challenged.

Missing indexes on foreign keys

PostgreSQL does not automatically index foreign key columns. ChatGPT will sometimes overlook this, especially if you don't paste the full schema. Ask explicitly: "Are there any foreign key columns in this schema that lack indexes and could cause lock contention during deletes on the referenced table?"

This kind of targeted follow-up is a pattern worth building into your workflow for other complex AI-assisted tasks too. The same technique of direct, failure-mode-specific questioning applies when getting ChatGPT to write accurate Elasticsearch queries — asking about specific failure modes surfaces problems the model wouldn't otherwise flag.

Wrapping Up

ChatGPT is a genuinely useful indexing assistant, but only if you do the work to supply the context it's missing. Here's what to take away:

1. Always include cardinality notes in your prompt. Column names alone don't tell the model whether a column has three distinct values or three million. This single addition produces the biggest improvement in suggestion quality.
2. Add explicit column-ordering rules to your prompt. Ask the model to justify each position in every compound index using selectivity and predicate type (equality vs. range).
3. Request partial and covering index analysis separately. ChatGPT won't volunteer these unprompted, but it reasons about them well when asked directly.
4. Run a second-pass conversation with your new EXPLAIN ANALYZE output. This catches cases where the planner ignores your new index due to stale statistics or an unexpected cost estimate.
5. Always review generated DDL for CONCURRENTLY and staging-test before production. Treat AI-generated index DDL the same way you'd treat any schema change: review, test, schedule a maintenance window if needed.