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
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:
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
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:
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:
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:
This index is perfect for queries like "Find all successful transactions for merchant 10". Both conditions are indexed.
Q9: Query Using Composite Index
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
This creates a GIN index on a JSONB column called metadata. Perfect for fast containment searches.
Q11: Query JSONB with GIN Index
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:
-> 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:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
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_atcolumn 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_indexesto see which indexes are actually being scanned.
Start by creating the indexes:
Test Your Knowledge
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 โ