Windsurf prompt to optimize database queries
Query tuning goes badly when changes and measurements happen in different places. Here Windsurf holds both: Cascade ranks suspect queries in Chat mode, waits for your approval, then pairs every index and rewrite with before-and-after EXPLAIN ANALYZE plans captured from the terminal.
Last updated
Find and fix the slow database queries in this repo. First, in Cascade Chat mode, list every query touching the largest tables and rank them by suspicion: missing indexes, SELECT star in hot paths, N+1 loops in list endpoints, unbounded result sets. Wait for my approval on that list. Then fix in order: covering indexes as migration files, batched IN queries or joins replacing per-row lookups, LIMIT with keyset pagination on any endpoint returning whole tables, and filtering pushed down into SQL. For every change, run EXPLAIN ANALYZE in the Windsurf terminal before and after, and include both plans in the summary. No denormalization and no caching in this pass. End with the migration list plus each query you left alone and why.
Same task in other tools
Questions about this prompt
Why require the ranked list before any edits?
Optimization invites drive-by rewrites of queries that were fine. The approval gate keeps the pass focused on measured problems, and the plan output proves each change earned its migration.
How do I use this when I already know the slow query?
Paste the query and its current plan into the prompt and delete the discovery step. Keep the before-and-after EXPLAIN ANALYZE requirement; a fix without a comparative plan is a guess.
An index got added but the plan ignores it. Why?
Common causes: a function wrapped around the column, a type mismatch forcing a cast, or stale statistics needing an ANALYZE run. Ask Cascade to check all three against the actual plan text before it reaches for a second index.