Indexes & Performance

Index Types

• B-tree — default, great for =, <, >, BETWEEN, LIKE prefix

• Hash — only equality (=), faster than B-tree for exact lookups

• GIN — arrays, JSONB, full-text search, @> operator

• GiST — geometric data, range types, full-text search

• BRIN — very large tables with natural order (timestamps), tiny size

Creating Indexes

-- Basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Composite index — order matters (leftmost prefix rule)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index — only index rows matching condition
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- GIN index for JSONB
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

-- GIN for full-text search
CREATE INDEX idx_articles_fts ON articles
USING GIN(to_tsvector('english', title || ' ' || body));

-- Concurrent creation (no table lock!)
CREATE INDEX CONCURRENTLY idx_users_name ON users(name);

-- Drop index
DROP INDEX CONCURRENTLY idx_users_name;

EXPLAIN ANALYZE

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- With actual execution stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Key nodes to understand:
-- Seq Scan     — full table scan (bad for large tables)
-- Index Scan   — uses index, fetches heap rows
-- Index Only Scan — all data in index (best)
-- Nested Loop  — for small datasets / indexed lookups
-- Hash Join    — for larger datasets
-- Merge Join   — sorted inputs

-- Cost format: (startup cost..total cost) rows=N width=N
-- Actual time: actual time=start..end rows=N loops=N

Query Optimization Tips

• Avoid SELECT * — fetch only needed columns

• Use LIMIT with ORDER BY for pagination instead of OFFSET for large pages

• Index foreign key columns to speed up JOINs

• Use partial indexes for filtered queries on large tables

• Run ANALYZE to update planner statistics after bulk loads

• Run VACUUM to reclaim space and prevent table bloat

-- Keyset pagination (fast for large offsets)
SELECT * FROM orders
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Table size
SELECT
  relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;