PostgreSQL Query Optimization: Indexes, Explain Plans & Performance Tuning

Slow queries tank applications. Users wait, bounce, churn. This guide covers PostgreSQL optimization strategies that make databases 10x faster.

EXPLAIN: Read Query Plans#

Always start with EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM users WHERE email = 'user@example.com';

Output:

{
  "Plan": {
    "Node Type": "Seq Scan",  -- Full table scan (bad!)
    "Relation Name": "users",
    "Actual Rows": 1,
    "Actual Loops": 1,
    "Execution Time": 234.567  -- Milliseconds (slow!)
  }
}

Seq Scan = scanning every row. For 1M rows, that's 1M reads.

Strategy 1: B-Tree Indexes (Most Common)#

B-Tree indexes speed up equality and range queries:

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

-- Query now uses index
EXPLAIN (ANALYZE)
SELECT * FROM users WHERE email = 'user@example.com';

Output:

Index Scan using idx_users_email on users
  Index Cond: (email = 'user@example.com')
  Actual Rows: 1
  Execution Time: 0.234 ms  -- 1000x faster!

When to Index#

-- ✅ Frequently filtered columns
CREATE INDEX idx_posts_author_id ON posts(author_id);
SELECT * FROM posts WHERE author_id = 42;

-- ✅ Sorted columns (ORDER BY, GROUP BY)
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

-- ✅ JOIN keys
CREATE INDEX idx_comments_post_id ON comments(post_id);
SELECT * FROM comments WHERE post_id = 123;

-- ❌ Low-cardinality columns (few unique values)
-- Example: boolean column (only true/false)
-- Index adds overhead without benefit

Strategy 2: Multi-Column Indexes#

For queries filtering multiple columns:

-- ❌ Separate indexes (less efficient)
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- ✅ Composite index (better)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Query uses both conditions
EXPLAIN SELECT * FROM orders 
WHERE user_id = 42 AND status = 'pending';
-- Index Scan using idx_orders_user_status

Rule: Put most-filtered columns first.

-- Most filtered column first
CREATE INDEX idx_products_category_price ON products(category, price);

-- These queries use index well:
SELECT * FROM products WHERE category = 'electronics' AND price < 100;
SELECT * FROM products WHERE category = 'electronics';

-- This query might not (price alone):
SELECT * FROM products WHERE price < 100;

Strategy 3: EXPLAIN ANALYZE (Real Metrics)#

ANALYZE runs the query and shows actual numbers:

EXPLAIN (ANALYZE, BUFFERS)
SELECT posts.title, users.name
FROM posts
JOIN users ON posts.author_id = users.id
WHERE posts.created_at > NOW() - INTERVAL '7 days'
ORDER BY posts.created_at DESC;

Output:

Sort  (cost=1200.50..1210.20 rows=3880)
  Sort Key: posts.created_at DESC
  ->  Hash Join  (cost=500.00..1050.20 rows=3880)
        Hash Cond: (posts.author_id = users.id)
        ->  Seq Scan on posts  (cost=100.00..400.00 rows=10000)
              Filter: (created_at > now() - '7 days'::interval)
        ->  Hash  (cost=300.00..300.00 rows=1000)
              ->  Seq Scan on users  (cost=0.00..300.00 rows=1000)

Red flags:

• Seq Scan with large row counts → Add index
• Hash Join when should be nested loop → Check index on FK
• High Planning Time → Increase work_mem

Strategy 4: Partial Indexes (Conditional)#

Index only rows matching a condition:

-- ❌ Index all records
CREATE INDEX idx_users_active ON users(id)
WHERE active = true;

-- This query uses index:
SELECT * FROM users WHERE active = true AND id > 1000;

-- This query skips index:
SELECT * FROM users WHERE active = false;

Example: Users table has 10M rows, 9M active, 1M inactive.

-- 5% of table (only active users)
CREATE INDEX idx_active_users ON users(id) WHERE active = true;
-- vs
CREATE INDEX idx_all_users ON users(id);
-- Partial index: 500MB vs Full index: 10GB

Strategy 5: Full-Text Search Index#

For text columns:

-- ❌ Slow: LIKE query (scans whole table)
SELECT * FROM articles 
WHERE title LIKE '%kubernetes%' OR body LIKE '%kubernetes%';
-- Execution Time: 450ms

-- ✅ Fast: Full-text search index
CREATE INDEX idx_articles_fts ON articles 
USING GIN (to_tsvector('english', title || ' ' || body));

SELECT * FROM articles 
WHERE to_tsvector('english', title || ' ' || body) @@ 
      plainto_tsquery('english', 'kubernetes');
-- Execution Time: 2ms

Strategy 6: JSONB Indexes (For JSON Columns)#

PostgreSQL can index JSON data:

-- Table with JSON column
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- Index specific JSON keys
CREATE INDEX idx_products_category ON products 
USING GIN ((data -> 'category'));

-- Fast query
SELECT * FROM products 
WHERE data ->> 'category' = 'electronics';

Common Mistakes#

Mistake 1: Missing Index on Foreign Keys#

-- ❌ Bad: Users querying by post_id (no index)
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INT,  -- No index!
  title TEXT
);

-- This Seq Scans posts table
SELECT * FROM posts WHERE author_id = 42;

-- ✅ Fix:
CREATE INDEX idx_posts_author_id ON posts(author_id);

Mistake 2: Unused Indexes#

Indexes add write overhead:

-- Every INSERT/UPDATE/DELETE updates ALL indexes
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- Updates: id (PK), email (index), name (if indexed)

Find unused indexes:

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- Never used

Drop them:

DROP INDEX idx_unused;

Mistake 3: Not Analyzing Statistics#

PostgreSQL uses statistics to choose plans:

-- Run periodically (especially after bulk loads)
ANALYZE users;

-- Or enable auto-analyze
ALTER TABLE users SET (autovacuum_analyze_scale_factor = 0.01);

Tuning Parameters#

# postgresql.conf
# RAM available for query operations
work_mem = '4GB'

# Shared buffers (cache)
shared_buffers = '8GB'

# Effective cache size (for query planner)
effective_cache_size = '16GB'

# Parallel queries
max_parallel_workers_per_gather = 4
max_worker_processes = 4

# Connection pooling (pgBouncer recommended)
max_connections = 200

After changing, restart PostgreSQL:

sudo systemctl restart postgresql

Query Optimization Examples#

Example 1: Slow Pagination#

-- ❌ Slow: OFFSET scans all rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 1000000;
-- Scans 1M+ rows, returns 20

-- ✅ Fast: Keyset pagination
SELECT * FROM posts 
WHERE created_at < (SELECT created_at FROM posts WHERE id = 123)
ORDER BY created_at DESC LIMIT 20;
-- Uses index, returns 20 rows

Example 2: N+1 Queries#

-- ❌ N+1 problem
users = User.all
users.each do |user|
  posts = Post.where(author_id: user.id)  -- N queries!
end

-- ✅ Use JOIN
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id;

Example 3: Aggregation#

-- ❌ Slow: Group each table
SELECT p.category, COUNT(*) as total
FROM products p
GROUP BY p.category;
-- Full table scan

-- ✅ With index
CREATE INDEX idx_products_category ON products(category);
SELECT p.category, COUNT(*) as total
FROM products p
GROUP BY p.category;
-- Uses index for faster grouping

Monitoring & Maintenance#

-- Check index bloat
SELECT schemaname, tablename, indexname, idx_blks_read, idx_blks_hit
FROM pg_statio_user_indexes
ORDER BY idx_blks_hit DESC;

-- Reindex bloated indexes (while locked)
REINDEX INDEX idx_users_email;

-- Check slow queries
SELECT mean_exec_time, calls, query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- Enable (add to postgresql.conf)
shared_preload_libraries = 'pg_stat_statements'

Checklist#

• Index columns used in WHERE clauses
• Index foreign keys
• Use multi-column indexes for common combinations
• Use EXPLAIN ANALYZE before deployment
• Monitor unused indexes (drop them)
• Partial indexes for conditional queries
• ANALYZE after bulk loads
• Monitor pg_stat_statements for slow queries
• Use connection pooling (pgBouncer)
• Set work_mem and shared_buffers correctly

Conclusion#

Query optimization is iterative: EXPLAIN → Index → Measure → Repeat.

Start with EXPLAIN ANALYZE. Add indexes where Seq Scans appear with high row counts. Monitor in production. Most slow queries come from missing indexes—the 80/20 rule applies.

What's your most common indexing mistake? Share your learnings.

---

Database queries getting slower as your data grows? Book a 15-minute call.