Indexing Basics

Introduction

An index is a data structure (usually a B-tree) that lets the DB jump to rows by column value in O(log n) instead of O(n). Indexes are the single biggest perf lever in SQL.

Understanding the topic

Core concepts to understand:

• CREATE INDEX idx_orders_user ON orders(user_id);
• B-tree is the default; hash, GIN, GiST, BRIN exist for special cases.
• Speeds up SELECT/JOIN/ORDER BY.
• Slows down INSERT/UPDATE/DELETE slightly.
• Costs disk space.

Syntax reference

Visual workflow / architecture:

Without index: SCAN every row in 10M rows  ── slow
With   index:  seek B-tree, jump to row     ── fast

           [B-tree on email]
                root
              /   |   \
           ...   ...  ...
           leaves → row pointers

Real-world use

Adding the right index is the most common SQL perf fix. A missing index can make a 5-millisecond query 50 seconds.

Best practices

• Index columns in WHERE, JOIN, ORDER BY.
• Don't index everything — every index slows writes.
• Drop unused indexes (Postgres pg_stat_user_indexes).

Common mistakes

• Indexing tiny tables — pointless.
• Indexing low-selectivity columns (e.g., boolean) by themselves — rarely helps.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What is a B-tree index?
• Q2. Read/write trade-off of indexes.
• Q3. When does an index NOT help?
• Q4. Hash vs B-tree.
• Q5. How to find unused indexes.