Advanced

Performance and Indexing

Optimize PostgreSQL performance with proper indexing, EXPLAIN ANALYZE, and query tuning.

Why Indexing Matters

Without indexes, PostgreSQL does a sequential scan — reading every row. With an index, it can jump directly to matching rows. This makes a massive difference on large tables.

Index Types

  • B-tree (default): Equality, range, sorting — most common
  • GIN (Generalized Inverted Index): Arrays, JSONB, full-text search
  • GiST: Geometric types, full-text
  • Hash: Equality only (rarely used)
  • BRIN: Very large tables where data is physically ordered

EXPLAIN and EXPLAIN ANALYZE

Use EXPLAIN ANALYZE to see the query execution plan and find bottlenecks. Look for sequential scans on large tables — those need indexes.

Common Performance Tips

  • Index foreign key columns
  • Use partial indexes for filtered queries
  • Avoid SELECT * in production queries
  • Use LIMIT when you don't need all rows
  • Vacuum regularly to reclaim dead tuples

Example

sql
-- EXPLAIN ANALYZE - understand query performance
EXPLAIN ANALYZE
SELECT u.username, COUNT(p.id)
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.published = TRUE
GROUP BY u.id, u.username;

-- Reading the output: look for "Seq Scan" on large tables = needs index

-- Common index patterns
-- 1. B-tree for equality and range
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- 2. Composite index (order matters - most selective first)
CREATE INDEX idx_posts_user_published ON posts(user_id, published);

-- 3. Partial index (only index rows meeting a condition)
CREATE INDEX idx_posts_published_at ON posts(published_at)
WHERE published = TRUE;

-- 4. GIN for arrays and JSONB
CREATE INDEX idx_posts_tags_gin ON posts USING GIN(tags);
CREATE INDEX idx_posts_metadata_gin ON posts USING GIN(metadata);

-- 5. Covering index (includes extra columns to avoid table lookups)
CREATE INDEX idx_posts_user_id_cover
ON posts(user_id)
INCLUDE (title, published_at, views);

-- 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 extension)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- VACUUM and ANALYZE
VACUUM ANALYZE users;
VACUUM ANALYZE posts;

Want to run this code interactively?

Try in Compiler