PostgreSQL Query Optimization and Indexing Strategies

Identify and fix slow queries in PostgreSQL using execution plan analysis, strategic indexing, and query restructuring. Below is a practical approach to EXPLAIN ANALYZE, B-tree and partial indexes, covering indexes, and common anti-patterns that degrade performance.

When to Use This

• Queries take longer than 100ms and are executed frequently. See Database Views for precomputed results.
• Sequential scans appear in query plans where index scans should be used. See SQL Joins for join optimization.
• Database CPU or I/O is saturated under normal load. See Redis Caching for reducing load.

Solution

1. Analyze Query Plans with EXPLAIN

-- Basic plan
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

Look for:

• Seq Scan on large tables → missing index
• Hash Join with high memory usage → consider nested loop with index
• Sort with high cost → add index on sort columns

2. Create Strategic Indexes

-- Composite index for range + equality queries
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);

-- Partial index for active records only
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';

-- Covering index to avoid heap lookups
CREATE INDEX idx_orders_covering
ON orders(user_id, status, total)
INCLUDE (created_at);

3. Rewrite Queries to Use Indexes

-- Before: function on column prevents index use
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- After: range condition allows index scan
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

4. Optimize Joins

-- Before: implicit cross join
SELECT * FROM users, orders WHERE users.id = orders.user_id;

-- After: explicit JOIN with proper conditions
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
  AND o.created_at > NOW() - INTERVAL '30 days';

5. Partition Large Tables

-- Range partition by month
CREATE TABLE events (
  id BIGSERIAL,
  event_type VARCHAR(50),
  created_at TIMESTAMP NOT NULL,
  payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

How It Works

• EXPLAIN ANALYZE shows the actual execution plan and timings
• B-tree indexes accelerate equality and range lookups
• Partial indexes are smaller and faster for filtered subsets
• Covering indexes include all columns needed, avoiding heap access
• Partitioning prunes irrelevant data, reducing scan scope

Variation: Find Missing Indexes

-- Identify frequently scanned tables
SELECT
  schemaname,
  tablename,
  seq_scan,
  idx_scan,
  seq_tup_read,
  idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND idx_scan < seq_scan * 0.1
ORDER BY seq_scan DESC
LIMIT 20;

Production Considerations

• Run ANALYZE after bulk loads or major data changes to update statistics
• Use pg_stat_statements to identify the slowest queries by total time. See Logging for query observability.
• Monitor index bloat with pgstattuple and rebuild with REINDEX

Common Mistakes

• Adding indexes on every column without considering query patterns
• Using SELECT * when only a few columns are needed
• Not updating table statistics after large data migrations. See Database Migrations for safe schema changes.

Additional Common Mistakes

6. Indexing on low-cardinality columns. An index on a boolean column (active) is rarely used because the planner skips it when most rows match.

7. Not running ANALYZE after data distribution changes. The planner uses stale statistics and chooses bad plans. Run ANALYZE after bulk imports, deletes, or schema changes.

8. Using OFFSET for pagination. OFFSET 100000 scans and discards 100,000 rows. Use keyset pagination instead:

-- Bad: OFFSET pagination
SELECT * FROM orders ORDER BY id OFFSET 100000 LIMIT 20;

-- Good: keyset pagination
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

9. Ignoring pg_stat_activity for long-running queries. Queries that run for minutes block vacuuming and cause bloat. Monitor and kill them:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

10. Over-indexing write-heavy tables. Each index adds overhead to every INSERT, UPDATE, and DELETE. Benchmark write performance after adding indexes.

Performance Tips

1. Use pg_stat_statements.track = all to capture nested queries. This tracks queries inside functions and triggers, not just top-level queries.

2. Monitor buffer hit ratio. A ratio below 90% means the database is reading from disk too often. Increase shared_buffers or add RAM:

SELECT
    sum(blks_hit) AS hits,
    sum(blks_read) AS reads,
    100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0) AS hit_ratio
FROM pg_stat_database;

3. Use pgbench for load testing. Benchmark changes before deploying:

pgbench -i -s 10 mydb  # Initialize with scale factor 10
pgbench -c 20 -j 4 -T 60 mydb  # 20 clients, 4 threads, 60 seconds

4. Check for index bloat regularly. Use pgstattuple to measure bloat:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');

5. Use parallel_setup_cost and parallel_tuple_cost tuning. For analytical workloads, lower these to encourage parallel query plans:

SET parallel_setup_cost = 100;
SET parallel_tuple_cost = 0.03;
SET max_parallel_workers_per_gather = 4;