---
title: How to Optimize a SQL Query with AI (Postgres and Beyond)
url: "https://goremy.ai/blog/articles/write-explain-and-tune-sql-queries-with-ai"
author: Luis Chavez-Mattos
editor: Luis Chavez-Mattos
editor_title: Director, Product
published: "2026-11-16T22:00:16.588Z"
updated: "2026-11-16T22:00:16.588Z"
topic: SQL and Data Analysis
description: Use AI to write, explain and speed up SQL queries. Copy-paste prompts, a Postgres slow query checklist, and what to verify before you run anything.
---

# How to Optimize a SQL Query with AI (Postgres and Beyond)

> Use AI to write, explain and speed up SQL queries. Copy-paste prompts, a Postgres slow query checklist, and what to verify before you run anything.

Edited by Luis Chavez-Mattos, Director, Product · Published 2026-11-16

## How do you optimize a SQL query with AI?

Give the AI four things: the query, the table definitions (including indexes), rough row counts, and the execution plan from your database. In Postgres that plan comes from `EXPLAIN (ANALYZE, BUFFERS)`. Ask the AI to explain what the plan shows, name the slowest step, and propose one change at a time. Then test each change on a copy of the data, compare timings, and confirm the results are identical. AI is good at reading plans and spotting known patterns. It cannot see your data, so you have to measure.

## TL;DR

- **Paste the plan, not just the query.** Without the execution plan, an AI is guessing about why a query is slow.
- **Describe the tables and indexes** (the `CREATE TABLE` statements and row counts) so suggestions match your real schema instead of invented columns.
- **Change one thing at a time** and re-run the plan after each change, so you know which fix did the work.
- **Check that the results match** before and after. A faster query that returns different rows is a bug.
- **Never run AI-written `UPDATE`, `DELETE` or `CREATE INDEX` statements on production first.** Use a copy, or a transaction you can roll back.
- **Analysts get the most from AI on writing and explaining SQL,** while DBAs and ERP staff get the most from plan reading and index suggestions. The same prompts work for all three.

## What should you give the AI before you ask for help?

Most bad AI answers about SQL come from missing context. A model asked "make this faster" will produce generic advice (add an index, avoid `SELECT *`). A model given your schema and plan will say something specific.

| Give it | Why it matters | How to get it |
|---|---|---|
| Database and version | Syntax and features differ between Postgres, SQL Server, Oracle and MySQL | Say it in the prompt, for example "PostgreSQL 15" |
| The query | The thing being tuned | Copy from your editor or logs |
| Table definitions | Stops the AI inventing column names | `\d tablename` in psql, or export the DDL from your tool |
| Existing indexes | Avoids suggesting an index you already have | Included in `\d tablename` output |
| Approximate row counts | An index helps on 50 million rows and is noise on 500 | `SELECT count(*)` or table statistics |
| The execution plan | Shows where time is actually spent | `EXPLAIN (ANALYZE, BUFFERS)` |
| The goal | "Return the same result, faster" or "make this report show X" | Your own words |

**A note on sensitive data.** Table structures and plans are usually fine to share, but check your company's AI policy first. Plans and queries can contain literal values such as customer names or account numbers in `WHERE` clauses. Replace them with placeholders before pasting. Never paste result rows containing personal or financial data into a public AI tool. If your company provides an approved AI tool, use that one.

## How do you write a SQL query with AI (for business analysts)?

If you know the business question but not the exact joins, AI is a strong first-draft writer. The key is to describe the tables the way you would to a new colleague.

**Prompt to copy:**

```
You are helping me write a SQL query for PostgreSQL 15.

Business question: Which customers placed more than 3 orders in the last 90 days, and what was their total spend?

Tables:
customers(id, name, region, created_at)
orders(id, customer_id, total, status, created_at)

Notes:
- Only count orders where status = 'completed'.
- "Last 90 days" means from today minus 90 days up to now.
- Return customer id, name, order count, total spend, sorted by spend descending.

Write the query. Then list every assumption you made, and tell me
two ways the result could be wrong.
```

The last line matters. Asking for assumptions and failure modes makes the model surface things like "I assumed `total` includes tax" or "customers with the same name will be shown as separate rows."

**What to check in the answer:**

- **Every column and table exists.** Models sometimes invent a `order_date` column when yours is `created_at`.
- **Join fan-out.** If you join orders to order lines and then sum `orders.total`, each order's total is counted once per line. Totals come out too high. This is the most common silent error in analyst SQL.
- **NULL handling.** Rows with NULLs can disappear from filters and counts without any error.
- **Date boundaries.** "Between January 1 and March 31" with a timestamp column often drops March 31 entirely. Safer is `>= '2025-01-01' AND < '2025-04-01'`.
- **Reconcile against a number you trust.** Compare the total with a finance report or a known dashboard figure. If they differ, find out why before you share the result.

The business definitions (what counts as an "active customer," a "completed" order, a "region") come from you, not the model. Put them in the prompt.

## How do you get AI to explain a SQL query you inherited?

ERP staff and analysts inherit long queries all the time: 200 lines, five levels of subqueries, no comments. AI is very good at turning that into plain English.

**Prompt to copy:**

```
Explain this PostgreSQL query to someone who understands the business
but not SQL. Do this:

1. One-paragraph summary of what it returns.
2. Step-by-step walkthrough, in the order the database logically
   processes it (FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY).
3. A list of the tables used and what each contributes.
4. Anything that looks risky: filters that might drop rows, joins that
   might duplicate rows, hardcoded values, unclear logic.

Query:
[paste query]
```

Then compare the summary against what you know the report is supposed to show. If the AI says "this returns open orders" and the report is called "Shipped Orders," you have found something worth a conversation. Ask follow-ups too: "Why is there a LEFT JOIN on line 34 and does the WHERE clause cancel it out?" That is a classic bug: a `LEFT JOIN` followed by a `WHERE` condition on the right-hand table silently turns it into an inner join.

## How do you troubleshoot a slow Postgres query?

This is the workflow for DBAs, IT staff and anyone with access to a Postgres database. Do these steps in order.

1. **Find the real offender.** Do not tune what people complain about before checking what is actually expensive. If the `pg_stat_statements` extension is enabled, it shows which statements consume the most total time and how often they run. You can also set `log_min_duration_statement` to log any query slower than a threshold. A query that takes 2 seconds and runs 50,000 times a day usually matters more than one that takes a minute and runs weekly.
2. **Reproduce it somewhere safe.** Use a staging copy or a restored backup. Slow queries often depend on data volume, so a tiny test database will mislead you.
3. **Get the plan.** Run `EXPLAIN (ANALYZE, BUFFERS) your_query;`. Be careful: `ANALYZE` actually executes the statement. For anything that writes data, wrap it: `BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE ...; ROLLBACK;`.
4. **Look for the usual signs yourself first.**
   - A `Seq Scan` on a large table where you expected an index to be used.
   - A big gap between estimated rows and actual rows. This points to stale or missing statistics.
   - A `Nested Loop` running an inner step many thousands of times.
   - A `Sort` or `Hash` that spills to disk.
   - Many shared buffer reads, meaning lots of data pulled from disk.
5. **Give the plan to the AI** with the prompt below.
6. **Apply one suggestion, re-run the plan, compare.** Keep a note of timings before and after.
7. **Confirm identical results.** Run both versions and compare row counts and totals, or use `EXCEPT` in both directions to find differences.

**Tuning prompt to copy:**

```
I'm troubleshooting a slow query on PostgreSQL [version].

Goal: return exactly the same results, faster.

Query:
[paste query]

Table definitions and existing indexes:
[paste output of \d for each table]

Approximate row counts:
[table: count]

EXPLAIN (ANALYZE, BUFFERS) output:
[paste full plan as text]

Please:
1. Explain in plain language what the plan is doing, step by step.
2. Identify the single step that costs the most time and why.
3. Suggest up to three changes, ranked by expected impact. For each:
   the exact SQL, what it should change in the plan, and any downside
   (write overhead, storage, lock risk).
4. Tell me what to look for in the new plan to confirm it worked.
5. Say what you cannot determine from this information.
```

Point 5 is not decoration. A good answer will say things like "I can't tell whether statistics are stale without checking when the table was last analyzed," which tells you what to check next.

## What fixes will AI suggest, and which are safe?

Most suggestions fall into a handful of categories. Here is how to judge them.

| Suggested fix | When it helps | Watch out for |
|---|---|---|
| Add an index | Plan shows a sequential scan filtering out most rows | Slows writes and uses storage. In Postgres, `CREATE INDEX CONCURRENTLY` avoids blocking writes but takes longer. Column order in multi-column indexes matters |
| Stop wrapping columns in functions in `WHERE` | `WHERE DATE(created_at) = ...` or `WHERE LOWER(email) = ...` prevents a normal index from being used | Rewrite as a range, or create an expression index. Check results stay identical |
| Select only needed columns | Wide tables, large text columns | Rarely a big win alone, but reduces data moved |
| Replace a correlated subquery with a join or window function | Subquery runs once per row | Joins can duplicate rows. Verify counts |
| Run `ANALYZE` on the table | Estimated and actual row counts are far apart | Cheap, but confirm autovacuum settings are not the real problem |
| Keyset pagination instead of deep `OFFSET` | Paging far into a large result | Needs a stable sort key and changes how the app pages |
| Filter earlier or pre-aggregate | Joining huge tables before filtering | Make sure the logic is equivalent |
| Partitioning, materialized views, config changes | Very large tables, heavy repeated reports | Big changes with real operational cost. Treat an AI suggestion here as a starting point for a design discussion, not a fix to apply |

A good rule: the first three or four rows are low-risk and reversible. The last row needs a human who owns the database to decide.

## What does a worked example look like?

Say an analyst's quarterly revenue report has become slow. Here is the original:

```sql
SELECT c.name, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE DATE(o.created_at) BETWEEN '2025-01-01' AND '2025-03-31'
  AND o.status = 'completed'
GROUP BY c.name
ORDER BY revenue DESC;
```

The plan shows a sequential scan on `orders`, which has millions of rows, filtering most of them out. You paste the query, the table definitions and the plan into the tuning prompt. A good AI response will point out two separate things:

1. **A performance problem.** `DATE(o.created_at)` applies a function to the column, so a plain index on `created_at` cannot be used. The rewrite:

```sql
WHERE o.created_at >= '2025-01-01'
  AND o.created_at <  '2025-04-01'
  AND o.status = 'completed'
```

   Then an index such as `CREATE INDEX CONCURRENTLY idx_orders_created_status ON orders (created_at, status);` may help. Whether the planner uses it depends on your data, which is why you re-run the plan.

2. **A correctness problem.** `GROUP BY c.name` merges two different customers who share a name. Grouping by `c.id` and showing the name fixes it. This changes results, so it is not a pure tuning change, and you should decide on purpose whether you want it.

Notice that the second finding came from asking the model to review the query, not only speed it up. Run the plan again after the rewrite, compare the timing on your own system, and reconcile the revenue totals with the old version (accounting for the name-merging fix).

## Where should you not trust AI with SQL?

- **Invented columns, tables and functions.** If something does not exist in the schema you pasted, say so and ask again. Always run on a copy first.
- **Dialect mix-ups.** Syntax for dates, string functions, `LIMIT` versus `TOP`, and `MERGE` varies. State your database and version every time.
- **Confident speed claims.** "This will be 10x faster" is a guess. Only the new plan and your timings count.
- **Index suggestions without evidence.** Every index costs writes and storage. Ask the model which part of the plan the index changes. If it cannot say, skip it.
- **Business definitions.** The model does not know what your company means by "active," "churned" or "net revenue."
- **Destructive statements.** For `UPDATE`, `DELETE`, `TRUNCATE` or schema changes, first run the equivalent `SELECT` to see which rows would be affected, then use a transaction, and have someone who owns the data review it.
- **Hidden causes.** A query can be slow because of locks, a busy server, a bad connection pool, or bloated tables. A plan of a single query cannot show those. If the same query is fast sometimes and slow at other times, look at the system, not only the SQL.

## What is a safe checklist before you run AI-written SQL?

1. Did you state the database type and version in the prompt?
2. Does every table and column in the answer exist in your schema?
3. Did you run it on a copy, staging environment or read-only replica first?
4. For anything that writes, did you preview with `SELECT` and use a transaction?
5. Do row counts and totals match the original query or a trusted report?
6. Did you re-run `EXPLAIN (ANALYZE, BUFFERS)` and compare, rather than trust the claim?
7. Did someone who owns the database approve any new index or schema change?
8. Did you remove real customer or employee data from what you pasted?

## Can you turn a query people keep asking for into a small tool?

If the same report gets requested every week, a better fix than faster SQL may be to stop answering requests by email. With [Remy](https://goremy.ai), you can describe a small internal app in plain English, such as a request form that logs who needs which report, plus a dashboard and a scheduled job that refreshes the numbers. Remy builds the full-stack app, with its own managed database and sign-in, and deploys it to a live URL. The spec stays readable, so you can review the logic before anyone relies on it. It builds apps with their own database, so it is not a tool for tuning your existing ERP or production Postgres. For that, use the steps above.

## Frequently Asked Questions

### Can ChatGPT or another AI optimize a SQL query?

Yes, with the right input. Give it the query, the schema, row counts and the execution plan, and it can explain bottlenecks and propose rewrites or indexes. Without the plan it can only give generic advice. Always test its suggestions on a copy and compare results and timings.

### What is the fastest way to find out why a Postgres query is slow?

Run `EXPLAIN (ANALYZE, BUFFERS)` on the query in a safe environment and look for sequential scans on big tables, large gaps between estimated and actual rows, and sorts or hashes spilling to disk. Use `pg_stat_statements` first if you need to find which queries are worth tuning at all.

### Is it safe to paste my company's SQL into an AI tool?

It depends on your company's policy and the tool. Schemas and plans are usually less sensitive than data, but queries can contain literal values such as names or account numbers. Replace them with placeholders, never paste real result rows containing personal or financial data, and use the AI tool your company has approved.

### Do I need to know SQL well to use AI for it?

You need enough to check the answer. Business analysts can get a lot from AI by describing tables and questions clearly, then verifying the output against a number they trust. Ask the AI to list its assumptions, and reconcile totals before sharing any result. If the query will write or delete data, get a second person to review it.
