SQL Query Optimization: The Techniques That Actually Matter (2026)

The Night My Dashboard Took 40 Seconds to Load

In 2023, deep into building Clickly — my Bitly-style URL shortener that I over-engineered into a graveyard — I opened the analytics dashboard one night and watched the spinner turn. And turn. I counted. Roughly forty seconds to render a table of maybe two hundred links with their click counts. On a database with, generously, a few thousand rows.

My first instinct was pure broke-engineer panic: my database can’t handle it, I need a bigger instance. I actually sat there pricing out a beefier compute tier at 1 AM, doing the math on how much a real analytics engine would cost me, feeling that familiar dread of paying more for a product with zero users. I’d already gone down the entire advanced-database rabbit hole — ClickHouse, Timescale, the works — convinced the engine was the problem.

It wasn’t. The problem was that my code was firing 201 separate SQL queries to draw one screen, and not one of the columns it filtered on had an index. I didn’t need a bigger server. I needed to understand what my queries were actually doing. That’s the whole post.

---

You Don’t Need a Bigger Server, You Need an Index

Here’s the thesis, and it’s the thing I wish someone had grabbed me by the collar and said in 2023: when a query is slow, the answer is almost never more hardware. Throwing a bigger PostgreSQL instance at a badly-written query is like buying a faster car to fix the fact that you’re driving in circles. You’ll pay more every month and the query will still be doing something dumb, just slightly faster.

Ninety percent of the “my SaaS is slow” problems I’ve seen — mine included — come down to a handful of fixable mistakes. A missing index. A query that runs once per row instead of once. Pulling back a megabyte of data to show a number. These are code problems, and code is free to fix. Hardware is a subscription.

Everything below runs on your existing free-tier database — I’m assuming Postgres on Neon, the answer every guide here lands on — before you even think about scaling up.

---

Read the Plan Before You Touch Anything

You cannot optimize what you can’t see. The single most important habit in this entire post is: before you change a query, ask Postgres how it’s running it. That’s what EXPLAIN is for.

EXPLAIN SELECT * FROM clicks WHERE link_id = 42;

EXPLAIN shows you the plan — the strategy Postgres intends to use — with cost estimates. It doesn’t actually run the query. The word you’re hunting for is Seq Scan (sequential scan): that means Postgres is reading every single row in the table to find your matches. On a table with a hundred rows, fine. On a table with two million, that’s your forty-second spinner.

But estimates lie. To see the real numbers, add ANALYZE:

EXPLAIN ANALYZE SELECT * FROM clicks WHERE link_id = 42;

This actually executes the query and reports true row counts and timings for each step. (Careful: on an INSERT, UPDATE, or DELETE, EXPLAIN ANALYZE really does write the data — wrap it in a transaction you roll back.) The two lines you read first:

• Execution Time — the real wall-clock cost at the bottom.
• The gap between estimated rows and actual rows on each node. When Postgres expects 5 rows and hits 50,000, its whole plan was built on a bad guess, and that’s usually where your problem lives.

The raw output is a nested cave of text. When my eyes glaze over, I paste the plan into explain.dalibo.com (the free Postgres Explain Visualizer) — it draws the plan as a tree and colors the expensive nodes red so the villain is obvious. If you want it to actually suggest fixes, pgMustard turns a plan into a ranked list of “add an index here, your row estimate is off there.” Start with the free one; you’ll rarely need more.

The discipline is simple: measure, change one thing, measure again. Never tune by vibes.

---

Indexes: The 20% That Fixes 80%

If you take one technique from this post, take this one. An index is a lookup structure — think the index at the back of a textbook. Without it, finding “every click for link 42” means reading the whole book cover to cover (Seq Scan). With it, Postgres jumps straight to the page.

My Clickly clicks table had a link_id column that every analytics query filtered on, and no index on it. One line fixed the worst of my forty seconds:

CREATE INDEX idx_clicks_link_id ON clicks (link_id);

Re-run EXPLAIN ANALYZE and the Seq Scan becomes an Index Scan. On a real table that’s the difference between reading two million rows and reading fifty. The rule of thumb: index the columns you filter (WHERE), join (ON), and sort (ORDER BY) on. Foreign keys especially — Postgres does not index them for you automatically, which surprises everyone. (If “foreign key” is fuzzy, here’s the plain-English schema guide.)

When one query filters on two columns together, a composite index beats two separate ones:

CREATE INDEX idx_clicks_link_time ON clicks (link_id, created_at DESC);

Column order matters — that index serves WHERE link_id = 42 ORDER BY created_at DESC beautifully, because it matches how you actually ask the question. (Adding an index to a live table is a schema change, so you ship it the same disciplined way as any other — through a versioned migration, not a hand-typed CREATE INDEX in production at midnight.)

But indexes are not free, and this is where beginners overcorrect. Every index you add is a copy of that data the database has to update on every INSERT, UPDATE, and DELETE. Index every column “just in case” and you’ve quietly made all your writes slower and your storage bigger. Indexes earn their keep on columns you read by, on tables that are read far more than written. Add them deliberately, driven by a slow query you actually saw in EXPLAIN — not speculatively. That’s the whole discipline: the right index, not every index.

---

Kill the N+1 Query (The ORM Classic)

This was the other half of my forty seconds, and it’s the most common performance bug in modern SaaS, full stop. It hides inside your ORM — Prisma, Drizzle, whatever — where the SQL is invisible, so you never see it happening.

Here’s the shape. My dashboard did this:

const links = await db.link.findMany({ where: { userId } }); // 1 query
for (const link of links) {
  link.clickCount = await db.click.count({ where: { linkId: link.id } }); // 1 query, EACH
}

Read that loop. One query to fetch 200 links, then one more query per link to count its clicks. That’s 1 + 200 = 201 queries to draw one table. Each is fast on its own, but the round-trip to the database — especially serverless, especially across a network — has overhead, and 201 of them stack into seconds. That’s the “N+1”: one query to get the list, then N queries for the details.

The fix is to ask the database once. In raw SQL it’s a single grouped query:

SELECT link_id, COUNT(*) AS clicks
FROM clicks
WHERE link_id = ANY($1)
GROUP BY link_id;

In an ORM it’s usually a join or an “include”/“with” that eager-loads the relation in one round trip — include: { _count: { select: { clicks: true } } } in Prisma, or a with clause in Drizzle. The details differ; the principle doesn’t: one query that returns everything, not a query in a loop. My dashboard went from 201 queries to 2, and from forty seconds to well under a second, and I hadn’t touched the hardware at all.

The scary part: you won’t notice N+1 in development, where you have twelve rows and the database is on localhost. It detonates in production, the moment you get users. So turn on your ORM’s query logging early and watch the count. If drawing one page fires fifty queries, you have an N+1.

---

Stop Selecting Everything

SELECT * is a reflex, and it’s a lazy one. It tells the database to haul back every column of every matching row — including that giant jsonb metadata blob and the raw_user_agent text field you don’t even show on this screen. You pay for that in disk reads, in network transfer, and in memory.

-- Lazy: drags back everything, including columns you ignore
SELECT * FROM clicks WHERE link_id = 42;

-- Deliberate: only what the screen needs
SELECT id, created_at, country FROM clicks WHERE link_id = 42;

Selecting only the columns you need is a free speedup, with a bonus most people miss: it unlocks covering indexes. If your index already contains every column a query asks for, Postgres answers straight from the index and never touches the table — an “Index Only Scan,” the fastest thing there is. SELECT * can’t do that, because the table has columns the index doesn’t. Ask for less, get more.

The same restraint applies to rows. Always LIMIT a list that feeds a UI, and paginate. Nobody scrolls past row 50; don’t fetch two million to show them ten.

---

Don’t Wrap Your Indexed Columns in Functions

This one’s sneaky, and it silently un-does the index you were so proud of. The moment you wrap an indexed column in a function inside your WHERE clause, Postgres can’t use the plain index anymore — it would have to run the function on every row to check, which is a Seq Scan in disguise.

The classic case is case-insensitive email lookup:

-- Index on `email` is useless here — LOWER() runs on every row
SELECT * FROM users WHERE LOWER(email) = 'shubham@example.com';

Two clean ways out. Either build an expression index that matches exactly what you query:

CREATE INDEX idx_users_lower_email ON users (LOWER(email));

…or store the email lowercased in the first place and compare directly. Same trap shows up with dates: WHERE DATE(created_at) = '2026-07-14' kills the index on created_at. Rewrite it as a range the index can use:

SELECT * FROM clicks
WHERE created_at >= '2026-07-14' AND created_at < '2026-07-15';

Rule of thumb: keep the indexed column bare on the left side of the comparison, and do the transforming on the other side (the constant), not on the column.

---

Connection Pooling: The One That Bites Serverless

Everything so far has been about individual queries. This last one is about how you connect, and it’s the technique that ambushes people the day they deploy to Vercel or any serverless platform.

A Postgres connection is expensive to open, and every server has a hard ceiling on how many exist at once — a small Neon compute caps direct connections around 100. That’s plenty for a normal server holding a few steady connections. But serverless functions don’t do that: every invocation can spin up its own fresh connection, and under a spike you get hundreds of functions each grabbing one. You blow past the limit, and instead of slow queries you get failed ones — too many connections — and your app just falls over.

The fix is a connection pooler — a piece of software that sits between your app and the database, keeps a small set of real connections warm, and hands them out as needed. PgBouncer is the standard, and the good news is you usually don’t install anything: Neon runs PgBouncer in front of every database in transaction mode, letting thousands of clients share a tiny pool of real Postgres connections. You just have to use it — connect to the pooled connection string (the one with -pooler in the host), not the direct one.

That’s the whole fix for most people: use the pooled endpoint. Self-hosting Postgres on a VPS means running PgBouncer yourself; on a managed host like Neon it’s a one-line change to your DATABASE_URL. Skip it and your app tests perfectly, then dies the first time real traffic hits — a miserable way to learn about connection limits. (This is the kind of infra plumbing I argue should be the 1% of your work that hums in the background.)

---

The Bottom Line

I spent that night in 2023 pricing bigger database instances to solve a problem that two lines of SQL fixed for free. That’s the lesson, and it’s cheaper than the one I almost paid for: before you scale hardware, look at your queries. They’re almost always the problem, and they’re almost always fixable without spending a rupee more.

The order I’d work in, every time: run EXPLAIN ANALYZE and find the Seq Scan. Add the one index that kills it. Check your ORM’s query log for N+1 and collapse the loop into a single query. Stop selecting columns and rows you don’t use. Keep functions off your indexed columns. And the day you go serverless, connect through the pooler. Do those six things and you’ll have a fast app on a free-tier database — exactly where a broke founder wants to be.

A bigger server is the last thing you reach for, not the first. Most of us never need it. We just needed an index and the humility to read the plan first.

This is the Broken Engineer Guide — I over-engineer everything, fail at business, and hand you the two-line fix I found at 2AM so you don’t lose the whole night. Now go run EXPLAIN on your slowest query.