Indexes for Common Queries
Add indexes for the queries you'll run most: by user, by date, by foreign key, and measure the impact with EXPLAIN.
Indexes for Common Queries is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is an Index?
An index is a separate on-disk data structure that lets the database find rows by column value without scanning the table. The classic implementation is a B-tree.
Indexes Trade Writes for Reads
Every index makes INSERT/UPDATE/DELETE slightly slower (the index must be updated too). Don't add indexes you don't need.
Primary Keys Get a Free Index
Declaring PRIMARY KEY automatically creates a unique B-tree index. Same for UNIQUE columns.
Index Foreign Key Columns
PostgreSQL does NOT auto-index the child side of an FK. Always add one — without it, deleting a parent scans all children:
CREATE INDEX comments_post_id_idx ON comments(post_id);
CREATE INDEX comments_author_id_idx ON comments(author_id);Index Columns Used in WHERE
If you filter by created_at a lot, index it:
CREATE INDEX posts_created_at_idx ON posts(created_at DESC);
-- Query benefits:
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;Composite Indexes
For multi-column filters, a composite index can be much faster than two single-column ones:
CREATE INDEX posts_author_published_idx
ON posts(author_id, published_at DESC);
-- This query uses the index for both filter and sort:
SELECT * FROM posts
WHERE author_id = 42
ORDER BY published_at DESC LIMIT 10;Left-Prefix Rule
A composite index (a, b, c) can serve WHERE a = ? and WHERE a = ? AND b = ? — but NOT WHERE b = ? alone. Order matters.
Partial Indexes
Index only the rows you query:
-- Only published posts:
CREATE INDEX posts_published_idx
ON posts(published_at DESC)
WHERE published_at IS NOT NULL;
-- Smaller index, faster lookups for the common case.Expression Indexes
Index a function of a column:
CREATE INDEX users_email_lower_idx ON users(LOWER(email));
-- Query that uses it:
SELECT * FROM users WHERE LOWER(email) = LOWER($1);Don't Over-Index
Rules of thumb:
- Index PK ✓ (automatic)
- Index UNIQUE columns ✓ (automatic)
- Index FK columns ✓
- Index columns used in WHERE / JOIN / ORDER BY of your hot queries
- Don't index columns with very low selectivity (e.g. a boolean with 50/50 split)
Measure with EXPLAIN
Before and after adding an index, run EXPLAIN ANALYZE to verify the planner is using it:
EXPLAIN ANALYZE
SELECT * FROM posts WHERE author_id = 42 ORDER BY published_at DESC LIMIT 10;Recap
For our blog: index the FKs (post_id on comments, author_id on posts and comments) and (author_id, published_at DESC) on posts for "latest posts by user" queries.
Quick Check
You added a foreign key orders.user_id REFERENCES users(id). What index, if any, should you create?
Frequently asked questions
Is the “Indexes for Common Queries” lesson free?
Yes — the full text of “Indexes for Common Queries” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Indexes for Common Queries”?
Add indexes for the queries you'll run most: by user, by date, by foreign key, and measure the impact with EXPLAIN. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Indexes for Common Queries” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Modelling a Blog: Users Posts Comments
- Choosing Keys and Types
- Indexes for Common Queries
- Seeding the Database with Test Data