What Is an Index?
An index is a data structure that lets the database find rows without scanning the entire table. Think of it like a book index — you look up a term, get the page number, and jump directly there instead of reading every page.
With Index
Query
→
Index Lookup
→
Row
Without Index
Query
→
Full Table Scan
✓Rule of Thumb
Indexes work best on columns used in WHERE, JOIN, or ORDER BY clauses with high cardinality — meaning many distinct values, like user IDs or email addresses.
Types of Indexes
Different index types serve different query patterns. PostgreSQL offers several choices depending on your data shape and access patterns.
B-Tree
Default index type. Handles equality and range queries: <, >, BETWEEN, LIKE 'prefix%'. Best general-purpose choice.
Hash
Optimized for exact equality (=) only. Faster than B-Tree for lookups but cannot handle ranges or sorting.
GIN
Generalized Inverted Index. Ideal for arrays, JSONB fields, and full-text search where one row has many indexed values.
GiST
Generalized Search Tree. Used for geometric data, IP ranges, and custom search operators requiring spatial logic.
Query Time by Index Type
Full Table Scan500 ms
B-Tree Index5 ms
Hash Index1 ms
GIN Index15 ms
How B-Tree Works
A B-Tree organizes values in a balanced tree. The database traverses from root to leaf in O(log n) time — extremely fast even with millions of rows.
1
Start at Root
The root node holds the broadest split key. The query value is compared against it to decide which branch to follow.
2
Traverse Branch Nodes
Each branch narrows the search range, directing traversal left or right until the target range is isolated.
3
Reach Leaf Node
Leaf nodes hold the actual indexed values plus pointers to the physical row location on disk.
4
Fetch the Row
The database follows the pointer to retrieve the full row — no other rows are touched.
B-Tree Structure
Root: [ 50 | 75 ]
Branch: [25 | 37] [62 | 68] [80 | 91]
Leaves: row pointers at each indexed value
B-Tree Node Hierarchy
Data Pages
Physical row data on disk — fetched via pointer
Leaf Nodes
Store actual indexed values plus row pointers
Internal Nodes
Narrow the search range left or right at each level
Root Node
Holds the broadest split keys — entry point for every search
When to Index
Indexes are not free — they consume disk space and add overhead to every write. Use them deliberately based on your query patterns.
| Do Index | Don't Index |
|---|---|
| High-cardinality columns (user_id, email) | Low-cardinality columns (is_active, status with 2–3 values) |
| Columns frequently used in WHERE or JOIN | Columns that are never filtered or sorted on |
| Foreign key columns | Very small tables where a full scan is trivially fast |
| Columns in ORDER BY on large result sets | Tables with extremely high INSERT/UPDATE volume |
| Prefix pattern columns (LIKE 'foo%') | Columns with mostly NULL values — use a partial index instead |
Performance Impact
!Read vs Write Trade-off
Every index speeds up reads but slows down INSERT, UPDATE, and DELETE — the database must maintain the index structure on every write. On write-heavy tables, too many indexes cause more harm than good. Always benchmark before adding indexes in production.
✓Use EXPLAIN ANALYZE
Run EXPLAIN ANALYZE on slow queries to see whether indexes are being used and where time is spent. The query planner may choose a sequential scan if the table is small or index selectivity is low — that is often the correct choice.