Right, let’s get our hands dirty. You’ve written a query. It’s not returning as fast as you’d like. Your first instinct, your only sane instinct, is to run EXPLAIN on it. This isn’t a magic spell; it’s a blueprint. It shows you the path the database planner thinks it’s going to take to get your data. I say “thinks” because EXPLAIN doesn’t run the query—it just shows you the plan, like a fire drill without the actual fire. We’ll get to the fire drill with EXPLAIN ANALYZE later.

The output is a tree of operations, called nodes, and you read it from the inside out and bottom to top. It looks intimidating, but once you know what to look for, it’s like reading a map. A very, very nerdy map.

The Anatomy of a Plan Node

Every line in an EXPLAIN output is a node representing an operation. Let’s break down a simple one. Imagine we have a users table.

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

You might get a plan that looks like this:

Seq Scan on users  (cost=0.00..22.70 rows=1 width=148)
  Filter: (email = 'test@example.com'::text)

Let’s autopsy this. The node type is Seq Scan (sequential scan). This is the database saying, “I’m going to read every single row in the users table, one after the other, and check if the email matches.” It’s the equivalent of looking for a specific book in a library by starting at the first shelf and reading every title on every shelf until you find it. It’s not clever, but sometimes it’s the only option.

Now, the numbers in parentheses: (cost=0.00..22.70 rows=1 width=148)

  • Cost (0.00..22.70): This is the planner’s estimate of how expensive this operation is. The first number is the startup cost (e.g., time to find the first row). The second is the total cost to complete the operation. These aren’t in units of time (like milliseconds); they’re arbitrary cost units based on the planner’s model of disk I/O and CPU cycles. You use them to compare the relative expense of parts of the plan. A lower cost is generally better.
  • Rows (1): The planner’s guess for how many rows this node will spit out. This is a critical number. If the planner guesses rows=1 but it actually returns a million, every subsequent part of the plan will be catastrophically wrong. This misestimation is the root of most evil query plans.
  • Width (148): The estimated average size, in bytes, of each row returned. A huge width hint at a lot of data being processed, which is more expensive.

The Big Three: Seq Scan, Index Scan, and Index Only Scan

These are the workhorses. You need to know them intimately.

  • Seq Scan: As we saw. It’s often the villain in performance horror stories. It’s efficient for reading large portions of a table (> ~5-10%), but a disaster for finding a single row. If you see this on a large table for a query fetching one row, you probably need an index.

  • Index Scan: This is the clever version. The database uses an index to find the location of the rows it needs, then goes to the table (“heap”) to fetch the actual data. Let’s create an index and see it in action.

    CREATE INDEX ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

    The plan might now show:

    Index Scan using users_email_idx on users  (cost=0.28..8.29 rows=1 width=148)
  Index Cond: (email = 'test@example.com'::text)

    See the Index Cond? That’s the condition it used to look up the value in the index. It found the row’s location in the index and then went to the main table to get the full row. This is two steps, but it’s almost always faster than a sequential scan for small result sets.

  • Index Only Scan: The holy grail. If all the data your query needs is already in the index, the planner can be truly lazy and never visit the main table at all. Let’s change our query.

    EXPLAIN SELECT email FROM users WHERE email = 'test@example.com';

    Now the plan might be:

    Index Only Scan using users_email_idx on users  (cost=0.28..4.29 rows=1 width=32)
  Index Cond: (email = 'test@example.com'::text)

    Notice the width dropped from 148 to 32 because it’s only returning the email column, not the entire row. This is incredibly fast. This is why you should always SELECT only the columns you need—it gives the planner a chance to use this optimization.

The Join Types: How Tables Get Hitched

When you join tables, the planner has to choose an algorithm. The two most common are:

  • Nested Loop: For each row in the first (outer) table, scan the second (inner) table for matching rows. It’s a brute-force method that’s only efficient if the outer table is very small. The cost explodes with larger tables. You’ll see a Nested Loop node with two child nodes underneath it.

  • Hash Join: The database builds a hash table in memory from the smaller table, then scans the larger table, probing the hash for matches. This is highly efficient for joining large tables on equality conditions (=). You’ll see a Hash Join node with a child node for Hash (building the hash table) and another for the other table being scanned.

The planner’s choice here depends heavily on its row estimates. If it thinks it’s joining two small result sets, it might pick a Nested Loop. If it’s wrong and one set is huge, your query will drown in a sea of loops.

The Crucial Role of WHERE and JOIN Conditions

Look closely at the filters. There’s a world of difference between a Filter and an Index Cond.

  • Index Cond: The condition was applied during the index lookup. This is fast.
  • Filter: The condition was applied after the rows were retrieved. This is what happens when you can’t use an index for the predicate. For example, using a function on a column: WHERE lower(email) = 'test@example.com' would likely force a Filter even if there’s an index on email, because the index stores the original value, not the lowercased one.

This is the most common self-inflicted wound I see. You put an index on a column, but your query function wraps that column, rendering the index useless. The planner does a sequential scan and filters the results. Don’t do that.