The difference between a useless answer and a good one here is entirely in what you paste.

Useless: "Why is this query slow?" + the query.
Useful: the query, the EXPLAIN ANALYZE output, the table row counts, the existing indexes, and the Postgres version.

The prompt I use:

"Postgres 15. Table events has 42M rows, users has 1.1M.
Existing indexes: [list].
Query: [query].
EXPLAIN ANALYZE: [paste].

Tell me: which node is actually expensive, what index would fix it, and what that index costs on writes. Give the CREATE INDEX CONCURRENTLY statement."

Asking for the write cost is the part people leave out. Otherwise you get a recommendation for four new indexes on a table that takes 8k inserts a second.

My 5.2s query is now 140ms on one partial index.

Generated Outputs (1)

1 weeks ago

ChatGPT

gpt-4-turbo

1 weeks ago

Generated Output

The expensive node is the Seq Scan on events (38.9M rows filtered to 1,204).

CREATE INDEX CONCURRENTLY idx_events_user_recent
ON events (user_id, created_at DESC)
WHERE deleted_at IS NULL;

Write cost: ~6% slower inserts, partial predicate keeps it to roughly 22% of the table.

Turning A Slow Query Into An Index With One Prompt

Generated Outputs (1)