Indexes and query speed
How indexes actually help, composite column order, and reading a query plan to find the real bottleneck.
What an index gives you
An index is a sorted copy of a few columns with pointers to rows — like a book index. It turns a full-table scan into a targeted lookup for selective queries.
CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email ON users (email);
DROP INDEX idx_products_category;- Indexes speed reads but cost writes — every insert or update maintains them.
- Most planners ignore an index when the predicate returns a large share of rows.
- Small tables are faster to scan than to index.
Column order matters
A composite index on (customer_id, created_at) also serves queries filtering only on customer_id — but not ones filtering only on created_at. This left-prefix rule drives most index design.
| Query | Uses (customer_id, created_at)? |
|---|---|
WHERE customer_id = 5 | Yes |
WHERE customer_id = 5 AND created_at > x | Yes — both columns |
WHERE created_at > x | No — needs its own index |
Reading a plan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;| Look for | Means |
|---|---|
Seq Scan | Full table read — fine for tiny tables, a red flag for big ones |
Index Scan | Index used, then rows fetched |
Index Only Scan | Everything came from the index — fastest |
| high cost / actual time | Where the query really spends time |
💡
Wrap columns in functions and indexes stop working:
WHERE DATE(created_at) = '2026-01-01' cannot seek. Use a range instead: created_at >= x AND created_at < x + 1 day.Practical habits
- Index foreign keys — databases rarely do it for you automatically.
- Prefer equality-first index column order, then sort/range columns.
- Avoid leading wildcards:
LIKE '%term'cannot use a b-tree index. - Measure with
EXPLAIN ANALYZErather than guessing.
FAQ
Why is my query slow even with an index?
Usually low selectivity (too many matching rows), a function wrapped around the column, or a type mismatch — comparing an integer column to a string often defeats the index.
How many indexes is too many?
Read-heavy tables tolerate several; write-heavy ones suffer quickly. Review with usage statistics and drop what queries never touch.
Related
SELECT: reading data Aggregation and GROUP BY
Last refreshed 2026-09-17.