Overview
Optimize PostgreSQL performance through proper indexing, query planning, and schema design. Learn to identify bottlenecks and write efficient queries.
Key Concepts
- Query Plans: EXPLAIN and ANALYZE for understanding queries
- Indexes: B-tree, Hash, GiST, and GIN indexes
- Joins: Optimize join strategies
- Statistics: Maintain column statistics for better plans
- Connection Pooling: Manage connection overhead
Query Analysis
-- View query execution plan
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';
-- Results show:
-- Seq Scan on users (cost=0.00..35.50 rows=1 width=100)
Create Indexes
-- Single column index
CREATE INDEX idx_users_email ON users(email);
-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Partial index (only some rows)
CREATE INDEX idx_active_users ON users(id) WHERE is_active = true;
-- GIN index for arrays/full-text search
CREATE INDEX idx_user_tags ON users USING GIN(tags);
Common Optimizations
-- Use LIMIT with offset for pagination
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 20;
-- Batch inserts
INSERT INTO logs VALUES
(1, 'error', now()),
(2, 'warning', now()),
(3, 'info', now());
-- Use prepared statements
PREPARE get_user(int) AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(42);
Proper indexing can improve query performance by 100-1000x.