Day: February 29, 2024

Welcome to Day 6C.

This is one of the most valuable lessons in the entire course.

Many developers know how to write SQL.

Very few can answer questions like:

“Why is this query slow?”

or

“Why did PostgreSQL choose a Bitmap Heap Scan instead of an Index Scan?”

or

“What would you optimize first?”

This lesson will teach you exactly that.

Today’s Goal

By the end of today, you should be able to:

• Read an EXPLAIN ANALYZE plan from top to bottom
• Understand every important field
• Explain why PostgreSQL chose a plan
• Identify bottlenecks
• Suggest optimizations
• Discuss execution plans confidently in a senior interview

First, Understand What EXPLAIN ANALYZE Actually Does

Consider this query:

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

Without EXPLAIN, PostgreSQL simply returns the result.

With:

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

PostgreSQL says:

“Here’s the plan I intend to use.”

With:

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

PostgreSQL actually executes the query and says:

“Here’s what really happened.”

The Query Planner

Imagine PostgreSQL as a GPS.

You ask:

Go from A to B.

The GPS considers:

• Highway
• Local roads
• Toll roads
• Traffic

Then chooses the cheapest route.

PostgreSQL does exactly the same.

It considers:

• Sequential Scan
• Index Scan
• Bitmap Scan
• Hash Join
• Nested Loop
• Merge Join

and chooses what it estimates to be the cheapest plan.

Our Practice Table

Use the same table from Day 6B.

users

100,000 rows.

Our First Plan

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

You might see something similar to:

Index Scan using idx_users_email on users
  (cost=0.42..8.44 rows=1 width=51)
  (actual time=0.030..0.032 rows=1 loops=1)

Let’s decode every part.

Part 1 – Scan Type

First line:

Index Scan

This answers:

How did PostgreSQL access the table?

Possible answers:

• Seq Scan
• Index Scan
• Index Only Scan
• Bitmap Heap Scan

The scan type is the first thing you should notice.

Part 2 – Using Which Index?

using idx_users_email

PostgreSQL tells you exactly which index it used.

If you expected:

idx_users_city

but it chose:

idx_users_age

you should ask yourself why.

Part 3 – Cost

Example:

cost=0.42..8.44

Many beginners think:

“8.44 milliseconds.”

No.

Cost is not time.

It is PostgreSQL’s internal scoring system.

Think of it like this:

Plan A
Cost = 150

Plan B
Cost = 70

PostgreSQL chooses Plan B.

Startup Cost

First number:

0.42

Cost before the first row can be returned.

Total Cost

Second number:

8.44

Cost to return every row.

Part 4 – Rows

rows=1

Planner estimate.

Meaning:

"I think this query will return
1 row."

Part 5 – Width

width=51

Estimated average size of one returned row.

Used internally for memory and I/O estimates.

Part 6 – Actual Time

actual time=0.030..0.032

Meaning:

First row
↓
0.030 ms

Entire query finished:

0.032 ms

Part 7 – Actual Rows

actual rows=1

Excellent.

Planner guessed:

1

Reality:

1

Very accurate.

Part 8 – Loops

loops=1

This operation executed once.

You’ll later see plans like:

loops=100000

That often indicates an expensive nested loop.

Reading Plans from Bottom to Top

This surprises many developers.

Execution plans are printed like a tree.

Example:

Limit
↓
Sort
↓
Index Scan

Although Limit appears first, execution begins at the bottom.

Conceptually:

Index Scan
↓
Sort
↓
Limit

Think of a factory:

Raw Material
↓
Machine 1
↓
Machine 2
↓
Finished Product

The raw material starts at the bottom.

Example 2 – Sequential Scan

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Chicago';

Output:

Seq Scan on users
(cost=0.00..2332.00 rows=24780 width=51)
(actual time=0.08..21.10 rows=25000 loops=1)

Let’s interpret it.

Why Seq Scan?

Question:

How many rows match?

25,000

That’s:

25%

of the table.

Using the index might require:

• index lookup
• 25,000 table lookups

Sequential Scan may simply be cheaper.

Interview Question

If PostgreSQL ignores your index,

does that mean

the index is useless?

Answer:

Absolutely not.

It means PostgreSQL estimated another plan to be cheaper for that specific query.

Example 3 – Bitmap Heap Scan

Suppose you create:

CREATE INDEX idx_users_city
ON users(city);

Now:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Chicago';

Output:

Bitmap Heap Scan
↓
Bitmap Index Scan

Notice there are two nodes.

Bitmap Index Scan

First:

Read the index.

Chicago
↓
Rows
4
8
12
...

Bitmap Heap Scan

Then:

Visit the table efficiently.

Instead of:

Index
↓
Table
↓
Index
↓
Table

It does:

Index
↓
Collect row locations
↓
Read pages together

Excellent for medium-sized result sets.

Visual

Bitmap Index Scan
↓
Matching Row IDs
↓
Bitmap Heap Scan
↓
Actual Rows

Example 4 – Index Only Scan

Suppose:

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

Plan:

Index Only Scan

Question:

Why is this faster?

Answer:

Because PostgreSQL answered the query using only the index.

No table lookup.

Planning Time vs Execution Time

Example:

Planning Time: 0.2 ms
Execution Time: 0.3 ms

Planning:

Choosing the route.

Execution:

Driving the route.

Why Estimates Matter

Suppose:

Planner:

rows=5

Reality:

actual rows=50000

Huge difference.

The planner may choose a terrible plan because its estimate was wrong.

This usually indicates stale statistics.

ANALYZE

Run:

ANALYZE users;

PostgreSQL updates statistics.

The planner now has better information.

VACUUM ANALYZE

Often you’ll see:

VACUUM ANALYZE users;

It does two things:

• Cleans dead tuples
• Updates statistics

We’ll study MVCC later.

The Most Common Plan Nodes

These are the ones you should know well for interviews.

Seq Scan

Reads every row.

Think:

Read entire book.

Index Scan

Uses an index.

Think:

Use the book's index.

Index Only Scan

Never touches the table.

Think:

Everything I need is already in the index.

Bitmap Index Scan

Collect matching row locations.

Bitmap Heap Scan

Fetch those rows efficiently.

Sort

ORDER BY

often produces:

Sort

Sorting millions of rows can be expensive.

Aggregate

Produced by:

COUNT()
SUM()
AVG()
GROUP BY

Hash Join

Often used for joins.

We’ll study joins from PostgreSQL’s perspective soon.

Nested Loop

Good when:

One side is tiny.

Terrible when:

Both sides are huge.

Limit

Produced by:

LIMIT 10

Real Example

SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 10;

Possible plan:

Limit
↓
Sort
↓
Seq Scan

Question:

Can we improve it?

Yes.

Index:

CREATE INDEX idx_created_at
ON users(created_at DESC);

Now PostgreSQL may avoid sorting completely.

Buffers (Advanced)

Sometimes you’ll see:

Buffers:
shared hit=500
read=2

Meaning:

Most pages were already in memory.

We’ll study this later.

Parallel Query

Sometimes:

Gather
↓
Parallel Seq Scan

PostgreSQL used multiple CPU workers.

Very common for huge tables.

How to Read Any Plan

I use this checklist.

Step 1

What is the scan type?

Step 2

Which index?

Step 3

Estimated rows?

Step 4

Actual rows?

Step 5

Huge mismatch?

If yes,

statistics may be wrong.

Step 6

Planning vs execution time.

Step 7

Which operation consumed most of the cost?

Real Interview Example

Interviewer shows:

Seq Scan
rows=100000
actual rows=1

Question:

Would you optimize?

Yes.

Probably missing an index.

Another example:

Index Scan
rows=90000

Question:

Should PostgreSQL maybe use Seq Scan?

Possibly.

Need to inspect the query.

Common Mistakes

Mistake 1

Thinking cost is milliseconds.

Wrong.

Mistake 2

Looking only at execution time.

Also inspect:

• estimated rows
• actual rows

Mistake 3

Ignoring scan type.

Always notice:

Seq
Index
Bitmap
Index Only

Mistake 4

Assuming an index must always be used.

False.

Senior-Level Interview Questions

Q1

Difference:

EXPLAIN
EXPLAIN ANALYZE

Q2

Why can PostgreSQL ignore an index?

Q3

What does

rows

mean?

Q4

Difference between

rows
actual rows

Q5

What is

loops

?

Q6

Difference between

Index Scan
Index Only Scan

Q7

Why is Bitmap Heap Scan useful?

Q8

Why isn’t cost measured in milliseconds?

Q9

How do stale statistics affect query plans?

Q10

Why should you run

ANALYZE

after major data changes?

Practical Exercises

Exercise 1

Run:

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

Write down:

• Scan type
• Estimated rows
• Actual rows
• Execution time

Exercise 2

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE city='Chicago';

Explain why PostgreSQL chose that plan.

Exercise 3

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 10;

Then create an index:

CREATE INDEX idx_users_created_at_desc
ON users(created_at DESC);

Run the query again and compare the plans.

Exercise 4

Run:

ANALYZE users;

Then compare the estimated rows with the actual rows again.

Senior Rails Interview Tips

If an interviewer gives you an execution plan, don’t immediately suggest adding an index.

Instead, ask:

1. How many rows are in the table?
2. How many rows does this query return?
3. What indexes already exist?
4. Is the planner’s estimate accurate?
5. Is the query actually slow?

That line of reasoning demonstrates experience much better than jumping straight to “add an index.”

What’s Next?

From here, I recommend Day 7: Query Optimization Workshop.

Unlike the previous lessons, it won’t introduce many new SQL keywords. Instead, we’ll work through real production-style problems, such as:

• A query that takes 8 seconds—how do we optimize it?
• Why did PostgreSQL choose a Nested Loop instead of a Hash Join?
• N+1 queries in Rails and how to eliminate them.
• OFFSET pagination vs keyset pagination.
• Rewriting slow SQL into faster SQL.
• Using indexes effectively rather than adding them blindly.

This is the stage where you’ll start thinking like a senior backend engineer rather than someone who simply knows SQL syntax.

Happy Learning! 🚀