Introduction
Database performance hinges on effective indexing. This guide covers PostgreSQL indexing strategies, from basic B-tree indexes to advanced GIN and GiST indexes for full-text search and geometric data.
Index Types
B-tree (Default)
-- Create index
CREATE INDEX idx_users_email ON users(email);
-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Use case: Equality and range queries
SELECT * FROM users WHERE email = 'user@example.com';
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2025-01-01';GIN (Generalized Inverted Index)
-- Full-text search
CREATE INDEX idx_posts_content ON posts USING GIN(to_tsvector('english', content));
SELECT * FROM posts
WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & performance');
-- JSON data
CREATE INDEX idx_metadata ON products USING GIN(metadata);
SELECT * FROM products WHERE metadata @> '{"category": "electronics"}';GiST (Geometric Search)
-- Geospatial queries
CREATE INDEX idx_locations ON stores USING GIST(location);
SELECT * FROM stores
WHERE ST_DWithin(location, ST_MakePoint(-73.935242, 40.730610), 1000);Query Optimization
-- Analyze query plan
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND status = 'completed';
-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));Monitoring
-- Find unused indexes
SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE indexname NOT IN (
SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan > 0
);
-- Index usage statistics
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;