SQL Indexes and Query Performance

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Write CREATE INDEX statements
Understand column order in a composite index
Explain the trade-offs of indexes

Let's break it down simply

An index is a data structure that lets you find rows quickly without scanning the whole table. It's a great fit for selective columns that show up often in WHERE, JOIN, and ORDER BY. Too many indexes, though, and your write and storage costs climb.

sql

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 10;

You should see

Index Scan using idx_orders_customer_created on orders

Try it yourself

Create a unique index on the email column of the users table, then try inserting a duplicate email and see what happens.

PostgreSQL Indexes — PostgreSQL

Easy traps

Indexing every single column
Choosing composite index column order without considering the actual query pattern

Exercise

Create a unique index on the email column of the users table, then try inserting a duplicate email and see what happens.

You'll know it worked when: Index Scan using idx_orders_customer_created on orders