# Best Tabnine Prompts for SQL Query Optimization: Quick Start Guide

> source: https://promptsio.com/tabnine-prompts-sql-query-optimization/
> published: 2026-10-06T18:04:57+00:00
> updated: 2026-10-06T18:04:57+00:00
> topic: Coding & Tech

Tabnine chat prompts for slow SQL: read execution plans, rewrite queries, propose indexes and verify the result safely before anything touches production.

A backend developer, Dev, owns an orders service on PostgreSQL. The `orders` table has about 40 million rows, and the "recent orders for a customer, with totals" endpoint has gone from fast to several seconds. He has Tabnine in his IDE and a vague plan to "ask the AI to make it faster". That plan usually produces a confident rewrite that is not faster, or an index that slows down writes. This guide gives you Tabnine prompts for SQL query optimization that force the right inputs (the query, the schema, the plan), ask for ranked hypotheses instead of magic, and end in a test you can run on a copy of your data.

## What Tabnine is good for here, and what it is not

Tabnine is an AI coding assistant that works in your IDE: code completion plus a chat that can see the context you give it. For SQL it is useful for explaining a query, spotting anti-patterns, drafting rewrites and index candidates, and writing test scaffolding. Check the current feature set, supported models, context limits and privacy or deployment options for your plan, because these vary and change.

What it cannot do: it does not know your data distribution, your real row counts, or your production load unless you paste them. It cannot run your query for you against your database. Treat every output as a hypothesis, measure it with `EXPLAIN (ANALYZE, BUFFERS)` on a realistic copy, and never run generated DDL, `UPDATE`, `DELETE` or migrations on production unchecked.

Also mind what you paste. Remove customer data, secrets and connection strings from anything you send to a chat assistant, and follow your company's policy on sharing schemas and queries.

## A prompt formula for query tuning

Give the assistant six things: **Engine and version** (PostgreSQL 15, MySQL 8.0), **Schema** (columns, types, existing indexes, row counts), **The query** (verbatim), **The evidence** (the plan, timings), **The goal** (target latency, read vs write mix) and **Constraints** (no downtime, no schema change, ORM in use). Then ask for a ranked list with a way to verify each suggestion.

## Reading plans and finding the real problem

### Prompt 1: Explain the plan in plain language

```
You are a senior PostgreSQL 15 performance engineer. Below is a slow query and its EXPLAIN (ANALYZE, BUFFERS) output from a staging copy with a similar row count.

Table: orders (id bigint PK, customer_id bigint, status text, created_at timestamptz, total_cents int). ~40M rows. Indexes: PK on id; btree on (customer_id).
Query:
SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 48213 AND status IN ('paid','shipped') ORDER BY created_at DESC LIMIT 20;

[PASTE EXPLAIN (ANALYZE, BUFFERS) OUTPUT HERE]

Explain each plan node in plain English, identify the node where most time and buffers are spent, and say what the planner row estimates vs actual rows tell us. List the top 3 likely causes in order of probability, and for each give one check I can run to confirm it. Do not propose a fix yet.
```

**Why it works:** separating diagnosis from fixing stops the assistant from jumping to the first index idea. **What to expect / check:** it should mention a sort node or filter after an index scan and any row estimate mismatch. If it ignores the actual figures, remind it to quote them. **Iterate:** "Quote the exact numbers from the plan that support your cause number 1."

### Prompt 2: Spot anti-patterns in a query

```
Review this PostgreSQL 15 query for performance anti-patterns only (not style). Schema: invoices(id, account_id, issued_at date, amount_cents, currency, status), accounts(id, region, created_at). invoices has ~12M rows; accounts ~200k.

SELECT a.region, SUM(i.amount_cents)
FROM invoices i JOIN accounts a ON a.id = i.account_id
WHERE DATE_TRUNC('month', i.issued_at) = DATE '2025-03-01'
GROUP BY a.region;

For each issue: quote the offending line, explain why it hurts (for example a function on a column preventing index use), give a rewritten version, and state any behaviour difference between old and new. If the rewrite changes results for edge cases such as time zones or NULLs, say so.
```

**Why it works:** "behaviour difference" forces equivalence thinking, which is where bad rewrites slip. **What to expect / check:** it should propose a range predicate (`issued_at >= ... AND < ...`). Verify results match with an `EXCEPT` comparison. **Iterate:** "Write the EXCEPT-based query that proves old and new return identical rows for March 2025."

### Prompt 3: Row estimate mismatch

```
In PostgreSQL 15 the planner estimates 120 rows but the node returns 2.1M rows, causing a nested loop that runs for 9 seconds. Table: events(id, tenant_id, type, payload jsonb, created_at), ~80M rows, skewed: one tenant has 35% of rows. Autovacuum is on; last ANALYZE date unknown.

List possible causes of the bad estimate (stale stats, correlated columns, skew, expression predicates, jsonb filters), and for each give a SQL statement to inspect it (pg_stats, pg_stat_user_tables) and the remediation to try on staging first (ANALYZE, per-column statistics target, extended statistics). Mark each step as safe on production or requiring a maintenance window.
```

**Why it works:** asking for inspection statements first gives you evidence, and the production-safety label forces caution. **What to expect / check:** correct use of `CREATE STATISTICS` and `ALTER TABLE ... SET STATISTICS`. Check syntax against your version's documentation. **Iterate:** "Give me the single most likely fix and the plan change I should expect to see afterward."

## Index design prompts

### Prompt 4: Composite index candidates

```
Propose index candidates for PostgreSQL 15. Table orders (~40M rows, ~300k inserts per day, ~2k reads per minute on this query). Existing index: btree(customer_id).

Query pattern A (hot): WHERE customer_id = $1 AND status IN ('paid','shipped') ORDER BY created_at DESC LIMIT 20.
Query pattern B: WHERE customer_id = $1 AND created_at >= $2.

Give up to 3 candidates. For each: the CREATE INDEX CONCURRENTLY statement, column order and why, whether a partial index or INCLUDE columns make sense, which queries it helps, estimated write overhead qualitatively, and which existing index it could replace. Do not invent size estimates; tell me how to measure index size and usage (pg_relation_size, pg_stat_user_indexes).
```

**Why it works:** the read/write numbers make the trade-off explicit, and "do not invent sizes" avoids fake precision. **What to expect / check:** column order should put the equality column first, then the sort column. Test each on staging. **Iterate:** "Compare candidate 1 and 2 on write amplification and on index-only scan potential."

### Prompt 5: Redundant and unused index audit

```
I will paste the output of \d+ orders and a pg_stat_user_indexes listing. Identify indexes that are likely redundant (prefix of another index), duplicate, or unused. For each, say what evidence supports that, what could make the evidence misleading (stats reset time, replicas serving reads, rare monthly jobs), and the safest removal process (monitor, then drop on staging, then DROP INDEX CONCURRENTLY with a rollback script that recreates it). Do not tell me to drop anything immediately.

[PASTE OUTPUT]
```

**Why it works:** it names the traps in "unused" stats, which prevents accidental removal. **What to expect / check:** a cautious plan with recreate scripts. **Iterate:** "Generate the rollback CREATE INDEX statements from the definitions I pasted."

## Rewrites that keep the same results

### Prompt 6: Pagination rewrite

```
Rewrite this endpoint query in PostgreSQL 15 to use keyset pagination instead of OFFSET. Current: SELECT id, created_at, status FROM orders WHERE customer_id = $1 ORDER BY created_at DESC, id DESC OFFSET $2 LIMIT 20; Deep pages (OFFSET 100000) take several seconds.

Provide: the first-page query, the next-page query using a (created_at, id) cursor with correct tie-breaking, the index it needs, how to encode the cursor in the API, and the edge cases (identical timestamps, rows inserted between pages, backwards navigation). Use parameter placeholders only, no string concatenation.
```

**Why it works:** the tie-break requirement and edge-case list mirror real bugs in keyset pagination. **What to expect / check:** row-value comparison `(created_at, id) < ($2, $3)`. **Iterate:** "Write a test that creates 5 rows with the same created_at and proves no duplicates or gaps across pages."

### Prompt 7: Replace correlated subquery

```
Replace the correlated subquery below with a JOIN or window-function version in PostgreSQL 15, and explain whether it should be faster and why. Provide a query that proves the two versions return identical results on the same data.

SELECT c.id, c.name, (SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = c.id) AS last_order FROM customers c WHERE c.region = 'UK';

Notes: customers ~1.5M rows, orders ~40M, index on orders(customer_id, created_at). Mention how customers with no orders are handled (NULL).
```

**Why it works:** NULL handling and a proof query make the rewrite verifiable. **What to expect / check:** a LEFT JOIN with aggregate or LATERAL. Compare plans, do not assume. **Iterate:** "Show me the LATERAL version and when it beats the aggregate join."

## Safety and testing prompts

### Prompt 8: Benchmark plan

```
Create a safe benchmarking checklist for testing a new index and a rewritten query on a staging copy of a 40M-row PostgreSQL 15 table. Include: how to make staging data realistic, warm vs cold cache runs, how many runs and which statistic to report (median and spread), EXPLAIN (ANALYZE, BUFFERS) commands, a way to test concurrent load, and how to measure insert slowdown caused by the new index. End with go/no-go criteria that I fill in: [TARGET P95 LATENCY], [MAX INSERT SLOWDOWN].
```

**Why it works:** it turns "it feels faster" into pass/fail criteria. **What to expect / check:** methodical steps; check anything about tools you do not use. **Iterate:** "Turn this into a short runbook with commands for psql only."

### Prompt 9: Security review of dynamic SQL

```
Review this Node.js (v20) function for SQL injection and query-plan abuse. It builds ORDER BY and filters from request parameters: [PASTE FUNCTION]. List each risk, show the exploit input, and give a fixed version using parameterised queries and an allow-list for sortable columns. Note any remaining risk such as unbounded LIMIT or expensive wildcard searches, and suggest limits.
```

**Why it works:** it asks for exploit inputs so you can write a regression test. **What to expect / check:** an allow-list map and `$1` placeholders. Run the exploit input against the fixed code. **Iterate:** "Write Jest tests for the three exploit inputs."

### Prompt 10: Migration for the new index

```
Write a migration for PostgreSQL 15 that adds the chosen index with CREATE INDEX CONCURRENTLY on a live 40M-row table. Include: the up and down scripts, a note that CONCURRENTLY cannot run inside a transaction block and how my migration tool [TOOL NAME] handles that, a check for an invalid index left by a failed run, and a verification query. Do not run anything; I will test on staging first.
```

**Why it works:** it covers the known failure mode of concurrent index builds. **What to expect / check:** invalid-index cleanup; confirm against your migration tool's documentation. **Iterate:** "Add a lock_timeout and explain what happens if it triggers."

## Worked example: weak to strong

**Prompt 1 (weak):** "Make this SQL faster." plus a pasted query.

**Typical result:** a rewrite with `SELECT *` replaced by column names, an unexplained `CREATE INDEX` on every filtered column, and a claim of "up to 10x faster". No plan was requested, the engine is assumed, and nothing can be verified.

**Prompt 2 (improved):** use Prompt 1 followed by Prompt 4 with the real plan and table facts pasted in.

**Result:** a diagnosis (sort after filter on the customer index), one composite index on `(customer_id, created_at DESC)` with a partial or INCLUDE discussion, a concurrent build statement and a measurement plan. It is better because every claim is tied to evidence you can check.

## End-to-end workflow

- Capture the slow query and `EXPLAIN (ANALYZE, BUFFERS)` on staging.

- Run Prompt 1 for diagnosis.

- Run Prompt 2 or 7 if the query shape is the problem, or Prompt 4 for indexes.

- Run Prompt 8 to design the benchmark and measure.

- Run Prompt 10 to write the migration, test it on staging, then deploy in a quiet window.

- Re-check plans a week later and re-run Prompt 5 to catch redundant indexes.

## Troubleshooting

| Problem | Likely cause | Fix | |---|---|---| | Suggested index is ignored | Wrong column order, low selectivity, stale stats | Run ANALYZE, check the plan, reorder columns | | Rewrite returns different rows | NULL or time zone semantics changed | Compare with EXCEPT, adjust predicates | | Assistant invents syntax | Wrong engine or version assumed | State engine and version in every prompt | | Faster reads, slower writes | Too many indexes | Measure insert time, drop redundant indexes after review | | Advice is generic | No plan or schema supplied | Paste the plan and table definition |

## Review checklist

- Engine and version stated and matched by the syntax.

- Plan captured before and after, on realistic data.

- Results proven identical by a comparison query.

- No production DDL or data changes run unchecked.

- Write overhead and index size measured.

- Parameterised queries only; no sensitive data pasted.

## FAQ

### Can Tabnine see my database?

Only what you paste or what your IDE context provides. Check your plan's privacy and context settings.

### Should I trust suggested index sizes or speedups?

No. Measure on your own data.

### Does this work for MySQL or SQL Server?

The structure does; swap the engine, version and tools (for example different plan commands).

### Is it safe to run generated SQL?

Not unchecked. Read it, test on staging and use transactions or backups where appropriate.

### How is this different from a DBA review?

It speeds up diagnosis, but a DBA is still valuable for critical systems.

## Conclusion

The best query tuning prompts supply schema, plan and constraints, then ask for evidence-backed hypotheses. For related database work see [GitHub Copilot prompts for database migration scripts](https://promptsio.com/github-copilot-prompts-database-migration-scripts/) and [unit test generation prompts](https://promptsio.com/bolt-new-prompt-examples-unit-test-generation/).

---
Published by Promptsio. Canonical version: https://promptsio.com/tabnine-prompts-sql-query-optimization/