Start with the query plan, then fix the biggest cost. Use EXPLAIN/ to see what the database actually does, add the right , eliminate queries, and select only the data you need.
Start with the query plan, then fix the biggest cost. Use EXPLAIN/ to see what the database actually does, add the right , eliminate queries, and select only the data you need.
EXPLAIN ANALYZEEXPLAIN ANALYZE shows the execution plan and real timings. A Seq Scan over a large table is the classic red flag:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders (cost=0.00..18500 rows=120)
-- Filter: (customer_id = 42)
-- rows removed by filter: 999880 ← scanned the whole table!
An index turns a full scan into a fast lookup:
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Now: Index Scan using idx_orders_customer (cost=0.42..8.5 rows=120)
-- 1,000,000-row scan → ~120-row lookup
Indexes have a cost on writes: every INSERT/UPDATE must also update the index, so index the columns you filter/join/sort on — not every column. A composite index (customer_id, created_at) serves WHERE customer_id = ? ORDER BY created_at in one structure.
The N+1 problem — one query for a list, then one per row — is a top cause of slowness. Replace it with eager loading, a JOIN, or batched IN (...):
-- N+1: 1 + 100 queries
SELECT * FROM orders; -- then per order: SELECT * FROM users WHERE id = ?
-- Fixed: one JOIN, only needed columns
SELECT o.id, o.total, u.name
FROM orders o JOIN users u ON u.id = o.user_id;
Also select only needed columns (avoid SELECT *) and paginate with LIMIT/keyset pagination so you never load millions of rows.
The database is the most common bottleneck. Reading the plan tells you why a query is slow instead of guessing; the right index, removing N+1, and trimming the result set routinely turn second-long queries into millisecond ones — without scaling hardware.