EXPLAIN and ANALYZE

Have you ever wondered how the database chooses whether to use that nice index you just created? As soon as you hit Enter on a query (say, a long join with a chained group by), the Query Planner computes dozens of strategies on the fly to carry the query out and picks the one believed, in theory, to be fastest.

But as a developer, you need to be able to look inside the dark mind of the Query Planner.

EXPLAIN

To discover how the Planner intends to proceed, just put the magic word EXPLAIN at the start of your query.

EXPLAIN SELECT * FROM orders WHERE status = 'delivered';

The result won't be the orders! Instead, you'll see the plan in output (with estimated statistics):

Seq Scan on orders  (cost=0.00..25.88 rows=15 width=40)
  Filter: (status = 'delivered'::text)

This tells us:

1. The Planner will use a Seq Scan (brute, sequential read of the entire disk). Bad if there are billions of orders.
2. It estimates ~15 rows will be returned, and computes the "cost" in pre-loaded CPU cycles.

EXPLAIN ANALYZE

While EXPLAIN relies on algorithmic guesses (based on global statistics computed in the last few hours over the data), EXPLAIN ANALYZE actually runs the query for real (and waits for the full logical results), comparing the real elapsed time in mathematically absolute terms.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 90;

Index Scan using idx_orders_cust on orders  (cost=0.15..8.13 rows=4 width=40) (actual time=0.012..0.015 rows=4 loops=1)
  Index Cond: (customer_id = 90)
Planning Time: 0.081 ms
Execution Time: 0.033 ms

Hooray! Here you learn that the database has decided to apply an Index Scan on our secondary idx_orders_cust index, taking a microscopic execution time of a thousandth of a second, skipping millions of rows on the fly.

Your turn

Getting truthful metrics