Create Indexes for Faster Queries

You've probably felt it: a query that once returned instantly now crawls, grinding through millions of rows before giving you a single answer. The culprit is usually a missing index, and the fix is one of the most satisfying wins in PostgreSQL: a single CREATE INDEX statement can turn a painful "full scan" into a lightning-fast lookup. In this lesson, you'll learn how to create indexes for faster queries, understand the core concept behind them, and get hands-on practice that you can apply to your own databases right away.

The problem this lesson solves

Every time PostgreSQL runs a query, it has to find the rows you've asked for. Without an index, the database performs a sequential scan: it reads every row in the table, one by one, checking each against your WHERE clause. For a small table, that's fine. But when your table grows to millions of rows, a sequential scan becomes painfully slow.

Consider a simple lookup like SELECT * FROM users WHERE email = 'alice@example.com';. Without an index on email, PostgreSQL must examine every row in the users table to find that one email. That's an O(n) operation that becomes your bottleneck as your data grows. It's like searching for a contact in a phone book that isn't alphabetized — you'd have to read every entry.

Pro tip: Slow queries don't just affect your users; they can also consume CPU and I/O, which can drag down your whole database server. Eliminating avoidable sequential scans is often the first step in a performance tuning session.

Core concept / mental model

Think of a PostgreSQL index as a book index or a phone book's alphabetical listing. The index is a separate data structure that stores a copy of the indexed column's values, sorted, along with a pointer to the actual row in the table. When you query on that column, PostgreSQL can quickly jump to the exact location in the index instead of scanning the entire table.

The most common type of index in PostgreSQL is the B-tree index. B-trees are balanced trees that allow fast lookups, range scans, and sorted traversal. They're ideal for equality and range conditions — exactly what most production queries use.

Here's a mental picture:

• Without an index: you scan every row (the full table).
• With a B-tree index: you do a binary-search-like navigation in a sorted structure, then jump directly to the matching row(s).

Indexes also speed up ORDER BY and JOIN operations because they provide pre-sorted data. However, they are not free: every insert, update, or delete must maintain the index, which adds overhead. Trade-off time vs. write cost is the core tension you'll manage.

How it works step by step

Step 1: Prepare the environment

Before you can create an index, you need a table with some data. We'll use a fictional e-commerce database with an orders table. The following commands create the table and insert a few sample rows so you can practice.

CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total NUMERIC(10,2),
    status TEXT,
    ordered_at TIMESTAMP DEFAULT now()
);

INSERT INTO orders (customer_id, total, status) VALUES
(101, 250.00, 'paid'),
(102, 89.50, 'paid'),
(103, 120.00, 'shipped'),
(101, 75.00, 'pending'),
(104, 200.00, 'paid');

Step 2: Check the current query plan

PostgreSQL provides the EXPLAIN command to show how it would execute a query. Before creating an index, run EXPLAIN on a search query to see the sequential scan.

EXPLAIN SELECT * FROM orders WHERE customer_id = 101;

The output will look something like:

 Seq Scan on orders  (cost=0.00..1.08 rows=2 width=28)
   Filter: (customer_id = 101)

The Seq Scan tells you the full table is being read. For this tiny table, it's fine, but you can imagine the cost on a real table.

Step 3: Create the index

To speed up queries that filter on customer_id, you'd create an index on that column:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Now, run EXPLAIN again:

EXPLAIN SELECT * FROM orders WHERE customer_id = 101;

The output should show an Index Scan:

 Index Scan using idx_orders_customer_id on orders  (cost=0.14..8.16 rows=2 width=28)

The index scan is usually much cheaper for large tables.

Step 4: Understand index creation overhead

Creating an index locks the table to writes (in PostgreSQL's default simple mode) and takes time proportional to the table size. For large tables, you might want to use CONCURRENTLY to avoid blocking writes, but note that you cannot run CONCURRENTLY inside a transaction block.

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

Step 5: Drop an index when it's not helping

Use DROP INDEX to remove an index that isn't used or that hurts write performance.

DROP INDEX idx_orders_customer_id;

Hands-on walkthrough

Let's put it together in a complete practice session. We'll create a table, populate it with enough rows to feel the difference, and measure query times with and without an index.

Setup: generate sample data

Using generate_series and random() we can create 10,000 orders quickly. (We'll use a separate table for clarity.)

CREATE TABLE IF NOT EXISTS orders_perf (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total NUMERIC(10,2),
    status TEXT DEFAULT 'pending'
);

INSERT INTO orders_perf (customer_id, total)
SELECT (random() * 1000)::int, round((random() * 500)::numeric, 2)
FROM generate_series(1, 10000);

Test query without index

First, measure the query time and check the plan.

-- Turn on timing
\timing

SELECT * FROM orders_perf WHERE customer_id = 500;
EXPLAIN SELECT * FROM orders_perf WHERE customer_id = 500;

You'll see a Seq Scan and the elapsed time (likely a few milliseconds, which is still fast for 10k rows, but you can imagine the scale-up).

Create the index and re-test

CREATE INDEX idx_orders_perf_customer_id ON orders_perf (customer_id);

SELECT * FROM orders_perf WHERE customer_id = 500;
EXPLAIN SELECT * FROM orders_perf WHERE customer_id = 500;

The plan should now show an Index Scan, and the query time may be even lower, especially as you increase the row count (try 1 million rows!).

Exercise wrap-up

After you finish, drop the index to practice cleanup:

DROP INDEX idx_orders_perf_customer_id;

Pro tip: Always test whether an index actually helps your workload. Use EXPLAIN ANALYZE to get real timings, and compare before/after. Sometimes PostgreSQL's planner may choose a sequential scan even with an index, especially for small tables — trust the planner.

Compare options / when to choose what

PostgreSQL offers several index types beyond the default B-tree. Here's a quick comparison:

For most cases, you'll want a B-tree. GIN is useful when you query inside arrays or JSONB columns. BRIN shines when your data is naturally sorted by a column (like timestamps in a log table).

Troubleshooting & edge cases

1. PostgreSQL still does a sequential scan

Issue: You create an index, but EXPLAIN still shows Seq Scan. Why: For small tables, PostgreSQL correctly judges that a sequential scan is faster than reading the index and then fetching rows. Also, if your query returns a large percentage of rows (say > 10% of the table), the planner might choose a sequential scan. Fix: Run ANALYZE to update statistics, or force with SET enable_seqscan = off; for testing (not for production).

2. Index not used for expression queries

Issue: You have WHERE lower(email) = 'foo@bar.com', and your index is on email, but the planner ignores it. Fix: Create an expression index: CREATE INDEX idx_lower_email ON users (lower(email));

3. Index bloat

Issue: Indexes become slow over time due to dead tuples. Fix: Run VACUUM (ANALYZE) or use REINDEX INDEX idx_name; periodically.

4. Write performance degrades

Issue: Too many indexes on a table that gets heavy inserts/updates. Fix: Only create indexes that are actually used. Drop unused ones using pg_stat_user_indexes to check usage.

What you learned & what's next

You now understand how to create indexes for faster queries, why they work, and when they help. You practiced creating an index, observed the query plan change from Seq Scan to Index Scan, and learned about trade-offs and troubleshooting.

You can now confidently add indexes to your PostgreSQL tables to speed up lookups, and you know when to avoid them. Make sure you can explain the core idea behind indexes and have completed the hands-on exercise.

What's next: In the next lesson of this PostgreSQL tutorial, you'll tackle more advanced query optimization techniques — likely EXPLAIN deep dives and query rewriting strategies. Check the track syllabus to continue your journey to PostgreSQL mastery!