SQL Performance Tuning: Indexes, Queries, and Optimization
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:
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';The key numbers are actual time (startup..total) and rows. If the estimated rows differ significantly from actual rows, the database has stale statistics.
Reading Query Plans
Beyond sequential scans, look for these warning signs in query plans:
Index Scan— good, the database used an indexBitmap Heap Scan— decent, but may indicate missing covering indexesSort (external merge)— the sort spilled to disk, increasework_memNested Loopwith high row estimates — may need a different join strategyMaterialize— the database is caching intermediate results, which can be expensive
The planning time should generally be under 5ms for OLTP queries. Execution times vary by query complexity, but anything over 100ms for a simple lookup deserves investigation.
Indexing Strategies
Indexes are the most effective optimization for most queries.
When to Create an Index
Index columns that appear in WHERE clauses, JOIN conditions, ORDER BY clauses, and GROUP BY clauses:
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
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
-- Also: 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 is the number of distinct values divided by total rows.
Partial Indexes
Index only a subset of rows:
CREATE INDEX idx_users_active ON users (email) WHERE active = true;
CREATE INDEX idx_orders_recent ON orders (order_date)
WHERE order_date > '2024-01-01';Partial indexes are smaller and faster than full indexes, often by an order of magnitude for sparse data. A table of 10 million users where only 2 million are active benefits enormously from a partial index on the active subset.
Covering Indexes
Include all columns needed by a query in the index, eliminating the need to read the table:
CREATE INDEX idx_users_covering ON users (id) INCLUDE (name, email, status);Covering indexes enable index-only scans, where the database retrieves all required data from the index pages without touching the heap table. This dramatically reduces I/O for frequently accessed columns.
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';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;Rewrite OR Conditions as IN or UNION
-- Bad: OR can confuse the query planner
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Books';
-- Good: IN uses indexes efficiently
SELECT * FROM products WHERE category IN ('Electronics', 'Books');
-- Alternative for disjoint conditions with different indexes
SELECT * FROM products WHERE category = 'Electronics'
UNION ALL
SELECT * FROM products WHERE category = 'Books';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.
Solution: JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;Solution: Batch Query
user_ids = [u.id for u in users]
orders = db.execute(
"SELECT * FROM orders WHERE user_id IN (?)",
(user_ids,)
)Solution: Eager Loading in ORMs
Most ORMs support eager loading to batch related queries automatically:
# Django
users = User.objects.prefetch_related('orders').all()
# SQLAlchemy
users = db.query(User).options(joinedload(User.orders)).all()
# Prisma (Node.js)
const users = await prisma.user.findMany({ include: { orders: true } })Pagination Optimization
Offset-based pagination is slow for deep pages because the database must read and discard all preceding rows:
-- Slow for deep pages
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 19980;Keyset (cursor-based) pagination uses the index directly:
-- Fast for any page depth
SELECT * FROM orders
WHERE id > 19800
ORDER BY id
LIMIT 20;Keyset Pagination with Multiple Sort Columns
When sorting by multiple columns, include both in the WHERE clause:
-- Paginate by (created_at, id)
SELECT * FROM orders
WHERE (created_at, id) < ('2024-06-01', 1000)
ORDER BY created_at DESC, id DESC
LIMIT 20;This works with composite indexes and maintains consistent ordering.
Table Partitioning
Partitioning splits a large table into smaller physical pieces while keeping a single logical table interface. PostgreSQL supports range, list, and hash partitioning:
-- Range partition by date
CREATE TABLE orders (
id SERIAL,
order_date DATE NOT NULL,
customer_id INT,
amount DECIMAL
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2024_q1 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_2024_q2 PARTITION OF orders
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');Partition pruning allows the query planner to skip irrelevant partitions entirely, dramatically reducing the data scanned for date-range queries.
Database Configuration Tuning
Sometimes the query is fine but the database configuration is suboptimal.
work_mem
Controls the memory used for sort operations and hash tables:
# Increase for queries with large sorts
work_mem = 64MB # default is 4MBBe careful — this is per-operation, not per-connection. A query with 10 sort operations uses 10x work_mem.
shared_buffers
Controls how much memory PostgreSQL dedicates to caching data:
# Set to 25% of system RAM
shared_buffers = 4GBeffective_cache_size
Tells the query planner how much cache is available from the OS and PostgreSQL combined:
# Set to 50-75% of system RAM
effective_cache_size = 12GBCommon Performance Tips
Monitor slow query logs to identify the worst offenders first. Update table statistics with ANALYZE to keep query plans accurate. Use connection pooling since establishing connections is expensive. Limit result sets to avoid fetching more rows than needed. Archive old data using table partitioning by date. Use batch operations — INSERT INTO ... VALUES (...), (...), (...). Avoid correlated subqueries by rewriting them as JOINs.
Regular query tuning is a maintenance activity, not a one-time effort. Monitor performance over time, track regressions, and build a performance budget into your development process. A query that was fast with 10,000 rows may become slow with 10 million.
FAQ
How do I find slow queries in PostgreSQL? Enable log_min_duration_statement in postgresql.conf (e.g., log_min_duration_statement = 200ms). Queries exceeding the threshold are logged. Tools like pgBadger analyze these logs to identify patterns and the most time-consuming queries.
What is index bloat and how do I fix it? Over time, B-tree indexes accumulate dead tuples and wasted space. Rebuild indexes with REINDEX INDEX index_name or REINDEX TABLE table_name. Scheduled maintenance windows during low traffic prevent bloat from degrading performance.
Should I use VACUUM or VACUUM FULL? Regular VACUUM reclaims space for reuse but does not return it to the OS. VACUUM FULL compacts the table and returns space to the OS but locks the table exclusively. Use VACUUM regularly and VACUUM FULL sparingly during maintenance windows.
How many indexes is too many? Each index adds overhead to writes (INSERT/UPDATE/DELETE) and consumes storage. A general guideline is 5-10 indexes per table for write-heavy workloads and up to 20 for read-heavy reporting tables. Monitor index usage with pg_stat_user_indexes and drop unused indexes.
What is the difference between UNIQUE index and UNIQUE constraint? In PostgreSQL, they are implemented identically — both create a unique index behind the scenes. A UNIQUE constraint is a logical schema constraint while a unique index is a physical structure. Use constraints for data integrity and indexes for performance.
For a comprehensive overview, read our article on Acid Transactions Guide.
For a comprehensive overview, read our article on Database Backup Recovery.