SQL Query Optimization Tips Every Developer Should Know

Slow SQL queries are one of the most common performance bottlenecks in web applications. This guide covers practical optimization techniques that work across PostgreSQL, MySQL, and SQLite — from indexing strategies to reading EXPLAIN output to eliminating the N+1 problem.

1. Always Use EXPLAIN to Understand Your Query

Before optimizing, you need to understand how the database is executing your query. EXPLAIN shows the execution plan. EXPLAIN ANALYZE actually runs the query and shows real timing data.

-- See the execution plan (does not run the query)
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- Run the query and show actual timing
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- PostgreSQL: more detail
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 123;

Key things to look for in the output:

• Seq Scan: Full table scan — the database is reading every row. Usually bad for large tables.
• Index Scan: Using an index. Much faster for selective queries.
• cost=X..Y: Estimated cost. The second number is total cost.
• rows=N: Estimated rows returned. Large numbers on filtered queries suggest a missing index.

2. Index Columns You Filter, Sort, and Join On

The single biggest query optimization is adding the right index. A B-tree index lets the database find matching rows in O(log n) time instead of scanning all rows.

-- Add an index on the column you filter by most
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite index for queries filtering on multiple columns
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Index for ORDER BY to avoid sort operations
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Partial index: only index a subset of rows (very efficient)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

Column order matters in composite indexes. Put the most selective column first, and ensure the index matches the query's WHERE clause column order. An index on (user_id, status) can serve a query filtering on user_id alone, but NOT a query filtering on status alone.

3. Avoid Functions on Indexed Columns

Applying a function to an indexed column in a WHERE clause defeats the index.

If you need case-insensitive search, use a functional index: CREATE INDEX ON users(LOWER(email)); and then query with WHERE LOWER(email) = ....

4. Select Only Columns You Need

Using SELECT * forces the database to read every column, even ones your application never uses. This increases I/O and memory usage. It also prevents covering index optimizations where the index alone can answer the query without reading the table.

5. Avoid the N+1 Query Problem

N+1 is when you execute 1 query to get a list, then N more queries to fetch related data for each item. This is the most common ORM-induced performance problem.

-- N+1 problem: 1 query for users, then 1 query per user for their orders
SELECT * FROM users LIMIT 100;
-- Then for each user:
SELECT * FROM orders WHERE user_id = ?;  -- runs 100 times!

-- Solution: JOIN to fetch everything in one query
SELECT
  u.id, u.name, u.email,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name, u.email
LIMIT 100;

6. Use LIMIT and Pagination

Never return unlimited rows from a table query in a user-facing API. Always paginate.

-- Offset pagination (simple, but slow for large offsets)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 1000;

-- Cursor pagination (much faster for large datasets)
SELECT * FROM posts
WHERE created_at < '2024-05-01T10:30:00'
ORDER BY created_at DESC
LIMIT 20;

Offset pagination becomes slow at large offsets because the database must scan and discard all preceding rows. Cursor pagination (keyset pagination) avoids this by filtering from a specific point.

7. Use Window Functions Instead of Subqueries

Window functions are often much faster than correlated subqueries for analytical calculations.

-- Fast: use window function
SELECT
  id,
  total,
  SUM(total) OVER (PARTITION BY user_id) AS user_total
FROM orders;

8. Use Connection Pooling

Opening a new database connection for every request is expensive (50-100ms). Use a connection pool to reuse existing connections.

-- Node.js with pg pool
const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,          // maximum pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Reuse connections from the pool
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Summary Checklist

• Run EXPLAIN ANALYZE on slow queries before optimizing
• Add indexes on columns used in WHERE, JOIN ON, and ORDER BY
• Avoid functions on indexed columns in WHERE clauses
• Select specific columns instead of SELECT *
• Eliminate N+1 queries with JOINs or batch fetching
• Use cursor pagination for large tables
• Use window functions instead of correlated subqueries
• Use a connection pool in your application

Related Snippets