Query Optimization and EXPLAIN

What EXPLAIN Tells You

EXPLAIN shows the plan the planner chose: the scan method, join order, filter conditions and estimated row counts. Adding ANALYZE runs the query and reports real timings.

Reading a Plan

EXPLAIN ANALYZE
SELECT * FROM customers WHERE city = 'Pune';

Look for a Seq Scan on large tables. Replacing it with an index scan is usually the biggest win available.

Index Strategy

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at);

Multi-column indexes follow the leftmost rule: a filter on customer_id alone can use the second index, but a filter on created_at alone cannot.

Vacuum and Statistics

The planner relies on statistics. ANALYZE refreshes them, and VACUUM reclaims dead rows so the planner sees accurate row counts.

Key Points

• EXPLAIN ANALYZE is the first tool for slow queries.
• Indexes help WHERE, JOIN and ORDER BY columns.
• Multi-column indexes respect the leftmost prefix rule.
• Keep statistics fresh with ANALYZE and VACUUM.