read the transcript!
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)
- 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!
- After running the query
(EXPLAIN ANALYZE SELECT ... FROM...)person 1: why is my query so slow?
EXPLAIN ANALYZEruns 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
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!)
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