PostgreSQLPerformanceIntermediate
PostgreSQL Indexing: Why Your Queries Are Slow
Fix slow queries with proper indexing strategies
⏱️ 8 min read💡 Intermediate
🔥 The Problem:
Your PostgreSQL query that used to run in 100ms now takes 5 seconds.
Why? You have 1 million rows and no indexes.
PostgreSQL is doing a FULL TABLE SCAN - checking every single row! 😱
⚠️ Without Index: Full Table Scan
Query Execution WITHOUT Index
The slow path 🐌
1
Query: SELECT * FROM users WHERE email = ?
PostgreSQL receives your query
2
Full Table Scan Started
Must check EVERY row in the table
3
Checking 1,000,000 rows...
Compare email column in each row
4
Found match at row 876,432!
Had to scan 876k rows to find it
5
Query completed in 5 seconds
Slow and wasteful
Interactive Flow Diagram
✅ With Index: Lightning Fast
Query Execution WITH Index
The fast path ⚡
1
Query: SELECT * FROM users WHERE email = ?
PostgreSQL receives your query
2
B-tree Index Lookup
Use sorted index instead of table scan
3
Binary search in ~20 comparisons
log₂(1M) ≈ 20 comparisons
4
Found exact match!
Index points directly to row location
5
Query completed in 50ms
100x faster! 🎉
Interactive Flow Diagram
📊 Index Types Comparison
PostgreSQL Index Types
| Feature | B-tree (Default) | Hash |
|---|---|---|
| Best For | Equality & Range queries (=, <, >, BETWEEN) | Exact matches only (=) |
| Supports ORDER BY | ✓ | ✗ |
| Memory Usage | Medium | Lower |
| Range Scans | Excellent | Not supported |
| Lookup Speed | O(log n) | O(1) - Faster for exact matches |
| Disk I/O | Optimized for sequential reads | Minimal for point queries |
| NULL Handling | Stores NULLs | Cannot index NULLs |
| Common Usage | 95% of cases - Default choice | Rare - Very specific use cases |
💡 Conclusion:
Use B-tree for almost everything. Only use Hash for simple equality lookups on very large tables.
Winner
✓Supported
✗Not supported
Other Index Types:
- • GiST: Geographic data, full-text search
- • GIN: Array/JSONB columns, full-text search
- • BRIN: Very large tables with natural ordering
💻 Creating Indexes
1. Simple Index (Single Column)
1-- Create index on email column
2CREATE INDEX idx_users_email ON users(email);
3
4-- Query will now use this index
5SELECT * FROM users WHERE email = 'john@example.com';
6-- Execution time: 5000ms → 10ms ⚡2. Composite Index (Multiple Columns)
1-- Create composite index
2CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
3
4-- This query will use the index
5SELECT * FROM orders
6WHERE user_id = 123
7 AND created_at > '2024-01-01';
8-- Fast! ⚡
9
10-- But this query WON'T use it efficiently
11SELECT * FROM orders WHERE created_at > '2024-01-01';
12-- Slow! Index starts with user_id, not created_at⚠️ Column Order Matters! In composite indexes, put the most selective column first. Index (user_id, created_at) ≠ Index (created_at, user_id)
3. Unique Index
1-- Enforce uniqueness AND speed up lookups
2CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
3
4-- Prevents duplicate emails
5INSERT INTO users (email) VALUES ('john@example.com');
6-- Error: duplicate key value violates unique constraint4. Partial Index (Conditional)
1-- Index only active users (saves space!)
2CREATE INDEX idx_active_users
3ON users(email)
4WHERE status = 'active';
5
6-- This query uses the smaller index
7SELECT * FROM users
8WHERE email = 'john@example.com'
9 AND status = 'active';
10-- Faster AND uses less disk space! ⚡5. Expression Index (Computed Values)
1-- Index on lowercase email for case-insensitive search
2CREATE INDEX idx_users_email_lower
3ON users(LOWER(email));
4
5-- Now this query is fast
6SELECT * FROM users
7WHERE LOWER(email) = 'john@example.com';
8-- Without this index, would be VERY slow!✅ When to Create Indexes
- ✓WHERE clauses: Columns frequently used in WHERE conditions
- ✓JOIN columns: Foreign keys used in table joins
- ✓ORDER BY columns: Columns used for sorting results
- ✓GROUP BY columns: Columns used in aggregations
- ✓Large tables (>10k rows): Indexing becomes essential
❌ Indexing Anti-patterns
- ✗Indexing every column: Slows down writes, wastes space
- ✗Small tables: Full table scan is faster than index lookup
- ✗Low selectivity columns: Gender, boolean (only 2-3 values)
- ✗Functions without expression index: WHERE UPPER(name) = 'JOHN'
- ✗Leading wildcards: LIKE '%example' cannot use index
🧪 Test Your Knowledge
Question 1 of 4Score: 0/4
Your query on a 1M row table takes 5 seconds. You add an index. How much faster can it get?
🚀 Production Tips
Monitor Index Usage
1-- Find unused indexes (candidates for removal)
2SELECT
3 schemaname,
4 tablename,
5 indexname,
6 idx_scan as index_scans
7FROM pg_stat_user_indexes
8WHERE idx_scan = 0
9 AND indexrelname NOT LIKE '%_pkey';
10-- Drop unused indexes to speed up writes!Check Index Size
1-- Find largest indexes
2SELECT
3 indexname,
4 pg_size_pretty(pg_relation_size(indexname::regclass)) as size
5FROM pg_indexes
6WHERE schemaname = 'public'
7ORDER BY pg_relation_size(indexname::regclass) DESC;Create Index Concurrently
1-- Don't lock the table during index creation
2CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
3-- Takes longer, but doesn't block writes!✅ Key Takeaways
- ✓Indexes can provide 100-1000x speedup for queries
- ✓B-tree indexes work for 99% of use cases
- ✓Always index columns in WHERE, JOIN, and ORDER BY
- ✓Composite index order matters - most selective first
- ✓Too many indexes slow down writes - only index what you need
- ✓Use EXPLAIN ANALYZE to verify index usage
Download PDF Version
Get this guide as a PDF + receive daily backend tips
Daily tips
100% secure
Free forever
📚 Related Posts
🚀 Master Database Performance
Get daily PostgreSQL, database optimization, and backend tips
Subscribe to DailyNest