GitHub Copilot prompt to optimize database queries
Query tuning with an assistant works when the assistant reasons over real plans, not vibes. This prompt sets up a loop where GitHub Copilot proposes an index or rewrite, you paste back the actual EXPLAIN output, and nothing merges until the plan shows the index being used.
Last updated
Review the data access layer in src/repositories/ for query waste against {{database}}. Start by asking me for the slowest endpoint, then trace its call path and list every query it issues per request, including ones hidden behind ORM lazy loads. Targets: replace per-row lookups in loops with a single IN query or a join, cut SELECT * down to the columns the caller reads, and convert OFFSET pagination on the activity feed to keyset pagination on (created_at, id). For each proposed index, output the CREATE INDEX statement in a migration file plus the EXPLAIN you expect before and after, and I will paste back the real plan to confirm the index is used. Do not denormalize anything and do not add caching, this pass is queries only. Done means the endpoint issues a fixed number of queries regardless of row count, verified by the query log.Customize it
Runs in your browser. Nothing you type here is sent anywhere.
Review the data access layer in src/repositories/ for query waste against PostgreSQL. Start by asking me for the slowest endpoint, then trace its call path and list every query it issues per request, including ones hidden behind ORM lazy loads. Targets: replace per-row lookups in loops with a single IN query or a join, cut SELECT * down to the columns the caller reads, and convert OFFSET pagination on the activity feed to keyset pagination on (created_at, id). For each proposed index, output the CREATE INDEX statement in a migration file plus the EXPLAIN you expect before and after, and I will paste back the real plan to confirm the index is used. Do not denormalize anything and do not add caching, this pass is queries only. Done means the endpoint issues a fixed number of queries regardless of row count, verified by the query log.
Same task in other tools
Questions about this prompt
Why does the prompt have me paste EXPLAIN output back in?
Copilot cannot execute queries against your database, so the real plan is the only proof an index is used. The paste-back loop stops plausible-sounding indexes from merging unverified.
Can keyset pagination replace OFFSET on other lists too?
Anywhere the ordering has a tiebreaker. Keep (sort_column, id) as the cursor, encode it opaquely in the API, and remember users lose the ability to jump straight to page forty.
The new index exists but the plan still shows a sequential scan. Now what?
Run ANALYZE first, stale statistics are the usual cause, and retest with realistic row counts. On tiny tables the planner prefers a scan, which says nothing about production behavior.