Query Optimization

Query optimization is the work of reducing the time and resources a database query consumes. It happens automatically in the database planner and manually when developers add indexes or rewrite queries.

Why it matters

Slow queries are a leading cause of slow pages and timeouts, and one bad query can saturate a database and take a whole site down. The usual workflow is to find the slowest queries, inspect their execution plans, then fix the cause, often a missing index, a function on an indexed column, or fetching far more rows than needed.

Example

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders ... rows=1200000

CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Index Scan using idx_orders_customer ... rows=18

Common mistakes

• Optimizing by guesswork instead of reading the execution plan and measuring.
• Selecting every column and row, then filtering in application code.
• Adding indexes for one query without noticing the slowdown they cause on writes.

Related

• Query execution plan
• Database index
• N+1 query
• SQL JOIN

This page is general information written by our editorial team and updated when documentation changes. Servers and providers differ, so test changes safely first. See our editorial policy and disclaimer.