Slow database queries are the most common performance bottleneck in web apps. This guide covers EXPLAIN ANALYZE, finding missing indexes, N+1 query problems, and the changes that deliver the biggest performance gains.
In this guide
Prefix any slow query with EXPLAIN (ANALYZE, BUFFERS): EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20. Look for: Seq Scan on large tables (needs an index), high actual time values, large rows/loops ratios. The actual time shown is milliseconds. Anything over 100ms for a single query in a web app warrants investigation.
Create an index on columns used in WHERE, JOIN ON, and ORDER BY clauses: CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id). Use CONCURRENTLY to avoid locking the table during index creation on production. For queries that filter and sort: CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC). Composite indexes must match the column order used in queries to be effective.
An N+1 problem happens when you run one query to fetch N records, then N more queries to fetch related data. Example: fetching 100 orders then querying each order's user separately = 101 queries. Fix with a JOIN: SELECT orders.*, users.name FROM orders JOIN users ON orders.user_id = users.id WHERE orders.status = 'pending'. In ORMs, use eager loading: include('user') in Sequelize, with('user') in Prisma. N+1 can turn a 10ms page load into 2 seconds.
Every database connection consumes ~5-10MB of RAM on the PostgreSQL server. Without pooling, a web app creates a new connection per request — fine for 10 users, catastrophic for 1,000. Use PgBouncer for connection pooling at the infrastructure level, or set pool size in your ORM: Prisma datasource db { url = "...?connection_limit=10" }. The optimal pool size is roughly (2 × num_cores) + 1.
Enable the pg_stat_statements extension: CREATE EXTENSION pg_stat_statements. Then query it: SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20. This shows your most expensive queries ranked by total time consumed — the ones actually hurting production performance, not just the ones that are slow in isolation. Focus optimization effort here first.
Need Help?
Our engineering team handles implementations like this every week. Get a free scoping call — we will tell you exactly what it takes and what it costs.
Book a free callCompetitive Intelligence
Efficiency Modeling
© 2026 NexWorldTech — Built for Global Dominance.