Why Indexes Matter

Imagine searching for a person's name in a phone book. You could read every entry from start to finish, or you could flip to the section that starts with their last name. That's what an index does for databases โ€” it creates a fast lookup shortcut.

Without an index, PostgreSQL must scan every single row in a table (a "sequential scan"). With an index, it can jump directly to matching rows. On tables with millions of rows, this difference is dramatic.

Let's see this with EXPLAIN โ€” a command that shows you PostgreSQL's query plan without actually running the query.

Q1: EXPLAIN Without an Index

sql
Seq Scan on transactions (cost=0.00..17.25 rows=14 width=136)
Filter: (merchant_id = 5)

What this means:

  • Seq Scan โ€” A sequential scan. PostgreSQL reads every row from start to finish.
  • cost=0.00..17.25 โ€” Estimated startup cost (0) and total cost (17.25) in arbitrary units.
  • rows=14 โ€” PostgreSQL estimates 14 matching rows (the actual number may differ).
  • Filter: (merchant_id = 5) โ€” Applied after scanning, checking each row.

Q2: EXPLAIN ANALYZE โ€” Actual Execution Data

EXPLAIN alone estimates. EXPLAIN ANALYZE actually runs the query and shows real numbers:

sql
Seq Scan on transactions (cost=0.00..17.25 rows=14 width=136) (actual time=0.019..0.070 rows=14 loops=1)
Filter: (merchant_id = 5)
Rows Removed by Filter: 486
Planning Time: 0.019 ms
Execution Time: 0.077 ms

What ANALYZE adds:

  • (actual time=0.019..0.070 rows=14 loops=1) โ€” Real timing data. First number is startup time, second is total time. Got exactly 14 rows.
  • Rows Removed by Filter: 486 โ€” 486 rows didn't match merchant_id = 5 and were filtered out.
  • Execution Time: 0.077 ms โ€” Total time to run this query.

Pro tip: EXPLAIN ANALYZE runs the query, so avoid it on DELETE or UPDATE unless you want the changes to happen. Use plain EXPLAIN first to see the plan, then ANALYZE to test actual performance.

Creating Your First Index

The default index type in PostgreSQL is B-tree. B-trees are fast for equality lookups (=) and range queries (<, >, BETWEEN).

Q3: Create a B-tree Index

sql
CREATE INDEX

This creates an index on the merchant_id column. The index name is idx_transactions_merchant_id (good naming: prefix + table name + column name).

Q4: EXPLAIN ANALYZE With the Index

Now run the same query. PostgreSQL will use the index:

sql
Bitmap Heap Scan on transactions (cost=4.26..16.01 rows=14 width=136) (actual time=0.010..0.011 rows=14 loops=1)
Recheck Cond: (merchant_id = 5)
Heap Blocks: exact=7
-> Bitmap Index Scan on idx_transactions_merchant_id (cost=0.00..4.25 rows=14 width=0) (actual time=0.006..0.006 rows=14 loops=1)
Index Cond: (merchant_id = 5)
Planning Time: 0.113 ms
Execution Time: 0.021 ms

What changed:

  • Bitmap Index Scan on idx_transactions_merchant_id โ€” PostgreSQL uses the index!
  • actual time=0.006..0.006 โ€” Index lookup is super fast (0.006 ms).
  • Bitmap Heap Scan โ€” After finding matching index entries, PostgreSQL fetches the actual rows from the heap.
  • Execution Time: 0.021 ms โ€” Total query time dropped from 0.077 ms to 0.021 ms (3.6x faster!).

Index benefit: Single-column indexes speed up WHERE, JOIN, and ORDER BY clauses on that column. On large tables, the speedup is massive.

Composite and Unique Indexes

Sometimes a single column isn't enough. Composite indexes combine multiple columns to optimize queries that filter on several columns together.

Q7: Create a UNIQUE Index

A unique index enforces that all values in the column(s) are distinct:

sql
CREATE INDEX

This would prevent inserting duplicate merchant_ids (if that's your business rule). Use this for email addresses, usernames, or any field that must be unique.

Q8: Create a Composite Index

Combine multiple columns for queries that filter on both:

sql
CREATE INDEX

This index is perfect for queries like "Find all successful transactions for merchant 10". Both conditions are indexed.

Q9: Query Using Composite Index

sql
Bitmap Heap Scan on transactions (cost=4.23..15.06 rows=8 width=20) (actual time=0.009..0.011 rows=6 loops=1)
Recheck Cond: ((merchant_id = 10) AND ((status)::text = 'success'::text))
Heap Blocks: exact=5
-> Bitmap Index Scan on idx_transactions_merchant_status (cost=0.00..4.23 rows=8 width=0) (actual time=0.007..0.007 rows=6 loops=1)
Index Cond: ((merchant_id = 10) AND ((status)::text = 'success'::text))
Planning Time: 0.047 ms
Execution Time: 0.013 ms

Key insight: The composite index handles both conditions in the WHERE clause. No sequential scan needed.

Composite index order matters: Put the most selective column first (merchant_id, then status). This gives PostgreSQL more rows to eliminate early.

GIN Index for JSONB

B-tree indexes are great for scalar values, but JSONB (JSON data) needs a different approach. Generalized Inverted Index (GIN) is designed for searching inside complex data structures.

Q10: Create a GIN Index

sql
CREATE INDEX

This creates a GIN index on a JSONB column called metadata. Perfect for fast containment searches.

Q11: Query JSONB with GIN Index

sql
Seq Scan on transactions (cost=0.00..17.25 rows=88 width=12) (actual time=0.003..0.059 rows=114 loops=1)
Filter: (metadata @> '{"device": "mobile"}'::jsonb)
Rows Removed by Filter: 386
Planning Time: 0.145 ms
Execution Time: 0.062 ms

Note: This shows a sequential scan. In production with larger datasets, PostgreSQL would use the GIN index. Test on real data volumes to see index usage kick in.

GIN indexes: Use for JSONB containment queries (@>), array operations (@&, &@), and full-text search.

When NOT to Index

Indexes aren't free. They consume disk space and slow down INSERT, UPDATE, and DELETE because PostgreSQL must update the index every time data changes. Index wisely.

Q13: When Sequential Scan is Optimal

Sometimes a sequential scan is the right choice. For aggregate functions on all rows, no index helps:

sql
Aggregate (cost=17.25..17.26 rows=1 width=32) (actual time=0.041..0.042 rows=1 loops=1)
-> Seq Scan on transactions (cost=0.00..16.00 rows=500 width=8) (actual time=0.001..0.014 rows=500 loops=1)
Planning Time: 0.036 ms
Execution Time: 0.049 ms

Why no index? You're reading the entire table. An index doesn't help because you need every row anyway.

Q14: Check Index Usage

Monitor which indexes are actually being used. Unused indexes are waste:

sql
schemaname | tablename | indexname | idx_scan
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
public | merchants | merchants_pkey | 716
public | transactions | transactions_pkey | 3
public | webhooks | webhooks_pkey | 0
public | audit_logs | audit_logs_pkey | 0
public | transactions | idx_transactions_merchant_id | 0
public | transactions | idx_transactions_status | 0
public | transactions | idx_transactions_merchant_status | 0
public | transactions | idx_transactions_metadata_gin | 0

What to look for:

  • idx_scan = 0 โ€” Never used. Consider dropping it to save disk space and speed up writes.
  • idx_scan > 1000 โ€” Heavily used. Keeping it is definitely worth the cost.
  • Primary keys (like merchants_pkey) are always used for unique lookups.

Index cost trade-off: Every index slows INSERT, UPDATE, and DELETE. Index only on columns you regularly query with WHERE, JOIN, and ORDER BY. Drop unused indexes regularly.

Challenge

Create indexes to optimize these queries, then prove they work with EXPLAIN ANALYZE:

  • Create an index on the created_at column of the transactions table (for date range queries).
  • Run EXPLAIN ANALYZE on a query like SELECT * FROM transactions WHERE created_at > '2026-01-01'; to verify the index is used.
  • Create a composite index on (status, created_at) for queries filtering on both columns.
  • Check pg_stat_user_indexes to see which indexes are actually being scanned.
Show hint

Start by creating the indexes:

sql

Test Your Knowledge

Question 1 of 5
What does EXPLAIN ANALYZE show that EXPLAIN doesn't?
Question 2 of 5
What is the default index type in PostgreSQL?
Question 3 of 5
When should you use a composite index?
Question 4 of 5
What index type is best for JSONB containment queries?
Question 5 of 5
Should you index every column?
0
/5
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

Your queries just got faster.

Join the Best AI Program: The AI-Era Data Engineer โ€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer โ†’