Day: February 20, 2024

Learn SQL: Day 6 – Indexes & EXPLAIN ANALYZE

Welcome to Day 6.

Today marks an important milestone in this course.

Up until now, we’ve focused on writing correct SQL.

From today onward, we’ll focus on writing fast SQL.

This is one of the biggest differences between a mid-level Rails developer and a senior Rails developer.

A mid-level developer asks:

“Does my query work?”

A senior developer asks:

“How many rows did PostgreSQL have to examine to answer this query?”

Today’s Goals

By the end of today, you should understand:

  • What an index is
  • How PostgreSQL uses indexes
  • B-Tree indexes
  • Sequential Scan
  • Index Scan
  • Bitmap Index Scan
  • EXPLAIN
  • EXPLAIN ANALYZE
  • When indexes help
  • When indexes hurt
  • Composite indexes
  • Foreign key indexes
  • ActiveRecord index creation
  • Common interview questions

A Senior Engineer’s Mental Model

Imagine you have a book with 2 million pages.

You need to find:

Ruby on Rails

Without an Index

You start from page 1.

Page 1
Page 2
Page 3
...
Page 2,000,000

This is a Sequential Scan.

With an Index

You open the index section at the back of the book.

Ruby on Rails → Page 1,542,381

Immediately jump there.

This is an Index Scan.

That analogy is almost exactly how database indexes work.

Part 1 – Create a Practice Table

We’ll create a larger dataset than before.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    city VARCHAR(100),
    age INTEGER,
    active BOOLEAN DEFAULT true
);

Insert Sample Data

Instead of inserting thousands of rows manually, PostgreSQL provides a wonderful function:

generate_series()

We’ll use it a lot.

INSERT INTO users(name, email, city, age)
SELECT
    'User ' || i,
    'user' || i || '@example.com',
    CASE
        WHEN i % 4 = 0 THEN 'Boston'
        WHEN i % 4 = 1 THEN 'Chicago'
        WHEN i % 4 = 2 THEN 'New York'
        ELSE 'Dallas'
    END,
    20 + (i % 40)
FROM generate_series(1,100000) i;

Congratulations.

You now have:

100,000 users

Verify:

SELECT COUNT(*)
FROM users;

Output:

100000

Part 2 – Why Indexes Exist

Suppose we search:

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

Without an index:

PostgreSQL has to inspect rows one by one.

User 1
No
↓
User 2
No
↓
User 3
No
↓
...
↓
User 75,000
YES

Potentially:

75,000 comparisons

Part 3 – See PostgreSQL’s Plan

Instead of running:

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

Run:

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

You will likely see something similar to:

Seq Scan on users

This means:

Sequential Scan

Interview Question:

What is a Sequential Scan?

Answer:

PostgreSQL reads every row (or nearly every row) in the table to evaluate the query condition.

Part 4 – Create an Index

Let’s create one.

CREATE INDEX idx_users_email
ON users(email);

Run:

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

Now you’ll likely see:

Index Scan

Congratulations!

You just made your first query significantly faster.

What Is an Index?

An index is a separate data structure maintained by PostgreSQL.

Conceptually:

users table
id
name
email
...
↓
Index
email
↓
Row Location

Notice:

The table itself is not sorted.

The index is.

Part 5 – B-Tree Index

The default PostgreSQL index type is:

B-tree

Rails interviewers love asking:

What type of index does PostgreSQL create by default?

Answer:

B-tree

Good for:

  • =
  • <
  • BETWEEN
  • ORDER BY

Create explicitly:

CREATE INDEX idx_users_age
ON users
USING btree(age);

Usually:

CREATE INDEX idx_users_age
ON users(age);

creates the same thing.

Part 6 – EXPLAIN ANALYZE

Very important.

Difference:

EXPLAIN

Shows what PostgreSQL plans to do.

EXPLAIN ANALYZE

Actually runs the query and measures it.

Run:

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

Output looks similar to:

Index Scan
Planning Time: 0.2 ms
Execution Time: 0.1 ms

Notice:

Planning time

vs

Execution time.

Reading EXPLAIN ANALYZE

Typical output:

Index Scan using idx_users_email
(cost=0.42..8.44)
(rows=1)
(width=65)
(actual time=0.03..0.04)
(actual rows=1)

Don’t panic.

We’ll learn each part.

rows

Estimated rows.

Example:

rows=1

Planner expects:

1 row

actual rows

Returned rows.

actual rows=1

Perfect.

If estimates differ greatly from actual rows, PostgreSQL may choose a poor plan.

This is one reason why running ANALYZE (to refresh table statistics) matters.

Part 7 – Why Doesn’t PostgreSQL Always Use an Index?

This surprises many developers.

Suppose:

SELECT *
FROM users;

Would an index help?

No.

You need every row.

Sequential Scan is faster.

Suppose:

SELECT *
FROM users
WHERE active=true;

Imagine:

98%
of users
are active.

Would using an index help?

Usually not.

Why?

Using the index would require PostgreSQL to:

  • traverse the index
  • then visit almost every table row anyway

Sometimes a Sequential Scan is cheaper.

Interview Question:

Why might PostgreSQL ignore an index?

Good answer:

Because the planner estimates that scanning the entire table is cheaper than using the index, often due to low selectivity or because a large percentage of rows match the condition.

Part 8 – Selectivity

A crucial concept.

Imagine:

Gender
Male
Female

Only two values.

Index?

Not very useful.

Now:

email

Every row unique.

Excellent index.

Rule of thumb:

Higher uniqueness

↓

Better selectivity

↓

More useful index

Examples:

Good:

email
UUID
order_number
tracking_number

Poor:

gender
active
status (if only a few values)

Part 9 – Composite Indexes

Suppose we often search:

SELECT *
FROM users
WHERE city='Chicago'
AND age=25;

Instead of:

CREATE INDEX idx_city;
CREATE INDEX idx_age;

We can create:

CREATE INDEX idx_city_age
ON users(city, age);

Interview Question:

Will this index help?

WHERE city='Chicago'

Yes.

Will it help?

WHERE city='Chicago'
AND age=25

Yes.

Will it help?

WHERE age=25

Usually No.

This is called the Leftmost Prefix Rule.

Leftmost Prefix Rule

For an index:

(city, age)

Efficient for:

WHERE city='Chicago'

and

WHERE city='Chicago'
AND age=25

Not generally for:

WHERE age=25

because the index is ordered by city first.

Part 10 – Indexes on Foreign Keys

Consider:

orders
user_id

Rails creates:

belongs_to :user

You often query:

SELECT *
FROM orders
WHERE user_id=5;

Should user_id be indexed?

Absolutely.

Without it:

Every order
↓
Scan

With it:

Jump directly to user 5's orders.

Rails Migration

add_reference :orders,
              :user,
              foreign_key: true,
              index: true

or

t.references :user,
             foreign_key: true

Rails creates the index automatically.

Part 11 – Rails Examples

Find by email:

User.find_by(email: email)

Should email be indexed?

Yes.

Authentication:

User.find_by(email: params[:email])

Index?

Definitely.

Showing a user’s orders:

user.orders

Queries:

WHERE user_id=?

Index?

Yes.

Searching by created_at:

Order.order(created_at: :desc)

Index?

Often yes, especially for recent-record queries or pagination.

Part 12 – Bitmap Index Scan

Sometimes PostgreSQL combines indexes.

Example:

city='Chicago'
AND
age=30

Two separate indexes:

idx_city
idx_age

Planner may choose:

Bitmap Index Scan

Meaning:

  • scan both indexes
  • combine the matching row locations
  • visit the table once

This can be efficient when no suitable composite index exists.

Part 13 – Common Mistakes

Mistake 1

Adding indexes to everything.

Indexes:

  • consume disk space
  • slow INSERTs
  • slow UPDATEs
  • slow DELETEs

Because PostgreSQL must maintain them.

Mistake 2

Ignoring foreign key indexes.

Mistake 3

Indexing low-cardinality columns.

Example:

is_admin
true
false

Usually poor selectivity.

Mistake 4

Creating duplicate indexes.

For example:

(email)
(email)

Wasteful.

Part 14 – Interview Questions
Q4

Check here: https://railsdrop.com/learn-sql-day-6-part-14-interview-questions/

Practical Exercises

Exercise 1

Run:

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

Observe the plan.

Exercise 2

Drop the email index.

DROP INDEX idx_users_email;

Run EXPLAIN again.

Compare the plan.

Exercise 3

Create an index on:

city

Run:

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

Exercise 4

Create:

(city, age)

Test:

WHERE city='Chicago'
WHERE city='Chicago'
AND age=30
WHERE age=30

Compare the execution plans.

Exercise 5

Create an orders table with at least 100,000 rows (using generate_series() again) and compare the execution plans for:

SELECT *
FROM orders
WHERE user_id = 500;

before and after creating an index on user_id.

Senior-Level Insights

  1. Indexes don’t make every query faster. They help when they allow PostgreSQL to avoid scanning most of the table.
  2. The optimizer chooses the execution plan, not you. Your job is to give it good indexes and accurate statistics.
  3. Always verify assumptions with EXPLAIN ANALYZE. Never assume an index is being used.
  4. Composite indexes should reflect your most common query patterns, not just individual columns.
  5. In Rails interviews, be ready to explain why you would add an index, not just how.

Homework

  1. Create indexes on:
    • email
    • city
    • (city, age)
  2. For each of the following, run both EXPLAIN and EXPLAIN ANALYZE:
SELECT *
FROM users
WHERE email='user100@example.com';
SELECT *
FROM users
WHERE city='Boston';
SELECT *
FROM users
WHERE city='Boston'
AND age=25;
SELECT *
FROM users
WHERE age=25;

Record:

  • Scan type
  • Estimated rows
  • Actual rows
  • Execution time
  1. Explain in your own words why the composite index helps some queries but not others.

Interview Challenge

Suppose your Rails application frequently runs:

Order.where(user_id: current_user.id)
     .order(created_at: :desc)
     .limit(20)

Think about these questions before Day 7:

  1. Which columns would you index?
  2. Would separate indexes be enough?
  3. Would a composite index be better?
  4. If so, what column order would you choose, and why?

This is a classic senior backend interview question, and we’ll answer it when we cover advanced indexing and query optimization.

Answers: https://railsdrop.com/learn-sql-day-6-part-14-interview-questions#Interview Challenge/