Query optimization
Optimising means measuring, indexing sensibly, fetching only what you need and letting the planner have good statistics.
In this page:
Query optimization
Start with EXPLAIN ANALYZE on the slowest queries. Select only needed columns, filter early, avoid functions on indexed columns in WHERE, replace OFFSET paging with keyset paging, batch writes, and keep statistics fresh with ANALYZE.
Consider covering indexes (INCLUDE) and partial indexes. Measure again after each change.
Note:
A function on a column, like lower(col), prevents a plain index on col from being used.
Example: Query optimization
-- Slow: function on the column defeats the plain index
shop=# SELECT id FROM users WHERE lower(email) = '[email protected]';
-- Fast: an expression index matches the query
shop=# CREATE INDEX idx_users_lower_email ON users (lower(email));
-- Fast: keyset pagination instead of a huge OFFSET
shop=# SELECT id, title FROM posts WHERE id > 12000 ORDER BY id LIMIT 20;
-- Covering index so PostgreSQL can answer from the index alone
shop=# CREATE INDEX idx_orders_cust ON orders (customer_id) INCLUDE (total);⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Optimising without measuring
- Using SELECT *
- Wrapping indexed columns in functions
Chapter Summary
- Measure with EXPLAIN ANALYZE
- Select only needed columns
- Avoid functions on indexed columns
- Refresh statistics with ANALYZE
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: