Optimize Queries with Indexes

Your queries are slow. The dreaded SELECT that takes seconds when it should take milliseconds. You've added more RAM, tuned work_mem, and still, the database crawls. The single most effective lever you have is the index. In this lesson, you'll learn how to optimize queries with indexes — not by blindly adding them, but by understanding how PostgreSQL actually uses them, how to read the plan, and how to avoid the pitfalls that turn indexes into a liability.

The problem this lesson solves

Every table scan is a full read of every row. On a table with millions of rows, that's millions of disk reads per query. Indexes exist to skip that work, but they aren't magic. A missing index means a sequential scan — the database reads every row to find matches. An over-indexed table means every INSERT, UPDATE, and DELETE pays a write penalty. The real problem isn't "no indexes" — it's not knowing which indexes to create and not verifying they're used.

You've probably seen the advice: "just add an index on the column you filter by." But that's oversimplified. The wrong index type, a function wrapping the column, or a poorly ordered composite index can render your index useless. This lesson gives you a repeatable process to diagnose, fix, and verify query performance with indexes.

Core concept / mental model

Think of a table as a messy pile of papers. To find every invoice from March, you'd flip through the entire pile. That's a sequential scan. An index is like the alphabetical tab at the top of the pile — it tells you exactly where the March invoices are, so you skip straight to them.

In PostgreSQL, an index is a separate structure that stores a sorted copy of the indexed column(s) along with pointers to the actual rows. When a query's WHERE clause matches the index, PostgreSQL can jump directly to those rows instead of scanning the whole table.

The key mental model is trade-off: - Read speed — index speeds up SELECT filtering, sorting, and joining. - Write cost — every index adds work on INSERT, UPDATE, and DELETE. - Storage — indexes consume disk and memory.

So an index is not a universal win. It's a targeted tool you apply to the queries that matter.

The PostgreSQL query planner uses several index types: - B-tree — the default, works for equality and range queries (=, >, <, BETWEEN). - Hash — equality only, but faster for exact matches on large tables. - GIN — for full-text search and array containment (@>). - GiST — for geometry and range types.

You'll rarely need beyond B-tree, but knowing they exist helps you choose.

How it works step by step

To optimize a query, follow this process:

1. Identify the slow query — use logs or pg_stat_statements to find high-latency or high-frequency queries.
2. Run EXPLAIN ANALYZE — this shows the plan and actual execution times.
3. Look for Seq Scan — that's your smoking gun. The planner scanned the whole table.
4. Examine the WHERE clause — note the columns, operators, and any functions applied.
5. Decide on an index — create a B-tree index on the filter column(s). If the query uses ORDER BY, a B-tree index can also prevent a sort.
6. Re-run EXPLAIN ANALYZE — confirm the plan now shows an Index Scan or Bitmap Index Scan and that the actual time dropped.
7. Measure the write impact — if the table is write-heavy, weigh the benefit.

A composite index (on multiple columns) is best when the WHERE clause filters by multiple columns. The order matters: put the most selective column first, and ensure the leading column is actually used in a filter — otherwise the index may be ignored.

When an index won't help: - Filtering on a function of the column (e.g., WHERE lower(email) = 'x'). Use a functional index or rewrite. - The table is small — a seq scan may be faster. - The query returns a large percentage of rows — a seq scan is cheaper than random I/O. - The column's distribution is so skewed that the planner prefers a seq scan.

Hands-on walkthrough

Let's work with a practical example. Assume we have a table of orders:

Setup

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Insert 100,000 rows for testing
INSERT INTO orders (customer_id, status, created_at)
SELECT g, 'pending', NOW() - (g * interval '1 second')
FROM generate_series(1, 100000) g;

Before index

Run EXPLAIN ANALYZE on a query filtering by customer_id:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 500;

Output (abridged):

Seq Scan on orders  (cost=0.00..1899.00 rows=1 width=48) (actual time=6.324..18.221 rows=1 loops=1)
  Filter: (customer_id = 500)
Planning Time: 0.132 ms
Execution Time: 18.341 ms

The Seq Scan shows a full table read. Fix it by creating an index:

CREATE INDEX idx_orders_customer ON orders (customer_id);

After index

Re-run the same EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 500;

Output (abridged):

Index Scan using idx_orders_customer on orders  (cost=0.29..8.31 rows=1 width=48) (actual time=0.032..0.034 rows=1 loops=1)
  Index Cond: (customer_id = 500)
Execution Time: 0.045 ms

The execution time dropped from ~18 ms to ~0.04 ms — a 400x improvement!

Composite index example

Now filter by both customer_id and status:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 500 AND status = 'pending';

Without a composite index, you might see a bitmap scan combining two indexes. Better: create a composite index:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Now the plan uses that single index directly.

Verify with EXPLAIN (FORMAT JSON)

For a machine-readable plan, use:

EXPLAIN (FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 500;

This helps when you want to automate plan analysis.

Compare options / when to choose what

Also consider: - Partial index — index a subset of rows via a WHERE clause. Great for common queries like WHERE status = 'active'. - Functional index — index the result of a function, e.g., CREATE INDEX ON users (lower(email)). - Covering index — include extra columns with INCLUDE to avoid table lookups.

Pro tip: Don't create indexes preemptively. Let a slow query guide you — add one index at a time and measure the impact.

Troubleshooting & edge cases

• Index not used — if EXPLAIN still shows Seq Scan after creating an index, check:
• The column type matches the query's constant (e.g., text vs varchar).
• You're using a function on the column (fix with a functional index).
• The planner decides a seq scan is cheaper for a small table or high selectivity.
• Index bloat — after heavy UPDATE/DELETE, an index becomes bloated. Run REINDEX to rebuild it.
• Write slowdown — too many indexes on a INSERT-heavy table. Keep only essential ones.
• Too many indexes — each index doubles write work. Drop redundant or unused ones with DROP INDEX.
• EXPLAIN shows Bitmap Heap Scan — that's fine. It's a combination of an index and a heap scan, often used for multiple indexes.
• Null values — B-tree indexes include nulls by default, so WHERE col IS NULL can use them.

What you learned & what's next

You now have a repeatable method to optimize queries with indexes: 1. You can explain the core idea behind indexes and the trade-offs. 2. You can run EXPLAIN ANALYZE to identify a Seq Scan. 3. You can create and verify B-tree and composite indexes. 4. You know when to consider partial, functional, or other index types. 5. You know how to troubleshoot when an index is ignored.

Next in the track, you'll learn about vacuum and autovacuum — maintaining your tables and indexes so they stay fast over time. A bloated table or index can undo all your optimizations, so that's the perfect follow-up.

Keep practicing — the more you fiddle with EXPLAIN, the more intuitive index tuning becomes.