Tag: Unique Index

Learn SQL: Day 6B – Advanced Indexing (Senior-Level Insights)

Welcome to Day 6B.

Today we’ll go beyond “create an index” and learn how senior backend engineers decide which index to create.

This is one of the most valuable topics for PostgreSQL and Rails interviews.

By the end of this lesson, you should be able to answer questions like:

Why did you create that index?

instead of just saying

Because the query was slow.

Today’s Goals

We’ll learn:

  • Composite Indexes
  • Leftmost Prefix Rule
  • Covering Indexes (Index Only Scan)
  • Included Columns (INCLUDE)
  • Partial Indexes
  • Expression Indexes
  • Unique Indexes
  • Choosing the right index
  • B-tree vs Hash vs GIN vs GiST vs BRIN
  • Rails migration examples
  • Real production examples

Part 1 – Let’s Create a Realistic Table

We’ll use a slightly more realistic table.

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT,
    city TEXT,
    age INTEGER,
    active BOOLEAN,
    created_at TIMESTAMP
);

Populate it:

INSERT INTO users(email, city, age, active, created_at)
SELECT
    '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),
    (i % 10 <> 0),
    NOW() - (i || ' days')::interval
FROM generate_series(1,100000) i;

Part 2 – Composite Indexes

Suppose your application frequently runs:

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

Many developers think:

I’ll create two indexes.

CREATE INDEX idx_users_city
ON users(city);

CREATE INDEX idx_users_age
ON users(age);

Sometimes PostgreSQL can combine them using a Bitmap Index Scan.

But often, a composite index is even better.

CREATE INDEX idx_users_city_age
ON users(city, age);

How PostgreSQL Stores It

Think of it as sorting by the first column, then by the second.

Conceptually:

Boston
   20
   21
   22
   23
Chicago
   20
   21
   22
   23
   24
   25
Dallas
...

Notice:

Everything is ordered by:

city
↓
age

The Leftmost Prefix Rule

This is probably the most important composite-index interview question.

Suppose you have:

(city, age)

Will it help?

Query 1

WHERE city='Chicago'

✅ Yes

Because the index starts with city.

Query 2

WHERE city='Chicago'
AND age=30

✅ Yes

Perfect.

Query 3

WHERE age=30

❌ Usually No

Why?

Imagine a dictionary.

Can you find:

age = 30

without first knowing the city?

No.

The index is organised by city first.

Senior Interview Question

Which is better?

(city, age)

or

(age, city)

Answer:

It depends on your query patterns.

Suppose:

95% of queries are:

WHERE city='Chicago'

Choose:

(city, age)

Suppose:

95% are:

WHERE age=30

Choose:

(age, city)

There is no universally “better” order.

Practical Exercise

Create:

CREATE INDEX idx_users_city_age
ON users(city, age);

Now test:

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

Test:

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

Test:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE age=30;

Observe which queries use the composite index.

Part 3 – Covering Indexes

Suppose you run:

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

Question:

Why read the whole table?

The index already contains:

email
↓
pointer

If PostgreSQL can answer the query using only the index, it may perform an:

Index Only Scan

instead of:

Index Scan

Why Is It Faster?

Index Scan:

Index
↓
Find pointer
↓
Read table
↓
Return email

Index Only Scan:

Index
↓
Return email

The table doesn’t need to be accessed.

Important Detail

Index Only Scans are only possible when PostgreSQL knows that the table pages are “all-visible” via the Visibility Map, which is maintained by VACUUM.

We’ll study this in PostgreSQL Internals.

Try It

Create:

CREATE INDEX idx_users_email
ON users(email);

Run:

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

Sometimes you’ll see:

Index Only Scan

Sometimes:

Index Scan

We’ll later learn why.

Part 4 – INCLUDE Columns

Suppose your application frequently runs:

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

A normal email index stores:

email
↓
pointer

The planner still needs to visit the table to fetch city.

PostgreSQL allows:

CREATE INDEX idx_users_email_include
ON users(email)
INCLUDE(city);

Now:

  • email is part of the searchable index key.
  • city is stored in the index payload.

This can allow an Index Only Scan for:

SELECT email, city
...

without making city part of the search order.

Interview Question

Difference between:

(email, city)

and

(email)
INCLUDE(city)

Answer:

city in a composite index affects the index ordering and can be used for searching.

city in INCLUDE cannot be searched efficiently but can be returned without accessing the table.

Part 5 – Partial Indexes

One of PostgreSQL’s best features.

Suppose:

100,000 users
90,000 active
10,000 inactive

Your application only searches active users.

Instead of indexing everybody:

CREATE INDEX idx_users_active
ON users(active);

Create:

CREATE INDEX idx_active_email
ON users(email)
WHERE active=true;

Now the index contains only active users.

Advantages:

  • Smaller index
  • Faster scans
  • Less maintenance

Query

SELECT *
FROM users
WHERE active=true
AND email='user100@example.com';

Perfect candidate.

Rails Migration

add_index :users,
          :email,
          where: "active = true"

Part 6 – Expression Indexes

Suppose users log in with:

WHERE LOWER(email)=LOWER(?)

Without an expression index:

PostgreSQL can’t efficiently use a normal email index because you’re applying a function to the column.

Create:

CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

Now:

SELECT *
FROM users
WHERE LOWER(email)=LOWER('JOHN@test.com');

can use the index.

Rails Example

User.where(
  "LOWER(email)=?",
  email.downcase
)

Expression indexes are extremely common for case-insensitive searches.

Part 7 – Unique Indexes

Earlier we learned:

email UNIQUE

Internally PostgreSQL implements this using a unique index.

You can also create one directly:

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

Now duplicates are impossible.

Interview Question

Difference between:

UNIQUE CONSTRAINT

and

CREATE UNIQUE INDEX

Practically, both enforce uniqueness.

The preferred way for business rules is usually a UNIQUE constraint, which PostgreSQL implements using a unique index under the hood.

Part 8 – Index Types

So far we’ve only used:

B-tree

PostgreSQL supports several index types.

B-tree (Default)

Good for:

  • =
  • <
  • BETWEEN
  • ORDER BY

Most common.

Hash

Good for:

=

only.

Rarely needed because B-tree also supports equality efficiently.

GIN

Great for:

  • JSONB
  • Arrays
  • Full-text search

Rails examples:

where("tags @> ARRAY['ruby']")

or

where("metadata @> ?", ...)

GiST

Useful for:

  • Geospatial data
  • PostGIS
  • Range types

BRIN

Designed for huge tables where rows are naturally ordered.

Example:

Logs
Millions of rows
Ordered by timestamp

A BRIN index is tiny compared with a B-tree.

Quick Comparison

IndexBest Use
B-treeDefault choice
HashEquality only
GINJSONB, arrays, full-text
GiSTGeometry, ranges
BRINHuge sequential tables

Part 9 – Real Rails Examples

Login

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

Index:

(email)

User Orders

user.orders

Index:

(user_id)

Dashboard

Order.where(status: "pending")

Maybe:

(status)

But ask:

How selective is status?

If 95% are pending, maybe not.

Recent Orders

Order
  .order(created_at: :desc)
  .limit(20)

Good candidate:

(created_at)

Or even:

(created_at DESC)

Part 10 – Choosing the Right Index

Never ask:

Which index can I create?

Ask:

Which queries does my application actually run?

Example:

95%
WHERE email=?

Index email.

Example:

95%
WHERE city='Chicago'

Index city.

Example:

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

Composite index.

Indexes should be driven by query patterns, not by table columns.

Common Mistakes

Mistake 1

Creating:

(city)
(age)

when almost every query filters on both together.

Mistake 2

Wrong column order.

(age, city)

when almost every query starts with city.

Mistake 3

Using functions without expression indexes.

LOWER(email)

Mistake 4

Indexing everything.

Indexes are not free.

Senior-Level Insights

  1. Composite indexes should reflect how your application filters data, not simply the table schema.
  2. Partial indexes are often a better solution than full indexes when only a subset of rows is queried frequently.
  3. Expression indexes solve a very common performance problem when functions are applied in WHERE clauses.
  4. Covering indexes reduce table lookups and can enable Index Only Scans.
  5. The best index is the one that matches your most common query pattern—not necessarily the one that indexes the most columns.

Practical Exercises

Exercise 1

Create:

(city, age)

Run:

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

Exercise 2

Run:

SELECT *
FROM users
WHERE city='Boston'
AND age=35;

Observe the plan.

Exercise 3

Run:

SELECT *
FROM users
WHERE age=35;

Explain why the composite index is or isn’t used.

Exercise 4

Create:

CREATE INDEX idx_lower_email
ON users(LOWER(email));

Run:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE LOWER(email)=LOWER('user100@example.com');

Exercise 5

Create a partial index:

CREATE INDEX idx_active_users
ON users(email)
WHERE active=true;

Run:

SELECT *
FROM users
WHERE active=true
AND email='user100@example.com';

Compare the execution plan with and without the partial index.

Homework

  1. Create and test:
    • A composite index
    • A partial index
    • An expression index
    • A unique index
  2. For each index, answer:
    • Which query benefits?
    • Why?
    • Would a different index be better?
  3. Use EXPLAIN ANALYZE to verify your assumptions.

Day 6C Preview

Next, we’ll become execution plan detectives.

We’ll take real EXPLAIN ANALYZE outputs like:

Gather
Hash Join
Nested Loop
Memoize
Bitmap Heap Scan
Bitmap Index Scan
Sort
Aggregate
Limit
Materialize

and decode every single line.

By the end of Day 6C, we’ll be able to sit in a senior interview, look at a PostgreSQL execution plan, and explain not just what PostgreSQL did, but why it chose that plan. That is a skill that sets experienced backend engineers apart.

Happy Learning!