Sometimes queries run slowly, and EXPLAIN can 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.

Smiling stick figure with long straight hair: I always run EXPLAIN on a query before running my production database. I won’t risk overloading the database with a slow query!

1. After running the query (EXPLAIN ANALYZE SELECT ... FROM)

Smiling bald stick figure: why is my query so slow?

Smiling stick figure with short curly hair: 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 2 tables of 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)