Skip to content

Why AI-generated SQL is slow

Last updated

View as Markdown

Quick answer: AI writes SQL that is locally correct against the code it can see, but blind to the database it cannot see: the indexes, row counts, data distribution, and query plans. So it ships N+1 loops, filters on unindexed columns, SELECT *, unbounded results, and functions that disable indexes. The code reads cleanly. The slowness comes from facts about the database that the model never had.

Performance depends on the database, not just the source. Your coding agent can read your schema and your query, but it does not see:

  • which columns are indexed,
  • how many rows the table holds,
  • the distribution of your data (is status = 'pending' rare or half the table?),
  • or the query plan the planner will choose.

Every performance bug below is a special case of that blindness. The AI writes code that would be correct against a small, evenly distributed, fully indexed table, and then it meets production.

The agent loads a list, then accesses a related object in a loop, and the ORM fires one query per row. The code looks fine (for post in posts: post.author), and this is a common ORM performance bug. See how to find N+1 queries.

2. Filtering or joining on unindexed columns

Section titled “2. Filtering or joining on unindexed columns”

WHERE customer_id = $1 can be fast with an index and can require a full table scan without one. The agent has no idea which of your columns are indexed, so it writes the natural predicate. On a big table that becomes a Seq Scan; see reading an EXPLAIN plan.

3. SELECT * instead of the columns you need

Section titled “3. SELECT * instead of the columns you need”

SELECT * pulls every column, moves more data than the app uses, and can prevent index-only scans. The agent defaults to it because it does not know which columns matter.

Fetching an unbounded result set is fine on 100 rows and a memory problem on 10 million. “Get the users” becomes SELECT * FROM users with no ceiling.

5. Functions that disable the index (non-sargable predicates)

Section titled “5. Functions that disable the index (non-sargable predicates)”

This one is easy to miss. A predicate like:

WHERE lower(email) = 'a@b.com' -- index on email is NOT used
WHERE created_at::date = '2026-01-01' -- index on created_at is NOT used

wraps an indexed column in a function, so the planner cannot use the plain index and falls back to a sequential scan. The fix is to index the expression the query uses:

CREATE INDEX ON users (lower(email));

Or rewrite the predicate so the raw column is compared against a range, leaving it sargable:

SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2026-01-02';

Leading-wildcard LIKE '%foo' has the same problem: a plain B-tree index cannot serve it.

None of it is visible in the code. Whether a column is indexed, how large the table is, and whether the planner picks the index are runtime facts about your specific database. The model reproduces patterns from its training data (lots of SELECT *, lots of lazy-loading tutorials) and writes code that is idiomatic and locally right. The more SQL your team generates with AI, the more of these ship, because the AI is optimizing code it can see against a database it cannot.

  • Review new queries against the schema and indexes as well as for correctness.
  • Rank real load with pg_stat_statements and pull plans for the top offenders.
  • Give whoever writes your SQL, whether human or agent, the database context it needs to reason about performance.
  • Why is AI-generated SQL often slow?

    Because performance is a property of the database, not the code. An AI coding agent sees the schema and the query text but not the indexes that exist, the number of rows, the data distribution, or the query plan. It writes code that is locally correct and idiomatic, and the slowness only appears when that code meets real data.

  • What are the most common performance bugs in AI-written SQL?

    N+1 query loops, filtering or joining on unindexed columns, SELECT * instead of the columns needed, queries with no LIMIT, and wrapping an indexed column in a function (like lower(email)) which stops the planner from using the index.

  • Why does wrapping a column in a function make a query slow?

    A predicate like WHERE lower(email) = $1 is not sargable against a plain index on email, so the planner falls back to a sequential scan. You either index the expression (CREATE INDEX ON users (lower(email))) or rewrite the predicate to compare the column directly.

  • Does using an AI coding agent mean my SQL will be slow?

    Not if the agent has database context. The code is fine; the missing piece is the database, indexes, row counts, plans, and statistics. Give the agent that context and it can catch the same issues a senior engineer would in review.