A slow endpoint is very often a slow query wearing an application's clothes — the fix rarely lives in the application code at all, it lives in an EXPLAIN output nobody's looked at yet.
Database query optimization is the practice of identifying and fixing slow queries — through proper indexing, query restructuring, and understanding how the database's query planner actually executes a given query. The starting point for almost all of it is EXPLAIN ANALYZE (or your database's equivalent), which shows exactly what the database is doing, not what you assume it's doing.
Why Query Optimization Matters (and When to Skip It)
Application-level performance work (caching, code splitting, CDN tuning) has diminishing returns if the underlying database queries themselves are slow — a query taking 2 seconds will make an endpoint feel slow regardless of how well-optimized everything else around it is. Query optimization is often the single highest-leverage performance fix available, because a missing index can be a 100x+ improvement in one change.
Skip deep query optimization work for queries that are already fast and infrequently run — profiling effort is best spent on queries that are both slow and frequently executed, not on theoretical worst-case queries that rarely happen in practice.
Getting Started with Database Query Optimization
Reading a query plan in Postgres:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
A sequential scan on a large table is usually the signal an index is missing:
Seq Scan on orders (cost=0.00..18334.00 rows=1 width=120) (actual time=45.2..312.8 rows=3 loops=1)
Filter: (user_id = 123 AND status = 'pending')
Adding an index and re-checking the plan:
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- now shows Index Scan instead of Seq Scan, with dramatically lower actual time
Core Database Query Optimization Concepts Every Developer Should Know
A sequential scan on a large table is the most common and most fixable performance problem, meaning the database is reading every row instead of using an index to jump directly to matching rows. Reading EXPLAIN output specifically for Seq Scan on tables with meaningful row counts is often the fastest path to finding your highest-impact fix.
Composite indexes need column order matching your query patterns. An index on (user_id, status) supports queries filtering on user_id alone or user_id AND status, but not efficiently on status alone — index column order should generally match your most selective, most-queried filter first.
N+1 queries are an application-pattern problem, not a missing-index problem. Fetching a list, then querying for related data per item in a loop, generates one query per item instead of one query total. Fix with eager loading (a JOIN, or a batched WHERE id IN (...) query) rather than trying to index your way out of it.
// N+1: one query per order
for (const order of orders) {
order.customer = await db.customers.findById(order.customerId);
}
// fixed: one batched query
const customerIds = orders.map(o => o.customerId);
const customers = await db.customers.findMany({ where: { id: { in: customerIds } } });
Over-indexing has a real cost. Every index speeds up reads matching it but slows down writes (every insert/update needs to update every relevant index) and consumes storage — indexing every column "just in case" is not free.
Common Database Query Optimization Mistakes and How to Fix Them
Mistake 1: adding indexes without checking EXPLAIN first, guessing at what's slow. This can add write overhead without actually fixing the real bottleneck. Fix: always profile with EXPLAIN ANALYZE before adding an index, confirming it targets the actual slow query pattern.
Mistake 2: N+1 query patterns from ORMs that lazily load related data by default. This is a very common source of surprisingly slow endpoints that don't show up as a single obviously slow query. Fix: use eager loading / batched queries for any list endpoint that accesses related data.
Mistake 3: selecting more columns/rows than actually needed (SELECT * on wide tables, or fetching full result sets when only a count or a page is needed). Fix: select only needed columns and use pagination/limits appropriately.
When Should You Optimize Queries Instead of Adding a Cache?
Optimize the query itself when the underlying data changes frequently or correctness requires up-to-date results — caching a slow query just delays when users hit the slowness, and masks a fixable root cause. Add caching on top of an already-optimized query when the data is read far more often than it changes and slight staleness is acceptable — the two approaches are complementary, not substitutes for each other.
Database Query Optimization in Production
Monitor slow query logs continuously rather than only investigating when a specific complaint comes in — many slow queries degrade gradually as table size grows and won't be obvious until they've already become a real problem. Also re-run EXPLAIN ANALYZE periodically on your most critical queries as data volume grows, since a query plan that was fine at 10,000 rows can degrade significantly at 10 million.
If you haven't checked your slow query log recently, that's the fastest way to find your next highest-impact fix — start there before guessing at what might be slow.