optimize-a-query

Optimize a query

Measure, don't guess. A slow query is almost always a sequential scan that should be an index scan, or an ORM that fired N queries where one would do. Both are visible — you just have to look.

When to use

• An endpoint/page is slow and you've traced it to a specific query.
• A query times out or pg_stat_statements shows it as a top consumer.
• Fixing the query needs a new/changed index → do the index work in design-a-schema.

1. Read the plan

Run the actual query with a plan (via psql against your DATABASE_URL, or Drizzle's logger):

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

What to look for:

• Seq Scan on a big table where you filter/join → missing index. The fix is an index (usually on the FK or the filtered column), not a query rewrite.
• Rows Removed by Filter ≫ rows returned → the index isn't selective, or a partial index would help.
• Estimated rows wildly off actual → stale stats: ANALYZE <table>; (autovacuum usually handles this).
• Nested Loop over many rows → often the ORM N+1 below, not a single bad query.

2. Catch the ORM N+1

The most common "slow page" in this stack isn't one slow query — it's many fast ones. Loading a list and then fetching each row's relation in a loop fires 1 + N queries.

• Symptom: the same short query in the logs, repeated with different ids.
• Fix: use Drizzle's relational query with with (one join-backed query), or a single inArray(...) batch — never a for loop of db.query.

// ❌ N+1
for (const p of posts) p.author = await db.query.user.findFirst({ where: eq(user.id, p.authorId) });
// ✅ one query
const rows = await db.query.posts.findMany({ with: { author: true } });

3. Find the hot queries you didn't know about

pg_stat_statements ranks queries by total time (Neon supports it — CREATE EXTENSION IF NOT EXISTS pg_stat_statements;):

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