Skip to main content
Stackbastion

Draft, not yet published

Fixing the missing indexes in an AI-generated database schema

7 July 2026· 5 min read · by Stackbastion

Your app was snappy in testing. Now it has real data and some pages take five seconds to load, or time out entirely. You didn’t change the code. What changed is the size of your tables, and the reason it hurts is almost always the same: the AI tool that built your schema never added indexes. Here’s how to find the missing ones and add them.

Why this happens

AI coding tools generate database schemas that are correct but not tuned. They create your tables and columns, they usually index the primary key, and then they stop. Indexes on the columns you actually filter and join by, the ones that make queries fast, rarely get added.

An index is like the index at the back of a book. Without one, Postgres finds matching rows by reading the entire table top to bottom, every single query. That’s called a sequential scan. At 100 rows it’s instant, so it looks fine while you build. At 100,000 rows the same query reads all 100,000 rows to find the 3 you asked for, every time, and now the page hangs.

The fix is to tell Postgres where to look. Two steps: find the slow queries, then index the columns they filter on.

How to fix it

Step 1: find the slow query

First identify what’s actually slow. Turn on Postgres’s slow-query log to catch anything over, say, 500ms:

-- log any query slower than 500ms
ALTER SYSTEM SET log_min_duration_statement = 500;
SELECT pg_reload_conf();

Or, if you have the pg_stat_statements extension, ask directly for your worst queries:

SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Step 2: run EXPLAIN ANALYZE

Take a slow query and put EXPLAIN ANALYZE in front of it. This makes Postgres run the query and tell you exactly how it did it:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 8842 ORDER BY created_at DESC;

Read the output for two words: Seq Scan. That means Postgres read the whole table. Here’s the smell:

Seq Scan on orders  (cost=0.00..18542.00 rows=3 width=64)
  Filter: (user_id = 8842)
  Rows Removed by Filter: 199997
Planning Time: 0.1 ms
Execution Time: 842.3 ms

“Rows Removed by Filter: 199997” is the tell. Postgres examined 200,000 rows to return 3. It filtered on user_id but had no index on it, so it had no choice but to check every row.

Step 3: add the index

Create an index on the column you filter by:

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Use CONCURRENTLY on a live database. It builds the index without locking the table, so your app keeps serving while it works. Without it, the table is locked for writes until the build finishes.

Now rerun the same EXPLAIN ANALYZE. You want to see Index Scan and a far smaller execution time:

Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=3 width=64)
  Index Cond: (user_id = 8842)
Execution Time: 0.3 ms

842ms down to 0.3ms. Same query, one index.

Step 4: index for the whole query, not just one column

That query also does ORDER BY created_at DESC. If you both filter by user_id and sort by created_at, a composite index on both, in that order, lets Postgres do the filter and the sort in one pass:

CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders (user_id, created_at DESC);

The rule: index the columns in the order you filter then sort. Equality filters first (user_id = ...), then the sort column (created_at).

Step 5: the usual suspects to index

You don’t need to index everything. Focus on:

  • Foreign keys you join on (user_id, post_id, org_id). AI tools almost never index these, and joins on them are your most common slow query.
  • Columns in WHERE clauses you use often (a status, an email for lookups).
  • Columns you ORDER BY on large tables.

Don’t index columns you never filter on, tiny tables, or every column “just in case.” Every index costs write speed and disk, so add them where a real query needs them, not by default.

Or, we do it for you

We run EXPLAIN ANALYZE across a customer’s slowest queries as part of onboarding and add the indexes their AI tool skipped, usually turning multi-second pages instant in an afternoon. If your app got slow as it grew, book a free audit and we’ll find the sequential scans.

FAQ

How do I know which queries are slow without special tools?

Turn on log_min_duration_statement as shown above and watch your logs while you use the app. Anything that shows up is a candidate. It’s the cheapest way to find slow queries with no extra setup, and it points you straight at what to run EXPLAIN ANALYZE on.

Will adding indexes break anything?

Adding a read index doesn’t change your data or results, only the speed. The one real cost is that each index slightly slows down writes (inserts and updates), because the index has to be updated too. That tradeoff is worth it for foreign keys and common filters; it’s not worth it for columns nobody queries.

What does CONCURRENTLY actually do?

It builds the index without taking a write lock on the table, so your app keeps working during the build. The normal CREATE INDEX locks the table against writes until it finishes, which on a big table can mean minutes of downtime. Always use CONCURRENTLY on a live database.

My table is huge and the index build is taking forever. Is that normal?

Yes, building an index on millions of rows takes time, and CONCURRENTLY is slower still because it’s careful not to block. Let it finish; it’s running in the background and not blocking your app. If it fails partway, drop the invalid index and rerun. Do big builds during low-traffic hours when you can.

Related posts