The Cost of a Slow Query
A single slow query can cascade into degraded performance across your entire application. When thousands of users hit the same endpoint simultaneously, a query that takes 2 seconds instead of 20 milliseconds translates directly into poor user experience, higher infrastructure costs, and frustrated customers.
Performance optimization starts with measurement. You cannot optimize what you cannot see. This guide walks through the tools and techniques to find and fix slow SQL queries systematically.
Understanding Query Execution Plans
Before touching any index or rewriting any query, read the execution plan. In PostgreSQL, use EXPLAIN ANALYZE:
-- Always use EXPLAIN ANALYZE to see actual runtime statistics
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;
Key things to look for in the output:
- Seq Scan on large tables: usually indicates a missing index
- Nested Loop with high row estimates: potential for N+1 style problems
- Actual rows vs. estimated rows: large discrepancies mean stale statistics — run
ANALYZE tablename; - Execution time: focus on nodes with the highest cost
Indexing Strategies
Indexes are the single highest-impact optimization tool available. The right index can reduce a query from seconds to milliseconds.
-- Basic B-tree index for equality and range queries
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
-- Composite index: column order matters!
-- Good for: WHERE status = 'pending' AND created_at > '2025-01-01'
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- Partial index: only index a subset of rows
-- Much smaller and faster for queries on active records
CREATE INDEX idx_users_active_email ON users(email)
WHERE deleted_at IS NULL;
-- Index on expression: for case-insensitive searches
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Now this query uses the index:
SELECT * FROM users WHERE LOWER(email) = LOWER($1);
-- Covering index: includes extra columns to avoid heap fetches
CREATE INDEX idx_orders_covering
ON orders(user_id, created_at)
INCLUDE (total_amount, status);
A critical rule: do not over-index. Each index slows down INSERT, UPDATE, and DELETE operations because the index must be updated alongside the table. Measure which queries are actually slow before adding indexes.
Rewriting Inefficient Queries
Sometimes the query itself is the problem, not the schema.
-- BEFORE: Correlated subquery runs once per row (very slow)
SELECT u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;
-- AFTER: Single JOIN executes in one pass
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- BEFORE: Using function on indexed column defeats the index
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2025;
-- AFTER: Range condition uses the index on created_at
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
-- BEFORE: SELECT * fetches unnecessary columns
SELECT * FROM products WHERE category_id = $1;
-- AFTER: Select only what you need
SELECT id, name, price FROM products WHERE category_id = $1;
Pagination Performance
OFFSET-based pagination degrades significantly on large tables. For a table with 10 million rows, OFFSET 9999000 forces the database to scan and discard 9.9 million rows.
-- SLOW: OFFSET pagination gets worse as page number increases
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 100000;
-- FAST: Keyset (cursor) pagination — always O(log n) with an index
SELECT * FROM products
WHERE id > $last_seen_id -- Pass the last ID from the previous page
ORDER BY id
LIMIT 20;
Keyset pagination is faster and more reliable for large datasets, though it does not support jumping to arbitrary pages.
Design with Performance in Mind Using Erdmates
Performance issues are often rooted in schema decisions made early in development. Tools like Erdmates let you visualize your database structure and relationships before writing any code, making it easier to spot potential N+1 patterns, missing foreign keys that would benefit from indexes, and normalization issues that lead to complex slow joins.
Conclusion
SQL performance optimization follows a clear process: measure with EXPLAIN ANALYZE, add targeted indexes, rewrite inefficient queries, and revisit schema design for structural issues. Start with the slowest queries identified in your production logs and work systematically. Small, validated improvements compound into dramatic overall performance gains.