Skip to Content

A stick figure smiling

Here's a preview from my zine, Become a SELECT Star!! If you want to see more comics like this, sign up for my saturday comics newsletter or browse more comics!

Image of a comic. To read the full HTML alt text, click "read the transcript".

browse more comics! get the zine!
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)

  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


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

Saturday Morning Comics!

Want another comic like this in your email every Saturday? Sign up here!

I'll send you one of my favourite comics from my archives every Saturday.
© Julia Evans 2024 | All rights reserved (see the FAQ for notes about licensing)