Sometimes queries run slowly, and EXPLAIN can tell you why!

2 ways you can use EXPLAIN in PostgreSQL: (other databases have different syntax for this)

1. Before running the query (EXPLAIN SELECT... FROM ...)
   This calculates a query plan but doesn’t run the query.

I always run EXPLAIN on a query. before running it on my production database. I won’t risk overloading the database with a slow query!

1. After running the query (EXPLAIN ANALYZE SELECT ... FROM...) person 1: why is my query so slow?
   person 2: EXPLAIN ANALYZE runs the query and analyzes why it was slow

Here are the EXPLAIN ANALYZE results from PostgreSQL for the same query run on two tables of 1,000,000 rows: one table that has an index and one that doesn’t

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1

unindexed table:

Seq Scan on users
Filter: (id = 1)
Rows Removed by Filter: 999999 Planning time: 0.185 ms
Execution time: 179.412 ms

“Seq Scan” means it’s looking at each row (slow!)

indexed table:

Index Only Scan using users_id_idx on users
Index Cond: (id = 1)
Heap Fetches: 1
Planning time: (3.411 ms
Execution time: 0.088 ms

the query runs 50 times faster with an index