EXPLAIN for Analysts — Just Enough to Read One

You don't need to be a DBA. You need to recognize 5 patterns in an EXPLAIN plan that tell you why your query is slow. FIND_VIDEO: search 'EXPLAIN ANALYZE postgres tutorial analyst' — recommended channel: Crunchy Data / EverSQL. Aim for 10 min or under.

10 minutesVideo LessonPDF notes

Watch EXPLAIN for Analysts — Just Enough to Read One on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Introduction to EXPLAIN — Sets up the 200-million row sample table and explains the purpose of the EXPLAIN command.
Decoding the Cost Line — Breaks down startup cost, total cost units, estimated row counts, and row width in bytes.
Indexed ORDER BY Sorting — Demonstrates low startup cost sorting when using a pre-ordered indexed column.
Unindexed Sorting and Parallelism — Examines parallel sequential scans and huge startup cost spikes during unindexed sorts.
Row Width and Column Selection — Shows how narrowing projected columns reduces row width from 31 bytes down to 4 bytes.
Index Scan vs Index Only Scan — Compares heap lookups in standard Index Scans against zero-heap Index Only Scans.

PDF notes

Bottom line: You can inspect how Postgres executes your queries without running them by prefixing statements with EXPLAIN. You will learn how to read cost estimates, row projections, row widths, and scan types to identify full table scans, expensive sorts, and excessive data transfer across the wire.

This lesson on EXPLAIN for Analysts — Just Enough to Read One is hands-on and example-driven. You can inspect how Postgres executes your queries without running them by prefixing statements with EXPLAIN. You will learn how to read cost estimates, row projections, row widths, and scan types to identify full table scans, expensive sorts, and excessive data transfer across the wire.

What You'll Be Able To Do

Interpret startup cost versus total cost numbers displayed in query execution plans
Identify when Postgres performs a Sequential Scan, Index Scan, or Index Only Scan
Trace execution nodes from the bottom up to locate resource-intensive operations like unindexed sorts
Extract estimated row counts from EXPLAIN to approximate table sizes without running expensive COUNT queries
Optimize query projection to minimize row width in bytes and reduce network transmission overhead

Detailed Concept Walkthrough

1. Anatomy of the Cost Line

The cost line in an EXPLAIN node provides Postgres's internal mathematical estimation of query work, row counts, and data volume before actual execution. It serves as a static prediction based on table statistics rather than wall-clock runtime.

Cost Range: The two numbers separated by dots (e.g., cost=0.00..289.00) denote startup cost and total estimated cost in planner units. Startup cost measures the work required before the first row can be returned, while total cost estimates completing the entire node.
Rows and Width: The rows metric estimates the number of rows returned by the operation based on catalog statistics. The width metric represents the average size of a single output row in bytes across all selected columns.
Best Practice: Use estimated rows from EXPLAIN instead of running SELECT COUNT(*) on multi-million row tables when you only require a quick approximation for dashboards or analytics.

-- Inspect cost, estimated rows, and byte width for a full table scan
EXPLAIN SELECT * FROM grades;

-- Example output line:
-- Seq Scan on grades  (cost=0.00..289.00 rows=200000000 width=31)

Key Takeaway: Startup cost indicates initial pre-fetch overhead, while row width dictates the total memory and network transfer footprint.

2. Sequential Scans Versus Index Scans

Postgres selects its data access path based on available indexes, filter conditions, and the volume of rows requested. The engine chooses between reading all pages sequentially from disk heap or seeking specific index pointers.

Sequential Scan (Seq Scan): Occurs when reading an entire table with no filtering or when no suitable index exists. The database reads disk pages directly from the table heap in sequential order, often utilizing parallel worker threads.
Index Scan: Traverses an index structure using a specific condition (e.g., WHERE id = 10), finds the pointer, and fetches the corresponding table page from the heap to retrieve non-indexed columns.
Index Only Scan: Retrieves requested columns directly from the index tree without fetching data pages from the heap. This happens when every column referenced in the SELECT and WHERE clauses exists inside the index.

-- 1. Index Scan: fetches row from heap using index condition
EXPLAIN SELECT * FROM grades WHERE id = 10;

-- 2. Index Only Scan: satisfies query entirely within the index leaf pages
EXPLAIN SELECT id FROM grades WHERE id = 10;

Key Takeaway: Index Only Scans are the fastest scan path because they bypass reading table heap pages entirely.

3. Sorting and Startup Overhead

Sorting data requires distinct execution steps that inflate the startup cost, because the planner cannot emit the first row until the entire dataset is ordered. Using an indexed column avoids runtime sorting because index entries are already ordered.

Execution Flow: Query plans must be read from the innermost/bottom node upward. Subordinate nodes fetch or scan raw records first, then pass intermediate sets to parent nodes for sorting or aggregation.
Unindexed Sort Penalty: Sorting by an unindexed column forces Postgres to perform a sequential scan, pull all matching rows into memory or disk work areas, and sort them completely before returning row number one.
Parallel Execution: On large unindexed sorts, the planner coordinates multiple parallel worker processes to scan and sort chunks independently, followed by a gather merge node to combine final ordered results.

-- Low startup cost: sorting on indexed column 'g' reads pre-sorted index
EXPLAIN SELECT * FROM grades ORDER BY g;

-- High startup cost: sorting unindexed column 'name' requires full table scan + sort step
EXPLAIN SELECT * FROM grades ORDER BY name;

Key Takeaway: An inflated startup cost in an execution plan typically indicates unindexed sorting, hashing, or blocking aggregation.

4. Column Selection and Row Width Optimization

The width field in an EXPLAIN plan reveals the byte payload transmitted per row, which directly impacts disk buffer throughput and client network performance.

Data Type Sizing: Fixed-width types occupy constant bytes (e.g., standard 4-byte integers, 8-byte doubles), whereas variable types like TEXT contribute average estimated byte lengths based on table statistics.
Network Footprint: Selecting unnecessary columns or blanket SELECT * inflates row width, multiplying memory consumption during sorts and generating higher TCP packet overhead across application connections.
Best Practice: Explicitly project only required columns in production analytics queries to keep row width small and unlock index-only execution paths.

-- Compare width: single 4-byte integer column
EXPLAIN SELECT id FROM grades;
-- -> (width=4)

-- Compare width: text column with variable average length
EXPLAIN SELECT name FROM grades;
-- -> (width=19)

Key Takeaway: Pruning unneeded columns reduces row byte width, memory usage during intermediate operations, and network transfer volume.

Topics Covered in EXPLAIN for Analysts — Just Enough to Read One

Introduction to EXPLAIN (0:00 - 0:45) — Sets up the 200-million row sample table and explains the purpose of the EXPLAIN command.
Decoding the Cost Line (0:45 - 1:55) — Breaks down startup cost, total cost units, estimated row counts, and row width in bytes.
Indexed ORDER BY Sorting (1:55 - 2:30) — Demonstrates low startup cost sorting when using a pre-ordered indexed column.
Unindexed Sorting and Parallelism (2:30 - 3:25) — Examines parallel sequential scans and huge startup cost spikes during unindexed sorts.
Row Width and Column Selection (3:25 - 4:20) — Shows how narrowing projected columns reduces row width from 31 bytes down to 4 bytes.
Index Scan vs Index Only Scan (4:20 - 5:10) — Compares heap lookups in standard Index Scans against zero-heap Index Only Scans.

SQL Cheat Sheet

EXPLAIN <query> — Shows estimated planner cost and execution node hierarchy without executing query
```
EXPLAIN SELECT * FROM grades;
```
cost=startup..total — Represents units to first row and units to completion
```
-- Seq Scan on grades (cost=0.00..289.00 rows=200000000 width=31)
```
rows=<count> — Estimates total rows emitted by that execution plan node
```
-- rows=200000000
```
width=<bytes> — Displays average byte size of single output row
```
-- width=4
```
Seq Scan — Scans raw table heap pages sequentially without using indexes
```
EXPLAIN SELECT * FROM grades;
```
Index Scan — Traverses index to find row pointers, then reads heap
```
EXPLAIN SELECT * FROM grades WHERE id = 10;
```
Index Only Scan — Returns data directly from index without accessing table heap
```
EXPLAIN SELECT id FROM grades WHERE id = 10;
```

Comparison Table

Scan Method	Heap Access Required	Typical Use Case
Seq Scan	Full heap scan	No filters or broad full-table reads
Index Scan	Selective heap fetch	Filtered queries retrieving unindexed columns
Index Only Scan	Zero heap fetch	Filtered queries selecting only indexed columns

Common Pitfalls

Mistake: Assuming EXPLAIN cost represents exact execution time in milliseconds. Avoid: Treat EXPLAIN cost as planner work units and use EXPLAIN ANALYZE for actual runtimes.
Mistake: Running SELECT COUNT(*) across large tables for rough counts. Avoid: Read the estimated rows metric from EXPLAIN to avoid expensive table-wide scans.
Mistake: Using SELECT * when sorting or filtering large datasets. Avoid: Project only necessary columns to decrease byte width and avoid unnecessary heap fetches.
Mistake: Reading nested EXPLAIN plans from top to bottom. Avoid: Read execution plans from the bottom up to follow actual data flow.

FAQs

Does EXPLAIN execute my SQL query behind the scenes? No, EXPLAIN only generates the planner's estimated execution strategy without executing the query or modifying data.
What does a startup cost of 0.00 mean? It means the database engine can immediately begin streaming rows from disk heap without completing preparatory work like sorting or grouping.
Why is an Index Only Scan faster than a standard Index Scan? An Index Only Scan satisfies the query entirely from index memory pages, eliminating the secondary step of fetching table heap pages.

Common pitfalls

Mistake: Assuming EXPLAIN cost represents exact execution time in milliseconds. Avoid: Treat EXPLAIN cost as planner work units and use EXPLAIN ANALYZE for actual runtimes.
Mistake: Running SELECT COUNT(*) across large tables for rough counts. Avoid: Read the estimated rows metric from EXPLAIN to avoid expensive table-wide scans.
Mistake: Using SELECT * when sorting or filtering large datasets. Avoid: Project only necessary columns to decrease byte width and avoid unnecessary heap fetches.
Mistake: Reading nested EXPLAIN plans from top to bottom. Avoid: Read execution plans from the bottom up to follow actual data flow.

Welcome guys! Today we're going to explain EXPLAIN in Postgres. Get it? So, there is this command in Postgres that is called EXPLAIN and it is used to retrieve information about what query plans Postgres will use for a given SQL statement. How about we jump into it? I have a beautiful table here, it has to be beautiful. If it's not beautiful, I send it back, Michael Scott. This table is called grades, and there is an id, and there is a grade, and there is a name. So, it's like a bunch of students. There is a lot of rows here, I think over 200 million. And I showed many times how to create such table and insert this much volume of rows. So, there is an index on the id, there is an index on the g, the grade, but there's no index on the name of the students. So, here's what we're going to do. So, we're going to execute a bunch of queries, and we're going to explain them, and see what Postgres will do. So, let's start with explaining the SELECT *, the worst query you can ever do. When we do that, we get this beautiful one-liner, and let's explain, let's demystify what's going on there. So, the first part is usually the query plan, and this is called a Seq Scan, and this is equivalent to a full table scan in other databases. Essentially, what what the database decided to do is like, okay, you're selecting everything and there is no filter, so I'm going to go directly to the table, I'm going to go directly to the heap and fetch everything. That's a sequential scan. Sometimes Postgres do a parallel sequential scan with threading, they spin up multiple threads just to do the scanning. on grades. And here it is the cost, and the cost is usually has two numbers. Almost has two numbers, separated by two dots. The first number and the second number. So let's explain what this means. The first number means how many millisecond it took me to fetch the first page, right? And in this case, it took zero. Why? Because Postgres immediately went to the table and fetched the first row, and immediately got results. So it cost you almost nothing to get the first results. So if you're doing some sort of a LIMIT, right, select * from grades LIMIT 1, you're going to get a result very, very quick. This number can increase, the startup cost can increase if Postgres decide, if the database decide to do some work before fetching, such as aggregating, such as ORDER BY, right? Things has nothing to do with actual fetching, work that precedes the actual things that you want to do. So that's you're going to see this number. If you see this number go up, that means you're doing a lot of stuff before fetching. And this number is essentially the total amount of time that it thinks, because remember, it didn't really execute that query, right? It just tells you, hey, I think I'm going to do this, right? And this is the total amount of time, which is in this is in milliseconds, so it it estimates that it's going to finish the whole thing in 289 seconds, essentially. And this is the another estimation, this is very valuable by the way, right? This is not an accurate number, but it gives you a quick number, approximate, based on its own statistics that this is the number of rows that it's going to fetch. That's why I always recommend when you want to do a COUNT, and you don't really care about the actual number, like you're doing I don't know, you're building Instagram and you're you're counting the number of likes, use this. Don't you Don't do select count. Select count will kill your performance. It's actually go and physically do the count on all your rows. And if you have billion rows, tough luck counting that stuff, right? That's why a lot of people use this: explain, give me an estimate, return that. And there's the width, the width of the row. So this is the sum of all the bytes for all the columns. And I have three bytes, if you sum them you're going to get 31, because there's text there. All right, so that's the first one. Let's spice things up a little bit. Let's do an explain select * from grades, and we're going to do an ORDER BY this time. We're going to ORDER BY g, and g is the grade. What do we do? What do we get? You see that this number bumped up a little bit. It's a It's a 0.43 ms. So it it did Postgres did some work, or it is attempting to do some work before fetching the row. And that work is what? Is actually doing the sort, ORDER BY g. And it's not so bad, because it did use the index that is on g to do the sorting. And guess what? The index is already sorted. So the work is very, very trivial. Right? But there is work nonetheless. And that's the total number of thing. The rows, in this time this is almost the same number, it should be, better be. And then the width is also the same, okay? Now let's spice things a little bit and do explain select * from grades order by name, something that has no index. And you can see that is a little bit of work. In this case, Postgres is, ooh, you want me to sort by the name? The name is a column that is not indexed, so I have to go to the heap, and guess what? I'm going to pull everything in order to sort it. So it started to, first of all, it did a parallel sequence scan. Always read it from bottom going up, right? This is how you do it. Don't read it from up to it starts from the inner thing, going up up up up up, okay? So the parallel sequential scan on grades. It took nothing to do that, because hey, I'm going directly to the table, right? It cost nothing to go there. Again, we didn't sort yet. Uh this is how much time it's going to take. So it's estimating that it's going to take me 200 seconds, 218 seconds. And this is the number of rows that we're going to get back, 31, got it? All right. Now we're going to sort on the name. Look at that! Look how much it took just to get to the point to fetch something. The sorting took Man, that's a lot! That's That's what, 1,000 seconds? Yikesy, right? So, you can see that this number is very critical, the first number. The second number's not not so much, that's the last one, right? But the first number is everything that you need. And then obviously, we planned two workers so there's two parallel doing this thing. And since we had two threads, two processes doing the work, we got to merge the results at the end of the day, right? And that's the total results, obviously. We get this number, and uh that's the final results, and 31 width. So you see that there is more work that need to happen to get to get to the actual thing. All right. Let's play a little bit with uh other stuff, guys. What I'm going to do here is I'm going to do explain select id, not just the whole thing, just the id from grades. Let's just try this thing. What do we get? Obviously, we get 0 cost. It cost nothing to go to the table. How what It cost nothing to go to the table to fetch the first page, that's almost nothing, zero, right? And the rows, but look at this. Look at the width! We get four. Why four? 4 bytes is the results. You're asking to return only the id, and the id, by default, is an integer, and the integer is 4 bytes. By the way, careful! Don't put your primary key as as just integer if you're expecting this table to grow beyond 2 billion, right? That's what happened to Parler, by the way. They grew beyond 2 billion notification and then blew up their table. All right, yeah. So they're 4 bytes in the id. Okay. Let's do explain select name from grades. You can see that the the average row size is 19, because the name is a is text, right? And it depends on the name of the students, it can go 19. It just they took the average and said, "Hey, we're expecting it's going to be 19 byte." So, be careful with this number, uh especially if you're returning this across the wire to your backend application, right? The larger is this number, the larger the network you're going to take, the the higher the TCP packets, right? I mean don't put like a blob and do select * when you don't use this thing. That's why it's insulting to do a select * to the database, only to pick one row or one column from those, right? Only select what you need. And you can see if I do, for example, the g, it's 8, that's the that's the number of bytes in the double value. All right, let's do and How was How is indexing working in the in Postgres? select * from grades where id = 10. So, there's an obviously there is a unique index on the id here. So, we're expecting this to be fast. Obviously, we scanned the index and we jumped to fetch We jumped to the heap to fetch some row value, and it took us 0.43 seconds, and the total time to took uh took the whole thing is 8 ms. Again, this is not actual time. There is another command that we're going to explain in another lesson or video, where we're going to show ANALYZE. That's the actual time taken. This is This is just an estimation. So we did an index scan, and we used the index condition like this one, in this case. And uh you can see if we played with this a little bit and say, "Okay, this is a silly query, but bear with me here." So now when I do explain select id from grades where id = 10, silly query, of course, because the id you just said id = 10, but you can see that now we're getting an Index Only Scan, which is a faster one, right? That the work We didn't even need to jump to the heap to do the work, right? So that's just another type of query. I I did I I did many videos talking about that as well. All right, guys, that was a quick video explaining EXPLAIN. And uh this was a very, very basics of the basics of the stuff, right? Maybe I'm going to make a few more lessons and more videos to explain the ANALYZE, explain the buffers, explain the shared hits, and other stuff as well. Let's Let's keep this video short and sweet. Going to see you on the next one. You guys stay awesome. Goodbye.

Frequently asked questions

Does EXPLAIN execute my SQL query behind the scenes?

No, EXPLAIN only generates the planner's estimated execution strategy without executing the query or modifying data.

What does a startup cost of 0.00 mean?

It means the database engine can immediately begin streaming rows from disk heap without completing preparatory work like sorting or grouping.

Why is an Index Only Scan faster than a standard Index Scan?

An Index Only Scan satisfies the query entirely from index memory pages, eliminating the secondary step of fetching table heap pages.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.