
PostgreSQL 17 introduces significant enhancements to its query optimizer and execution engine, demanding a refined approach to performance tuning. This guide breaks down execution plans, leverages new memory management features, and optimizes complex query patterns to eliminate sequential scans and disk spills.
Analyze queries for sargability, missing composite indexes & offsets
Analyze SQL queries for performance anti-patterns, wildcards, and slow offsets. Generates optimized queries and DDL composite indexes for PostgreSQL, MySQL, and SQLite.
EXPLAIN ANALYZE OutputThe foundation of PostgreSQL query optimization lies in interpreting EXPLAIN ANALYZE output. This command provides the planner's estimated execution plan and the actual runtime statistics.
Each node in the EXPLAIN tree represents an operation. Key metrics include:
cost: Planner's estimated cost (startup..total).rows: Planner's estimated number of rows.actual time: Actual time spent (startup..total) in milliseconds.actual rows: Actual number of rows returned.loops: Number of times the node was executed.Buffers: Details on shared, local, and temp block accesses (requires BUFFERS option).Wal: WAL record generation statistics (requires WAL option).Settings: GUC parameters that affect the plan (requires SETTINGS option).Consider a simple query:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)
SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.price > 100 AND c.category_name = 'Electronics';
A typical output might look like this:
QUERY PLAN
--------------------------------------------------------------------------------------
Hash Join (cost=25.80..50.35 rows=10 width=64) (actual time=0.150..0.250 rows=5 loops=1)
Hash Cond: (p.category_id = c.category_id)
Buffers: shared hit=10 read=2
Wal: records=0, full_page_writes=0, bytes=0
-> Seq Scan on products p (cost=0.00..24.50 rows=20 width=36) (actual time=0.010..0.100 rows=15 loops=1)
Filter: (price > 100::numeric)
Rows Removed by Filter: 85
Buffers: shared hit=5 read=1
-> Hash (cost=25.75..25.75 rows=1 width=32) (actual time=0.030..0.030 rows=1 loops=1)
Buffers: shared hit=5 read=1
-> Seq Scan on categories c (cost=0.00..25.75 rows=1 width=32) (actual time=0.010..0.020 rows=1 loops=1)
Filter: (category_name = 'Electronics'::text)
Rows Removed by Filter: 99
Buffers: shared hit=5 read=1
Planning Time: 0.100 ms
Execution Time: 0.300 ms
Interpretation:
Hash Join: The top-level operation. It took 0.250 ms to complete and returned 5 rows. The planner estimated 10 rows.Seq Scan on products p: Scanned the products table. Filtered 85 rows, keeping 15. This scan hit 5 shared blocks and read 1 from disk.Hash: Built a hash table from the categories table scan.Seq Scan on categories c: Scanned categories, filtered 99 rows, keeping 1. This scan also hit 5 shared blocks and read 1 from disk.The Buffers section is critical. shared hit indicates data found in shared buffers (memory), read means data had to be fetched from disk. High read counts often point to missing indexes or insufficient shared_buffers.
Discrepancies between rows (estimated) and actual rows are red flags. A significant mismatch indicates the planner made a poor decision, often due to:
ANALYZE or VACUUM ANALYZE regularly.WHERE clauses, especially involving functions or expressions.ANALYZE with increased default_statistics_target can help.CREATE STATISTICS can provide multi-column or functional dependency statistics.Example of a blunder:
If actual rows for Seq Scan on products p was 10000 but rows was 20, the planner might have chosen a Nested Loop instead of a Hash Join or Merge Join, leading to abysmal performance.
work_mem and maintenance_work_memPostgreSQL 17 continues to refine memory management. Proper tuning of work_mem and maintenance_work_mem is crucial.
work_memThis parameter defines the maximum amount of memory to be used by a query operation (e.g., sort, hash table) before writing temporary data to disk. Each such operation can use this amount.
spill or disk in EXPLAIN ANALYZE output (e.g., Sort Method: external merge Disk: 12345kB).Tuning Strategy:
Sort Method: external merge Disk or HashAggregate (actual time=... loops=1) Memory: 12345kB, Disk: 67890kB in EXPLAIN ANALYZE.4MB or 8MB). If spills occur, increase it. A common production value might be 64MB to 256MB per session, but this depends heavily on workload and available RAM.SET work_mem TO '256MB'; can be used.-- Example: Observe a sort spill
-- Assume 'large_table' has millions of rows and 'some_column' is not indexed
SET work_mem TO '64kB'; -- Artificially low for demonstration
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM large_table
ORDER BY some_column;
-- Output might show:
-- Sort (cost=... rows=... width=...) (actual time=... rows=... loops=1)
-- Sort Key: some_column
-- Sort Method: external merge Disk: 12345kB <-- This indicates a spill
-- Buffers: ...
-- Increase work_mem to prevent spill
SET work_mem TO '256MB'; -- Adjust based on actual spill size and available RAM
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM large_table
ORDER BY some_column;
-- Output should ideally show:
-- Sort (cost=... rows=... width=...) (actual time=... rows=... loops=1)
-- Sort Key: some_column
-- Sort Method: quicksort Memory: 12345kB <-- In-memory sort
-- Buffers: ...
maintenance_work_memUsed for maintenance operations like VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY. These operations are typically less frequent but can be very memory-intensive.
Tuning Strategy:
work_mem. Common values range from 256MB to 1GB or more, depending on the size of your largest tables and available RAM.-- Example: Creating an index on a large table
-- A low maintenance_work_mem can make this very slow.
SET maintenance_work_mem TO '16MB'; -- Artificially low
CREATE INDEX idx_large_table_col ON large_table (another_column);
-- This might take a very long time and use temp files.
-- Increase for faster index creation
SET maintenance_work_mem TO '1GB'; -- Or higher, depending on table size
CREATE INDEX idx_large_table_col_new ON large_table (yet_another_column);
-- This should be significantly faster.
PostgreSQL 17 enhances the planner's ability to utilize incremental sort and improve hash join performance.
When a query requires sorting on a subset of columns that are already sorted by an index, PostgreSQL can perform an "incremental sort." This avoids a full sort operation, significantly reducing cost.
-- Assume an index on (order_date, customer_id)
CREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id);
-- Query that can benefit from incremental sort
EXPLAIN (ANALYZE)
SELECT *
FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY order_date, customer_id, order_total DESC;
The EXPLAIN output might show:
QUERY PLAN
--------------------------------------------------------------------------------------
Sort (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Sort Key: order_total DESC
Presorted Key: order_date, customer_id <-- Indicates incremental sort
-> Index Scan using idx_orders_date_customer on orders (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Index Cond: (order_date >= '2023-01-01'::date)
The Presorted Key line is the key indicator. To optimize:
ORDER BY clause.Sort Key columns will be sorted incrementally.Hash joins are efficient for joining large, unsorted datasets. PostgreSQL 17 improves their memory usage and spill handling.
work_mem is sufficient to hold the smaller relation's hash table in memory. If it spills to disk, performance degrades.enable_hashjoin: Ensure this GUC is on (default).-- Example: Hash Join
EXPLAIN (ANALYZE, BUFFERS)
SELECT a.id, b.value
FROM large_table_a a
JOIN medium_table_b b ON a.b_id = b.id;
Output:
QUERY PLAN
--------------------------------------------------------------------------------------
Hash Join (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Hash Cond: (a.b_id = b.id)
Buffers: ...
-> Seq Scan on large_table_a a (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buffers: ...
-> Hash (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Memory: 12345kB <-- Hash table memory usage
Buffers: ...
-> Seq Scan on medium_table_b b (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buffers: ...
If Memory in the Hash node is followed by Disk, it means the hash table spilled. Increase work_mem.
PostgreSQL 17 continues to enhance parallel query capabilities, allowing multiple CPU cores to work on parts of a single query.
max_parallel_workers_per_gather: Controls the maximum number of parallel workers that can be launched for a single Gather or Gather Merge node.max_parallel_workers: Total number of parallel workers allowed system-wide.min_parallel_table_scan_size: Minimum table size (in KB) for a parallel scan to be considered.parallel_setup_cost: Estimated cost to launch parallel workers.parallel_tuple_cost: Estimated cost to transfer a tuple from a worker to the leader.Identifying Parallel Plans:
Look for Gather or Gather Merge nodes in EXPLAIN ANALYZE output.
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM very_large_table
WHERE some_column > 100;
Output:
QUERY PLAN
--------------------------------------------------------------------------------------
Finalize Aggregate (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buffers: ...
-> Gather (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: ...
-> Partial Aggregate (cost=... rows=... width=...) (actual time=... rows=... loops=2)
Buffers: ...
-> Parallel Seq Scan on very_large_table (cost=... rows=... width=...) (actual time=... rows=... loops=2)
Filter: (some_column > 100)
Buffers: ...
Optimization:
max_parallel_workers_per_gather is set appropriately (e.g., 4 to 8 on a modern server).min_parallel_table_scan_size is not too high.| Feature/Parameter | Description | Default (PG17) | Recommended Range | Tradeoffs |
|---|---|---|---|---|
work_mem | Memory for sort/hash ops per query. | 4MB | 64MB - 256MB | Too low: disk spills, slow queries. Too high: OOM for concurrent queries. |
maintenance_work_mem | Memory for VACUUM, CREATE INDEX. | 64MB | 256MB - 1GB+ | Too low: slow maintenance. Too high: impacts active queries during maintenance. |
shared_buffers | Main data cache. | 128MB | 25% - 40% of RAM | Too low: high disk I/O. Too high: OS caching redundancy, OOM. |
effective_cache_size | Planner's estimate of OS + shared_buffers. | 4GB | 50% - 75% of RAM | Informational for planner; doesn't allocate memory. Incorrect value leads to bad plans. |
max_parallel_workers_per_gather | Max parallel workers per query. | 2 | 4 - 8 | Too low: underutilizes CPU. Too high: excessive context switching, resource contention. |
default_statistics_target | Granularity of statistics. | 100 | 200 - 1000 | Too low: poor row estimates. Too high: increased ANALYZE time, larger pg_statistic table. |
Gotcha: Sudden Performance Degradation After VACUUM FULL or REINDEX
Seq Scan replacing Index Scan.VACUUM FULL and REINDEX rewrite tables/indexes, potentially leading to a temporary state where statistics are stale or the planner's cache is invalidated.ANALYZE VERBOSE; on the affected tables or the entire database after such operations. This forces the planner to rebuild accurate statistics. For REINDEX, ensure ANALYZE is run on the table after the index is rebuilt.Gotcha: work_mem Spills Despite High Setting
EXPLAIN ANALYZE shows Sort Method: external merge Disk or HashAggregate ... Disk even when work_mem is set to a seemingly high value (e.g., 256MB).work_mem is allocated per operation. A single complex query might have multiple sort or hash operations, each consuming work_mem. Also, if many concurrent queries are running, the total memory consumption can exceed system RAM.work_mem further: If it's a single, critical query, try increasing work_mem for that session.ORDER BY or GROUP BY. Refactor the query to reduce the dataset size before sorting/hashing.pg_stat_activity to see temp_bytes for active queries. If total temp_bytes across all queries is high, you might be hitting system limits.Gotcha: Planner Choosing Suboptimal Join Order
EXPLAIN ANALYZE shows a join order that processes a large intermediate result set early.WHERE clauses that the planner cannot accurately estimate.ANALYZE: Ensure all tables involved have up-to-date statistics.CREATE STATISTICS: For multi-column correlations or functional dependencies, create extended statistics.
CREATE STATISTICS my_correlation_stats ON (col1, col2) FROM my_table;
ANALYZE my_table;
WHERE clauses: If possible, break down complex predicates.SET join_collapse_limit / SET from_collapse_limit: Temporarily reduce these GUCs to force the planner to consider more join orders, though this can increase planning time.SET enable_nestloop = off;: Temporarily disable specific join types to force the planner to consider alternatives, useful for debugging.Gotcha: High Planning Time in EXPLAIN ANALYZE
Planning Time is consistently high (e.g., hundreds of milliseconds to seconds), even for simple queries.UNION operations.default_statistics_target: While good for accuracy, higher values mean more data to process during planning.enable_seqscan = off: Forcing index scans everywhere can make the planner work harder to find an index.default_statistics_target is not excessively high for your workload.A database administrator observes that a critical query's `EXPLAIN ANALYZE` output frequently shows a high number of `Buffers: shared read` for a specific table, despite the table being relatively small and frequently accessed. What is the most likely immediate cause of this observation, and what is the most direct action to investigate further?
How do I know if my work_mem is set correctly?
Check EXPLAIN ANALYZE output for Sort Method: external merge Disk or HashAggregate ... Disk. If these appear, work_mem is too low for that specific operation. Also, monitor pg_stat_statements for temp_bytes and temp_files to see overall temporary file usage.
When should I use CREATE STATISTICS?
Use CREATE STATISTICS when the planner consistently makes bad row estimates for queries involving multiple columns in WHERE clauses, JOIN conditions, or GROUP BY clauses, especially if these columns have correlated data. This helps the planner understand relationships between columns that single-column statistics miss.
What's the difference between shared_buffers and effective_cache_size?
shared_buffers is the actual memory PostgreSQL allocates for its data cache. effective_cache_size is a GUC that tells the query planner how much memory it expects to be available for caching data, including shared_buffers and the OS file system cache. It doesn't allocate memory but influences the planner's cost estimates for disk I/O.
My query is using a Seq Scan instead of an Index Scan even with an index. Why?
This usually happens when the planner estimates that a sequential scan will be faster than an index scan. Common reasons include:
ANALYZE.Seq Scan is often faster due to overhead of index traversal.random_page_cost: If this GUC is set too high, it makes random I/O (index scans) appear more expensive.REINDEX might help.How can I force PostgreSQL to use a specific plan?
While generally discouraged as it can lead to suboptimal plans if data changes, you can influence the planner by temporarily disabling certain plan types using GUCs (e.g., SET enable_seqscan = off;, SET enable_nestloop = off;). For more granular control, tools like pg_hint_plan (an extension) allow SQL-level hints, but this should be used with extreme caution and only after thorough testing. The best approach is to fix the underlying statistical or indexing issues.
Clean AI CLI logs, build cron expressions, decode JWTs, and calculate chmod permissions offline.

Diagnose and eliminate PostgreSQL table and index bloat. Master autovacuum tuning formulas, pg_repack zero-downtime compaction, and MVCC visibility maps.
Read more
Tuning PgBouncer transaction pooling for 15,000 clients: sizing connection pools, handling prepared statements, and reducing PostgreSQL session memory.
Read more
Zero-downtime change data capture (CDC) with PostgreSQL logical replication, Debezium, and Kafka: WAL management, slot lag, and failover resilience.
Read more