Postgresql Query Optimization

PostgreSQL Query Optimization

Workflow: Find and Fix Slow Queries

1. Identify — find expensive queries using pg_stat_statements or pg_stat_activity
2. Capture the plan — run EXPLAIN (ANALYZE, BUFFERS) on the slow query
3. Read the plan — identify the expensive nodes and misestimations
4. Diagnose — determine root cause: missing index, stale statistics, wrong config
5. Fix — create an index, run ANALYZE, or adjust configuration
6. Verify — re-run EXPLAIN and compare actual vs previous execution time

Step 1 — Find Slow Queries

Using pg_stat_statements (requires extension)

Enable in postgresql.conf and restart: shared_preload_libraries = 'pg_stat_statements'

-- Top queries by total execution time (biggest overall cost)
SELECT left(query, 120) AS query,
       calls,
       round(total_exec_time::numeric / 1000, 2) AS total_sec,
       round(mean_exec_time::numeric, 2)          AS avg_ms,
       round(stddev_exec_time::numeric, 2)        AS stddev_ms,
       rows / NULLIF(calls, 0)                    AS rows_per_call
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Top queries by average execution time (slowest individual runs)
SELECT left(query, 120) AS query,
       calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Top queries by I/O (most disk reads)
SELECT left(query, 120) AS query,
       calls,
       shared_blks_read,
       round(100.0 * shared_blks_hit /
           NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

Find Currently Running Slow Queries

SELECT pid, now() - query_start AS duration,
       state, wait_event_type, wait_event,
       left(query, 150) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;

Find Tables with Excessive Sequential Scans

SELECT relname,
       seq_scan,
       seq_tup_read,
       idx_scan,
       round(100.0 * seq_scan / NULLIF(seq_scan + idx_scan, 0), 1) AS seq_scan_pct,
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS size
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND pg_total_relation_size(schemaname || '.' || relname) > 10 * 1024 * 1024  -- > 10 MB
ORDER BY seq_scan DESC
LIMIT 20;

Step 2 — Capture the Execution Plan

Always use ANALYZE and BUFFERS together — EXPLAIN alone shows only estimates.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

For complex or production queries, use TIMING OFF to reduce overhead:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT ...;

For machine-readable output (useful for automated tools):

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Important: EXPLAIN ANALYZE actually executes the query. For DML statements, wrap in a transaction and roll back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'processed' WHERE id = 42;
ROLLBACK;

Step 3 — Read the Plan: Key Node Types

Warning Signs to Look For

• Rows estimated vs actual mismatch > 10x — stale statistics; run ANALYZE tablename
• Seq Scan on a large table — missing index or enable_seqscan = on overriding
• Sort Method: external merge Disk: N kB — sort spilled to disk; increase work_mem
• Hash Batches: N (original N) where N > 1 — hash join spilled; increase work_mem
• Rows Removed by Filter: N (high) after an Index Scan — add a partial index or better column selection
• Nested Loop with large outer set — missing index on inner table join column

Example Plan Analysis

Nested Loop  (cost=0.56..45231.82 rows=12 width=184)
             (actual time=0.095..8423.112 rows=12 loops=1)
  ->  Index Scan using orders_pkey on orders  (cost=0.43..8.45 rows=1 width=100)
                                              (actual time=0.042..0.044 rows=1 loops=12)
  ->  Seq Scan on order_items  (cost=0.00..4520.00 rows=8 width=84)
                               (actual time=701.900..701.908 rows=1 loops=12)
        Filter: (order_id = orders.id)
        Rows Removed by Filter: 90000

Reading this: The inner Seq Scan on order_items executes 12 times and each time reads 90,000 rows to find 1. Total: 1,080,000 rows scanned unnecessarily.

Fix: CREATE INDEX ON order_items (order_id);

Step 4 — Index Type Selection

Creating Indexes Without Blocking Writes

-- Standard: locks out writers during build
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Non-blocking: takes longer but does not block writes
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

-- Rebuild a bloated index online
REINDEX INDEX CONCURRENTLY idx_orders_customer;

Partial Index Examples

-- Only index active orders (smaller index, faster queries on active orders)
CREATE INDEX idx_active_orders ON orders (customer_id)
WHERE status = 'active';
-- Query MUST include: WHERE status = 'active' for the planner to use this index

-- Only index non-null emails
CREATE INDEX idx_users_email ON users (email)
WHERE email IS NOT NULL;

-- Partial index for a hot lookup pattern
CREATE INDEX idx_pending_jobs ON jobs (created_at)
WHERE processed = false;

Covering Index (Enables Index Only Scan)

-- Query: SELECT status, total FROM orders WHERE customer_id = 42 ORDER BY order_date
-- Without INCLUDE: requires heap access for status and total
CREATE INDEX idx_orders_covering ON orders (customer_id, order_date)
INCLUDE (status, total);
-- Now the query can be served entirely from the index

Step 5 — Statistics and the Planner

Incorrect row estimates are the most common cause of bad plans. Always check estimated vs actual rows in EXPLAIN ANALYZE output.

Update Statistics

ANALYZE tablename;                -- Analyze one table
ANALYZE VERBOSE tablename;       -- With progress output
VACUUM ANALYZE tablename;        -- Vacuum + analyze together

Increase Statistics Target for a Column

When a column has skewed or complex distribution, the default statistics_target = 100 may produce poor estimates.

-- Check current statistics
SELECT attname, n_distinct, correlation, null_frac,
       most_common_vals, histogram_bounds
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-- Increase statistics target for a column with complex distribution
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Extended Statistics for Correlated Columns

-- When two columns are correlated (e.g., city and zip_code)
-- the planner underestimates combined selectivity
CREATE STATISTICS orders_stats ON customer_id, order_date FROM orders;
ANALYZE orders;

-- View statistics used by planner
SELECT * FROM pg_statistic_ext_data;

Step 6 — Key Configuration Parameters

Changing a Parameter for Testing

-- Test in current session only (no restart needed)
SET work_mem = '256MB';
SET random_page_cost = 1.1;

-- Persist for a specific role
ALTER ROLE app_user SET work_mem = '64MB';
ALTER ROLE app_user SET random_page_cost = 1.1;

-- Persist globally (writes to postgresql.auto.conf)
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

Force/Disable Plan Choices for Debugging

-- Force the planner to avoid a seq scan (testing only)
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_seqscan;

-- Disable hash join to force merge or nested loop
SET enable_hashjoin = off;

Common Query Rewrites

Avoid Functions on Indexed Columns

-- Bad: function call on created_at prevents index use
SELECT * FROM orders WHERE date_trunc('day', created_at) = '2025-01-01';

-- Good: range query uses index
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02';

Keyset Pagination (Replace OFFSET)

-- Bad: OFFSET 1000000 scans and discards 1M rows
SELECT * FROM orders ORDER BY id OFFSET 1000000 LIMIT 10;

-- Good: cursor-based pagination using last seen ID
SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 10;

JSONB Query Optimization

-- Containment query: use GIN index
CREATE INDEX idx_events_gin ON events USING GIN (payload);
SELECT * FROM events WHERE payload @> '{"type": "login"}';

-- Specific key extraction: use expression index
CREATE INDEX idx_event_type ON events ((payload->>'type'));
SELECT * FROM events WHERE payload->>'type' = 'login';

Reference Files

• EXPLAIN ANALYZE guide — all node types with good/bad indicators
• Index types reference — B-tree, Hash, GIN, GiST, BRIN with syntax and use cases
• Planner configuration — all planner parameters, effects, and recommended values

Examples

• Optimization scenarios — sequential scan fix, JSONB GIN index, partial index, statistics fix, pg_stat_statements analysis