SQL Query Optimization: Write Faster Queries
Slow queries are the most common cause of application performance problems. A single unoptimized query can take down a production database by consuming CPU, memory, and I/O resources. Query optimization is the process of reducing query execution time by understanding how the database engine processes your SQL and helping it work more efficiently.
Understanding Query Execution with EXPLAIN
EXPLAIN shows how the database plans to execute a query. It is the most important tool in query optimization:
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- PostgreSQL output
QUERY PLAN
------------------------------------------------------------
Seq Scan on users (cost=0.00..1000.00 rows=1 width=100)
Filter: (email = 'alice@example.com'::text)A Seq Scan (sequential scan) means PostgreSQL is reading every row in the table. For a small table, this is fine. For a table with millions of rows, this is a problem.
EXPLAIN ANALYZE
EXPLAIN ANALYZE actually executes the query and shows real timing:
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND order_date > '2024-01-01';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on orders (cost=0.00..5000.00 rows=100 width=200) (actual time=0.123..150.456 rows=95 loops=1)
Filter: ((customer_id = 42) AND (order_date > '2024-01-01'::date))
Planning Time: 0.234 ms
Execution Time: 150.567 msThe key numbers are actual time (startup..total) and rows. If the estimated rows differ significantly from actual rows, the database has stale statistics.
What to Look For in Query Plans
- Seq Scan on a large table — missing index
- Sort Method: external merge — sort spilled to disk, increase work_mem
- Nested Loop with high row estimates — consider a Hash Join
- Rows Removed by Filter significantly higher than rows returned — poor index selectivity
- Never executed — the planner determined this branch is unreachable
Indexing Strategies
Indexes are the most effective optimization for most queries.
When to Create an Index
Index columns that appear in:
WHEREclausesJOINconditionsORDER BYclausesGROUP BYclauses
-- Users frequently queried by email
CREATE INDEX idx_users_email ON users (email);
-- Orders frequently filtered by customer and date
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
-- Products sorted by price
CREATE INDEX idx_products_price ON products (price);Composite Indexes
The order of columns in a composite index matters. The index can be used for queries on the first column alone, or the first plus second, but not the second alone:
-- This index works for:
-- WHERE customer_id = 42
-- WHERE customer_id = 42 AND order_date > '2024-01-01'
-- But NOT for:
-- WHERE order_date > '2024-01-01'
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);Put the most selective column first. Selectivity means the number of distinct values divided by total rows. Higher selectivity means fewer rows to scan.
Partial Indexes
Index only a subset of rows:
-- Index only active users (common query pattern)
CREATE INDEX idx_users_active ON users (email) WHERE active = true;
-- Index only recent orders
CREATE INDEX idx_orders_recent ON orders (order_date)
WHERE order_date > '2024-01-01';Partial indexes are smaller and faster than full indexes.
Covering Indexes
Include all columns needed by a query in the index, eliminating the need to read the table:
-- Query needs name, email, and status
-- Covering index includes all three
CREATE INDEX idx_users_covering ON users (id) INCLUDE (name, email, status);
-- Now this query is an "index-only scan":
SELECT name, email FROM users WHERE id = 42;Query Rewriting
Sometimes the way you write a query affects how the database optimizes it.
Avoid SELECT *
-- Bad: reads all columns, prevents index-only scans
SELECT * FROM users WHERE email = 'alice@example.com';
-- Good: reads only needed columns
SELECT id, name, email FROM users WHERE email = 'alice@example.com';Use EXISTS Instead of COUNT for Existence Checks
-- Bad: counts all matching rows
SELECT COUNT(*) FROM orders WHERE customer_id = 42;
-- Good: stops at first match
SELECT EXISTS (SELECT 1 FROM orders WHERE customer_id = 42);Avoid Functions on Indexed Columns
-- Bad: function prevents index usage
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Good: search on the actual value
SELECT * FROM users WHERE email = 'Alice@Example.com';
-- Alternative: expression index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));Use UNION ALL Instead of UNION
-- Bad: UNION removes duplicates (slower)
SELECT name FROM active_users
UNION
SELECT name FROM archived_users;
-- Good: UNION ALL keeps duplicates (faster when dedup not needed)
SELECT name FROM active_users
UNION ALL
SELECT name FROM archived_users;Prefer JOIN Over Subqueries
-- Slow: correlated subquery runs once per row
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id)
FROM users;
-- Fast: single scan with LEFT JOIN
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Avoiding the N+1 Query Problem
The N+1 problem occurs when your code runs one query for a list of items, then one query per item:
# N+1 problem
users = db.execute("SELECT * FROM users")
for user in users:
# One query per user → N additional queries
orders = db.execute(
"SELECT * FROM orders WHERE user_id = ?", user.id
)Solution: JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;Solution: Batch Query
# Single query for all
user_ids = [u.id for u in users]
orders = db.execute(
"SELECT * FROM orders WHERE user_id IN (?)",
(user_ids,)
)Pagination Optimization
Offset-Based Pagination (Slow for Deep Pages)
-- Works well for pages 1-100, terrible for page 1000
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 19980;Offset skips rows internally, meaning the database must read and discard all preceding rows.
Keyset (Cursor-Based) Pagination
-- Fast for any page depth
SELECT * FROM orders
WHERE id > 19800
ORDER BY id
LIMIT 20;The database uses the index directly to find the starting point.
Keyset with Composite Sort
-- Paginate by (created_at, id) — requires composite index
SELECT * FROM orders
WHERE (created_at, id) < ('2024-06-15', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 20;Common Performance Tips
- Monitor slow query logs — identify the worst offenders first
- Update table statistics —
ANALYZEkeeps query plans accurate - Use connection pooling — establishing connections is expensive
- Limit result sets — do not fetch more rows than needed
- Archive old data — partition tables by date and archive old partitions
- Use batch operations —
INSERT INTO ... VALUES (...), (...), (...) - Avoid correlated subqueries — rewrite as JOINs when possible
-- Bad: correlated subquery runs once per row
SELECT name,
(SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;
-- Good: JOIN runs once
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Partitioning for Large Tables
Table partitioning splits a large table into smaller physical segments while maintaining a single logical interface. Range partitioning by date is the most common pattern:
CREATE TABLE logs (
id SERIAL,
created_at TIMESTAMP NOT NULL,
level VARCHAR(10),
message TEXT
) PARTITION BY RANGE (created_at);
CREATE TABLE logs_2024 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE logs_2025 PARTITION OF logs
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');The query planner automatically prunes partitions that do not match the WHERE clause, making full-table scans a fraction of the original size.
Materialized Views
For expensive aggregations queried frequently, materialized views pre-compute and store results:
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date(order_date), product_id, SUM(amount) AS total
FROM orders
GROUP BY date(order_date), product_id;
-- Refresh periodically
REFRESH MATERIALIZED VIEW daily_sales;Materialized views are ideal for dashboards, reports, and any query where slightly stale data is acceptable.
FAQ
How do I identify the most expensive queries in PostgreSQL? Enable log_min_duration_statement in postgresql.conf (set to 200ms for example) and use pgBadger or pgbouncer’s built-in logging to analyze the log output. The pg_stat_statements extension provides cumulative query statistics including total execution time, calls, and mean latency.
What is the difference between a clustered and non-clustered index? A clustered index determines the physical order of data on disk (table data is stored in the index leaf nodes). PostgreSQL does not use clustered indexes natively (it uses heap storage), but CLUSTER can reorder a table based on an index. MySQL InnoDB always stores data in a clustered index (the primary key). Non-clustered indexes store pointers to the actual data.
Should I use VACUUM, ANALYZE, or REINDEX? ANALYZE updates table statistics for the query planner — run it after significant data changes or daily during low traffic. VACUUM reclaims space from dead tuples — run it regularly (PostgreSQL auto-vacuum handles this). REINDEX rebuilds corrupted or bloated indexes — run it quarterly or when index performance degrades.
How does connection pooling improve performance? Establishing a database connection requires a TCP handshake, SSL negotiation, authentication, and memory allocation. Connection pools (PgBouncer, HikariCP) maintain a set of pre-established connections and hand them to application threads as needed. A pool of 20-50 connections typically handles hundreds of concurrent requests.
What is query plan caching? PostgreSQL caches query plans for the duration of a session (up to 5 executions by default for prepared statements). MySQL 8.0+ uses a query cache for identical query strings. Plan caching avoids the overhead of re-optimizing the same query repeatedly but can use stale plans if data distribution changes.
For a comprehensive overview, read our article on Acid Transactions Guide.
For a comprehensive overview, read our article on Database Backup Recovery.