Query-Plans

Now, let’s be honest: staring at a monolithic blob of JSON or a deeply nested tree of text from EXPLAIN output is a special kind of torture. It’s like trying to appreciate a Rembrandt by looking at it through a keyhole. You can see a sliver of the genius, but you’re missing the whole picture. This is where visualization tools come in. They are the gallery that lets you step back and see the masterpiece—or, more often, the horrifying paint-by-numbers your query just created—in its full glory.

Right, let’s talk about the secret sauce that makes EXPLAIN anything more than a collection of hopeful guesses: the planner statistics. Without this, the query planner is just a person trying to navigate a pitch-black room by shouting “Marco!” and hoping the “Polo!” they get back is from the exit and not a brick wall. The pg_statistic system catalog and the ANALYZE command are the lights in that room. When you run ANALYZE (or VACUUM ANALYZE), you’re not just “updating stats.” You’re sending a tiny, overworked statistician scurrying through your tables. This statistician doesn’t look at every single row—that would take forever on a large table. Instead, it takes a sample. It builds a profile of your data: what values are most common, how values are distributed, and how many distinct values there are. This profile is stored, in a highly condensed and somewhat esoteric form, in pg_statistic. You don’t usually query pg_statistic directly; its friendlier cousin, pg_stats, is a view that translates this arcane data into something vaguely human-readable.

Right, let’s get into the real meat of the matter: how your database decides to smoosh two tables together. Forget the textbook definitions for a second. When the planner picks a join algorithm, it’s making a high-stakes bet based on the size of your data, available indexes, and memory. Get this wrong, and a query that should be milliseconds can become a coffee-break-length disaster. I’m here to make sure you can read that betting slip.

Alright, let’s get our hands dirty. You’ve fired off a query and it’s taking longer than a Monday morning. You know you need to look at the query plan, but staring at that tree of nested loops and scans can feel like reading tea leaves. I’m here to teach you how to read them. The three big players you’ll see are Sequential Scans, Index Scans, and Index-Only Scans. Knowing the difference is the difference between guessing and knowing.

Alright, let’s get our hands dirty. You’ve run EXPLAIN and you’ve seen the plan, but it’s a bit like a friend describing a movie plot without the special effects—you get the gist, but none of the spectacle. EXPLAIN ANALYZE is that movie with all the explosions. And when you add BUFFERS, you’re getting a behind-the-scenes documentary on how they pulled off those explosions, specifically how much stuff they had to haul in from the studio lot (your disk) versus what was already on the soundstage (your memory).

Alright, let’s get our hands dirty with EXPLAIN ANALYZE. You’ve run EXPLAIN and seen the hypothetical plan, the database’s best guess at how it thinks your query will perform. EXPLAIN ANALYZE is where we move from theory to practice. It actually runs your damn query and comes back with a post-game report, showing you what really happened. This is where you separate the confident predictions from the messy, sometimes horrifying, reality.

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.