Welcome to Day 7.
Everything we’ve learned so far leads to this lesson.
Until now, you’ve learned:
- How to write SQL
- How JOINs work
- How GROUP BY works
- How indexes work
- How PostgreSQL chooses execution plans
Today, we’ll combine everything to solve real-world performance problems.
Today’s Goals
By the end of today, you’ll be able to answer questions like:
- Why is this query slow?
- Should I add an index?
- Should I rewrite the query?
- Is this a database problem or an application problem?
- How would I debug this in production?
These are exactly the kinds of discussions that happen in senior Rails interviews.
A Senior Engineer’s Workflow
Suppose your manager says:
“The Users page takes 8 seconds to load.”
A junior developer might immediately say:
“Let’s add an index.”
A senior developer thinks:
1. Is the query actually slow?2. Which query is slow?3. How much data is involved?4. What is PostgreSQL doing?5. Can I rewrite the query?6. Do I need an index?7. Is the application causing the problem?
Notice:
Adding an index is Step 6, not Step 1.
Our Practice Schema
Let’s build something closer to a real Rails application.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;
Users
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name TEXT,
email TEXT,
city TEXT
);
Products
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT,
price NUMERIC(10,2),
category TEXT
);
Orders
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
status TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
Order Items
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INTEGER,
price NUMERIC(10,2)
);
The Six-Step Performance Checklist
Every slow query investigation should start with this checklist.
Step 1 – Measure
Never optimise blindly.
Run:
EXPLAIN ANALYZESELECT ...
Step 2 – Understand the Business Question
Example:
Show the last 20 completed orders.
Don’t optimise before understanding what the query should do.
Step 3 – Read the Plan
Look for:
- Seq Scan
- Nested Loop
- Hash Join
- Sort
- Aggregate
- Bitmap Heap Scan
Step 4 – Find the Bottleneck
Ask:
- Which node took the most time?
- Which node processed the most rows?
Step 5 – Decide the Fix
Possible fixes:
- Better index
- Better SQL
- Better schema
- Better ActiveRecord
- Better pagination
Step 6 – Measure Again
Never assume the optimisation worked.
Always compare before and after.
Scenario 1 – Missing Index
Query:
SELECT *FROM usersWHERE email='john@example.com';
Execution plan:
Seq Scanrows=100000actual rows=1
Question:
What’s wrong?
Diagnosis
No index on email.
Fix
CREATE INDEX idx_users_emailON users(email);
Run again:
EXPLAIN ANALYZESELECT *FROM usersWHERE email='john@example.com';
Expect:
Index Scan
Scenario 2 – Wrong Index
Suppose the application runs:
SELECT *FROM usersWHERE city='Chicago'AND age=30;
Indexes:
(city)(age)
Question:
Better solution?
Answer
Composite index:
CREATE INDEX idx_city_ageON users(city, age);
Because the application almost always filters by both.
Scenario 3 – Sorting
Query:
SELECT *FROM ordersORDER BY created_at DESCLIMIT 20;
Plan:
Seq Scan↓Sort↓Limit
Question:
Can we avoid sorting?
Solution
CREATE INDEX idx_orders_created_at_descON orders(created_at DESC);
Now PostgreSQL can read the index in order.
Often:
Index Scan↓Limit
No Sort node.
Scenario 4 – N+1 Queries
Rails code:
orders = Order.limit(100)
orders.each do |order|
puts order.user.name
end
SQL executed:
SELECT * FROM orders LIMIT 100;
Then:
SELECT * FROM users WHERE id=1;
SELECT * FROM users WHERE id=2;
…
100 additional queries.
Total
101 queries
Fix
Order.includes(:user)
Now:
SELECT * FROM orders;
SELECT *FROM usersWHERE id IN (...);
Two queries.
Interview Question
Which is faster?
includes
or
joins
Answer:
They solve different problems.
Scenario 5 – OFFSET Pagination
Query:
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20
OFFSET 100000;
Looks harmless.
But PostgreSQL must skip:
100000 rows
before returning:
20 rows
Large OFFSET values become increasingly expensive.
Better Solution
Keyset Pagination.
Instead of:
OFFSET 100000
Use:
WHERE created_at < '2026-07-01'
ORDER BY created_at DESC
LIMIT 20;
This lets PostgreSQL continue from the last seen row instead of counting through earlier rows.
Rails Example
Instead of:
Order.order(created_at: :desc)
.offset(100000)
.limit(20)
Use:
Order
.where("created_at < ?", last_created_at)
.order(created_at: :desc)
.limit(20)
This is called keyset pagination or cursor pagination.
Scenario 6 – SELECT *
Query:
SELECT *FROM users;
Returns:
idnameemailcityaddressbioavatar...
Suppose the page only displays:
- name
- city
Why fetch everything?
Better:
SELECT name, cityFROM users;
Rails:
User.select(:name, :city)
Scenario 7 – COUNT(*)
Suppose:
SELECT COUNT(*)FROM orders;
On:
300 million rows
Question:
Can this be slow?
Yes.
Because PostgreSQL must count visible rows.
Unlike some databases, PostgreSQL generally doesn’t maintain an exact row count that’s instantly available for arbitrary COUNT(*).
Scenario 8 – DISTINCT
Query:
SELECT DISTINCT users.*FROM usersJOIN ordersON users.id=orders.user_id;
Question:
Why is DISTINCT needed?
Because JOIN duplicates users.
Could EXISTS express the requirement more directly?
SELECT *FROM users uWHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id=u.id);
Sometimes that’s clearer.
Scenario 9 – Functions in WHERE
Query:
SELECT *FROM usersWHERE LOWER(email)='john@example.com';
Normal email index?
Not useful.
Solution:
CREATE INDEX idx_lower_emailON users(LOWER(email));
Expression index.
Scenario 10 – Too Many Indexes
Suppose:
users↓12 indexes
Problem?
Every INSERT must update:
Table+12 indexes
Indexes speed reads but slow writes.
Always consider the workload.
Optimization Decision Tree
When a query is slow, ask:
Is PostgreSQL scanning too many rows? │ ▼Yes │ ▼Would an index help? │ ▼Yes │ ▼Do I already have one? │ ▼No │ ▼Create the correct index.
But also ask:
Am I returning unnecessary data?Am I sorting unnecessarily?Am I joining unnecessarily?Am I executing the query too many times?
Real Rails Optimization Example
Suppose this page loads slowly:
@orders = Order
.where(status: "completed")
.order(created_at: :desc)
.limit(20)
Questions:
- Is there an index on
status? - Is there an index on
created_at? - Would a composite index help?
Potential solution:
CREATE INDEX idx_orders_status_created_at
ON orders(status, created_at DESC);
Why?
Because the query filters by status and orders by created_at.
Senior Interview Exercise
Suppose you see:
SELECT *FROM ordersWHERE user_id = 100ORDER BY created_at DESCLIMIT 20;
Which index would you create?
Many people answer:
(user_id)(created_at)
A stronger answer is:
CREATE INDEX idx_orders_user_createdON orders(user_id, created_at DESC);
Because it supports both the filter and the ordering in one index.
Common Performance Mistakes
Mistake 1
Adding indexes without measuring.
Mistake 2
Using SELECT * everywhere.
Mistake 3
Ignoring N+1 queries.
Mistake 4
Using huge OFFSET values.
Mistake 5
Creating duplicate indexes.
Mistake 6
Ignoring EXPLAIN ANALYZE.
Senior-Level Mental Model
Every query has a “cost.”
The cost comes from:
Rows read+Rows sorted+Rows joined+Rows transferred+Application round trips
The goal of optimisation is to reduce one or more of these.
Practical Exercises
Exercise 1
Create:
CREATE INDEX idx_orders_user_createdON orders(user_id, created_at DESC);
Run:
EXPLAIN ANALYZESELECT *FROM ordersWHERE user_id=1ORDER BY created_at DESCLIMIT 20;
Observe whether PostgreSQL can avoid an explicit Sort.
Exercise 2
Compare:
SELECT *FROM users;
with:
SELECT name, cityFROM users;
Think about the amount of data returned.
Exercise 3
Write three versions of:
“Find users with completed orders.”
Using:
- JOIN
- EXISTS
- IN
Then compare their execution plans.
Exercise 4
Find a query that performs a Seq Scan.
Add an appropriate index.
Run EXPLAIN ANALYZE again.
What changed?
Interview Case Study
Imagine you’re in a senior Rails interview.
The interviewer says:
“A customer reports that the Orders page takes 6 seconds to load.”
A strong answer isn’t:
“I’ll add an index.”
A stronger answer is:
- Reproduce the issue.
- Identify the SQL generated by ActiveRecord.
- Run
EXPLAIN ANALYZE. - Inspect scan types, joins, and sort operations.
- Check existing indexes.
- Decide whether the fix belongs in the SQL, indexes, ActiveRecord code, or schema.
- Measure again after the change.
That systematic approach demonstrates senior-level thinking.
Homework
Build a small benchmark using your practice schema.
- Populate:
- 100,000 users
- 500,000 orders
- Measure these queries before and after adding indexes:
- Find a user by email.
- Find recent orders for a user.
- Find completed orders.
- Find users with no orders.
- For each query, record:
- Execution plan
- Execution time
- Scan type
- Rows estimated
- Rows returned
- Explain why PostgreSQL chose each plan.
What’s Next?
At this point, you’re already covering topics that many experienced Rails developers never study in depth.
For Day 8, I recommend Window Functions:
ROW_NUMBER()RANK()DENSE_RANK()LAG()LEAD()- Running totals
- Moving averages
- Top N per group
Window functions are common in reporting, analytics, and senior backend interviews because they solve problems that are difficult or inefficient with plain GROUP BY. Understanding them will significantly broaden your SQL toolkit.
Happy Learning! 🚀
The Rails Drop 