Module 25 — Indexes

1. What is an Index?

What it is: An index is a data structure (typically a B-tree) that maintains a sorted copy of specific columns, allowing the database to find rows without scanning the entire table. It's like a book's index — instead of reading every page, you jump directly to the relevant section.

Why we use it: Without indexes, PostgreSQL performs a sequential scan — reading every row to find matches. On a table with millions of rows, this is slow. Indexes reduce query time from seconds to milliseconds.

When we use it: On columns used in WHERE clauses, JOIN conditions, ORDER BY, and GROUP BY. Don't index small tables or columns with very few unique values.

-- Create an index on the email column
CREATE INDEX idx_users_email ON users(email);
 
-- Now this query is fast:
SELECT * FROM users WHERE email = 'alice@example.com';

2. Index Types

What it is: PostgreSQL supports several index types optimized for different data patterns. B-tree is the default and works for most cases. Hash is for exact equality, GIN for arrays/JSONB/full-text, and GiST for geometric/range data.

Why we use it: Different data patterns benefit from different index structures. Using the right index type can significantly improve performance for specific query patterns.

When we use it: B-tree for 90% of cases. GIN when indexing JSONB columns or arrays. GiST for geometric data or range types. Hash rarely (B-tree is usually better).

-- B-tree (default, most common)
CREATE INDEX idx_users_email ON users(email);
 
-- Hash (equality only, smaller)
CREATE INDEX idx_users_email_hash ON users(email) USING hash;
 
-- GIN (for JSONB)
CREATE INDEX idx_data_metadata ON events USING gin(metadata);
 
-- GiST (for geometric data)
CREATE INDEX idx_locations_coords ON

3. When to Add Indexes

What it is: Not every column needs an index. Indexes speed up reads but slow down writes (every INSERT/UPDATE/DELETE must also update the index). The key is to index columns that are frequently used in queries.

Why we use it: Over-indexing wastes storage and slows writes. Under-indexing causes slow queries. Finding the right balance is a core DBA skill.

When we use it: Index columns in WHERE clauses, JOIN conditions (foreign keys), ORDER BY, and GROUP BY. Don't index tiny tables, low-cardinality columns (booleans), or columns rarely used in queries.

4. Partial Indexes

What it is: A partial index only indexes rows that match a WHERE condition. For example, indexing only active users or only published posts. The index is smaller and faster to maintain.

Why we use it: When you only query a subset of rows — "active users", "published posts", "pending orders" — a partial index is more efficient than indexing the entire table.

When we use it: When queries consistently filter by the same condition, when the table has a large number of rows that are rarely queried, or when you want to reduce index size.

-- Only index active users
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
 
-- Only index published posts
CREATE INDEX idx_published_posts ON posts(created_at) WHERE published = true;

5. EXPLAIN ANALYZE — Debug Slow Queries

What it is: EXPLAIN ANALYZE shows the query execution plan and actual runtime statistics. It tells you which scan type was used (Seq Scan vs Index Scan), how many rows were processed, and how long it took.

Why we use it: Without EXPLAIN, you're guessing about performance. It shows you exactly how PostgreSQL executes your query — whether it uses an index, how many rows it scans, and where the bottleneck is.

When we use it: Every time a query is slow, when verifying that an index is being used, when comparing query plans, or when optimizing database performance.

-- See the query plan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
 
-- See the query plan AND actual execution time
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

Reading EXPLAIN Output

Seq Scan on users  (cost=0.00..1.04 rows=1 width=...)
  Filter: (email = 'alice@example.com'::text)

• Seq Scan — Full table scan (slow for large tables)
• Index Scan — Using an index (fast)
• cost — Estimated cost (lower is better)
• rows — Estimated rows returned

6. Composite Indexes

What it is: A composite index covers multiple columns in a single index. The order of columns matters — the index can be used for queries that filter by the leftmost column(s), but not for queries that skip to later columns.

Why we use it: When queries frequently filter by multiple columns together — WHERE category = 'Electronics' AND price > 1000. A composite index on (category, price) is faster than two separate indexes.

When we use it: When queries consistently filter by the same combination of columns, when building covering indexes, or when optimizing multi-column WHERE clauses.

-- Index on category + price (for queries that filter by both)
CREATE INDEX idx_products_category_price ON products(category, price);

Common Mistakes to Avoid

1. Over-indexing — Every index slows down writes. Only index what you actually query.
2. Indexing small tables — Full scan on 100 rows is faster than using an index
3. Not using EXPLAIN — Always verify your index is actually being used
4. Wrong column order in composite indexes — Most selective column first

Practice Exercise

1. Create an index on your courses table for the title column
2. Use EXPLAIN ANALYZE to compare query speed with and without the index
3. Create a partial index for published courses only
4. Create a composite index on (instructor, price)
5. Verify the index is used with EXPLAIN ANALYZE