Database Query Optimization: The 5 Fixes That Solve 80% of Slow Queries

Database optimization sounds like it requires years of DBA experience. For 80 percent of slow queries we encounter, the fixes are straightforward. We have optimized performance on twenty-six projects, mostly PostgreSQL. Five fixes appear over and over.

Fix one: add the missing index. About 40 percent of cases. A developer queries a column without an index. Works fine with a thousand rows. At five hundred thousand rows, PostgreSQL does a sequential scan and the query takes three seconds. Diagnostic: EXPLAIN ANALYZE, look for "Seq Scan" on large tables with filter conditions. Most commonly missed: foreign key columns (ORMs create constraints but often skip indexes), WHERE clause columns added post-launch, ORDER BY columns on large result sets.

A specific ORM pattern: SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10. Without a composite index on (user_id, created_at DESC), PostgreSQL scans all matching rows and sorts. With the composite index, it jumps directly to the answer. Difference on a million rows: 500ms versus 2ms.

Fix two: eliminate N+1 queries. About 25 percent. Fetch ten orders, then query the customer for each individually. Eleven queries instead of two. Diagnostic: enable query logging, look for repeated similar queries with different parameters. Fix: eager loading (include in Prisma, joinedload in SQLAlchemy). We cut one API response from 2.4 seconds to 80ms by adding a single include.

Fix three: select only needed columns. About 10 percent. ORMs default to SELECT *, transferring large text or JSONB columns unnecessarily. A hundred-row query shrinks from 5MB to 200KB by specifying five columns instead of fifty.

Fix four: add pagination to unbounded queries. About 8 percent. An "all users" admin page works with ten users and chokes at ten thousand. Fix: cursor-based pagination for lists, date-range requirements for analytics.

Fix five: cache expensive computations. The rest are inherently expensive queries -- complex aggregations, multi-table joins. Fix: materialized views refreshing on schedule, Redis caching with appropriate TTLs, or offloading search to Typesense.

Diagnostic workflow: identify the slow query, run EXPLAIN ANALYZE, check these five fixes in order. One of them is the culprit 83 percent of the time.