work_mem, parallel_workers, and Configuration Tuning

PostgreSQL's performance is determined not just by SQL quality and indexes, but by configuration. The default PostgreSQL settings are deliberately conservative — suitable for a shared server with many connections and limited RAM. For dedicated analytics workloads, tuning a handful of parameters can produce dramatic speed improvements.

The most impactful settings

Example 1: work_mem — sorting and hash joins in memory

work_mem is the amount of memory available to each sort and hash operation. A complex query with multiple joins and sorts may use work_mem many times simultaneously (once per node). The default of 4MB causes disk spills for medium-sized sorts.

-- Check current setting
SHOW work_mem;

-- Check if a query is spilling to disk
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.country, COUNT(*), SUM(o.total_amount)
FROM ec_orders o JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.country
ORDER BY SUM(o.total_amount) DESC;
-- Look for: "Sort Method: external merge  Disk: 8192kB"
-- This means the sort spilled to disk — work_mem is too low

-- Increase work_mem for the current session (doesn't affect other sessions)
SET work_mem = '256MB';

-- Re-run — look for: "Sort Method: quicksort  Memory: 45kB"
-- The sort now happens entirely in memory

-- For a specific one-time analytics query:
BEGIN;
SET LOCAL work_mem = '1GB';  -- only affects this transaction
SELECT ... long analytics query ...;
COMMIT;

Rule of thumb: Set work_mem = total_RAM / (max_connections * 3). On a dedicated analytics server with 32GB RAM and 10 connections, work_mem = 32GB / 30 ≈ 1GB is reasonable.

Example 2: Parallel query execution

PostgreSQL can distribute a query across multiple CPU cores using parallel workers. This is particularly effective for large sequential scans and aggregations.

-- Check current parallel settings
SHOW max_parallel_workers_per_gather;
SHOW parallel_tuple_cost;

-- Enable parallel for the current session
SET max_parallel_workers_per_gather = 4;

-- Verify a query uses parallelism
EXPLAIN (ANALYZE)
SELECT c.country, COUNT(DISTINCT o.order_id), SUM(oi.quantity * oi.unit_price) AS total
FROM ec_orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN ec_order_items oi ON oi.order_id = o.order_id
GROUP BY c.country;
-- Look for: "Gather" or "Gather Merge" nodes — these coordinate parallel workers

-- Force a plan that doesn't use parallelism (for comparison)
SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE)
SELECT c.country, COUNT(DISTINCT o.order_id), SUM(oi.quantity * oi.unit_price) AS total
FROM ec_orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN ec_order_items oi ON oi.order_id = o.order_id
GROUP BY c.country;

-- Minimum table size before parallelism kicks in
SHOW min_parallel_table_scan_size;  -- default: 8MB
-- Tables smaller than this won't get parallel plans

-- Queries that benefit most from parallelism:
-- - Large sequential scans with aggregations
-- - Hash joins on large tables
-- - DISTINCT and ORDER BY on large result sets

-- Queries that don't parallelize:
-- - Queries touching only indexed rows (index scan is already fast)
-- - DML (INSERT/UPDATE/DELETE) — cannot be parallelised
-- - Recursive CTEs

Example 3: random_page_cost for SSDs

The random_page_cost parameter tells the planner how expensive a random I/O is relative to a sequential I/O. The default (4.0) assumes spinning disk. On SSD, random reads are nearly as fast as sequential reads — setting random_page_cost = 1.1 makes the planner much more willing to use indexes:

-- Check current setting
SHOW random_page_cost;

-- For SSD storage, set globally in postgresql.conf:
-- random_page_cost = 1.1
-- seq_page_cost = 1.0  (keep default)

-- Or for a specific session:
SET random_page_cost = 1.1;

-- Test: a range query that might use seq scan vs index scan
EXPLAIN
SELECT * FROM ec_orders WHERE status = 'completed' LIMIT 10;
-- With random_page_cost=4.0: might choose Seq Scan (random I/O too expensive)
-- With random_page_cost=1.1: will use index if selectivity is reasonable

-- effective_cache_size (informational — doesn't allocate memory)
-- Tells the planner how much OS page cache is available
-- Set to roughly 50–75% of total RAM
-- Low effective_cache_size → planner prefers hash joins (assumes data isn't cached)
-- High effective_cache_size → planner prefers index scans (assumes data is in cache)
SET effective_cache_size = '24GB';  -- if you have 32GB RAM

Example 4: pg_stat_statements — finding slow queries in production

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

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 20 slowest queries by total execution time
SELECT
    ROUND(total_exec_time / 1000, 1) AS total_seconds,
    calls,
    ROUND(total_exec_time / calls / 1000, 3) AS avg_seconds,
    ROUND(stddev_exec_time / 1000, 3) AS stddev_seconds,
    rows / calls AS avg_rows,
    LEFT(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Queries with high variability (occasionally slow — index issues?)
SELECT
    ROUND(mean_exec_time, 2) AS mean_ms,
    ROUND(stddev_exec_time, 2) AS stddev_ms,
    calls,
    LEFT(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 100
  AND stddev_exec_time > mean_exec_time  -- high variance
ORDER BY stddev_exec_time DESC
LIMIT 10;

-- Reset stats (start fresh after making changes)
SELECT pg_stat_statements_reset();

Key Takeaway

Configuration matters as much as SQL for analytics workloads: work_mem prevents disk spills in sorts/hashes, max_parallel_workers_per_gather enables multi-core execution, and random_page_cost = 1.1 makes the planner index-friendly on SSDs. Use pg_stat_statements to find the queries that are actually consuming time in production — optimise those first, not the ones that seem slow in development.