Query Optimizer User Guide

Query Optimizer User Guide

Version: 7.0 Status: Production Ready Last Updated: 2026-01-04

---

Table of Contents

1. Optimizer Hints
2. Statistics Management
3. Join Order Optimization
4. Subquery Optimization
5. CTE Optimization
6. Window Function Optimization
7. Advanced Configuration

---

Optimizer Hints

Optimizer hints allow you to influence query planning decisions when the optimizer makes suboptimal choices.

Index Hints

-- Force use of specific index
SELECT /*+ IndexScan(orders idx_orders_date) */
  * FROM orders
WHERE order_date > '2025-01-01';

/*
Index Scan using idx_orders_date on orders
  Index Cond: (order_date > '2025-01-01')

Without hint: Optimizer might choose sequential scan
With hint: Forces index scan on specified index
*/

-- Force index-only scan
SELECT /*+ IndexOnlyScan(orders idx_orders_covering) */
  customer_id, order_date
FROM orders
WHERE customer_id = 123;

/*
Index Only Scan using idx_orders_covering on orders
  Index Cond: (customer_id = 123)
  Heap Fetches: 0
*/

-- Prevent index usage (force sequential scan)
SELECT /*+ SeqScan(orders) */
  * FROM orders
WHERE customer_id = 123;

/*
Seq Scan on orders
  Filter: (customer_id = 123)

Use case: Testing, comparing plan costs
*/

Join Hints

-- Force hash join
SELECT /*+ HashJoin(o c) */
  o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

/*
Hash Join
  Hash Cond: (o.customer_id = c.id)
*/

-- Force nested loop join
SELECT /*+ NestLoop(o c) */
  o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.id = 12345;

/*
Nested Loop
  -> Index Scan on orders o
  -> Index Scan on customers c

Use case: When outer table is very small
*/

-- Force merge join
SELECT /*+ MergeJoin(o c) */
  o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
ORDER BY o.customer_id;

/*
Merge Join
  Merge Cond: (o.customer_id = c.id)
  -> Index Scan on orders o
  -> Index Scan on customers c
*/

Join Order Hints

-- Specify join order
SELECT /*+ Leading(c o oi) */
  c.name, o.order_date, oi.product_id
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE c.status = 'active';

/*
Execution Order:
1. Scan customers (filtered by status)
2. Join with orders
3. Join with order_items

Without hint: Optimizer chooses based on cost
With hint: Follows specified order
*/

-- Alternative leading syntax
SELECT /*+ Leading((c o) oi) */
  c.name, o.order_date, oi.product_id
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id;

/*
Parentheses control grouping:
- First join c with o
- Then join result with oi
*/

Parallel Execution Hints

-- Force parallel execution
SELECT /*+ Parallel(orders 8) */
  SUM(amount)
FROM orders;

/*
Gather
  Workers Planned: 8
  -> Partial Aggregate
       -> Parallel Seq Scan on orders
*/

-- Disable parallel execution
SELECT /*+ NoParallel */
  SUM(amount)
FROM orders;

/*
Aggregate
  -> Seq Scan on orders

Use case: Reduce resource usage, debugging
*/

Multiple Hints

-- Combine multiple hints
SELECT /*+ Leading(c o) HashJoin(c o) Parallel(o 4) */
  c.name, SUM(o.amount)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

/*
Gather
  Workers Planned: 4
  -> Partial HashAggregate
       -> Parallel Hash Join
            Hash Cond: (o.customer_id = c.id)
            -> Parallel Seq Scan on orders o
            -> Parallel Hash
                 -> Parallel Seq Scan on customers c
*/

---

Statistics Management

Accurate statistics are essential for optimal query plans.

Understanding Statistics

-- View table statistics
SELECT
  relname AS table_name,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  last_vacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

/*
table_name | live_rows | dead_rows | last_analyze
-----------+-----------+-----------+---------------------
orders     | 1000000   | 5000      | 2025-12-15 02:00:00
*/

-- View column statistics
SELECT
  attname AS column_name,
  null_frac AS null_fraction,
  n_distinct AS distinct_values,
  avg_width AS avg_bytes
FROM pg_stats
WHERE tablename = 'orders';

/*
column_name  | null_fraction | distinct_values | avg_bytes
-------------+---------------+-----------------+-----------
id           | 0             | -1              | 8
customer_id  | 0             | 10000           | 4
order_date   | 0             | 365             | 4
amount       | 0             | 50000           | 8
status       | 0             | 5               | 10
*/

Collecting Statistics

-- Analyze single table
ANALYZE orders;

-- Analyze with verbose output
ANALYZE VERBOSE orders;
/*
INFO:  analyzing "public.orders"
INFO:  "orders": scanned 30000 of 30000 pages
INFO:  "orders": 1000000 rows in sample, 1000000 estimated total rows
*/

-- Analyze specific columns
ANALYZE orders (customer_id, order_date);

-- Sample-based analysis for very large tables
ANALYZE (SAMPLE 10 PERCENT) huge_table;

Extended Statistics

For correlated columns, extended statistics improve cardinality estimates.

-- Identify correlation issue
EXPLAIN ANALYZE
SELECT * FROM sales
WHERE region = 'US' AND product_category = 'Electronics';

/*
Seq Scan on sales
  (cost=0.00..50000.00 rows=100 width=50)
  (actual time=0.5..250.8 ms rows=50000 loops=1)

Problem: Estimated 100 rows, actual 50000 rows!
Cause: Columns are correlated but optimizer doesn't know
*/

-- Create extended statistics
CREATE STATISTICS sales_region_category (dependencies)
ON region, product_category FROM sales;

-- Or with ndistinct for multi-column distinct counts
CREATE STATISTICS sales_region_category_ndist (ndistinct)
ON region, product_category FROM sales;

-- Collect the extended statistics
ANALYZE sales;

-- Verify improved estimate
EXPLAIN ANALYZE
SELECT * FROM sales
WHERE region = 'US' AND product_category = 'Electronics';

/*
Seq Scan on sales
  (cost=0.00..50000.00 rows=48000 width=50)
  (actual time=0.5..250.8 ms rows=50000 loops=1)

Improvement: Now estimates 48000 rows (much closer to actual 50000)
*/

-- View extended statistics
SELECT
  stxname AS stat_name,
  stxkeys AS columns,
  stxkind AS stat_type
FROM pg_statistic_ext
WHERE stxname = 'sales_region_category';

Statistics Freshness Monitoring

-- Check statistics age
SELECT
  schemaname,
  relname,
  last_analyze,
  EXTRACT(days FROM now() - last_analyze) AS days_since_analyze,
  n_live_tup,
  n_mod_since_analyze
FROM pg_stat_user_tables
WHERE EXTRACT(days FROM now() - last_analyze) > 7
   OR last_analyze IS NULL
ORDER BY n_mod_since_analyze DESC;

/*
Identifies tables needing ANALYZE:
- Not analyzed in last 7 days
- High modification count since last analyze
*/

-- Automate statistics collection
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1;
SELECT pg_reload_conf();

/*
Triggers auto-analyze when:
  modifications > 50 + (0.1 * table_size)

For 10000 row table: triggers at 1050 modifications
*/

---

Join Order Optimization

The optimizer evaluates different join orders to find the lowest-cost plan.

Understanding Join Order

-- Three-table join
EXPLAIN (VERBOSE, COSTS)
SELECT c.name, o.order_date, p.product_name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id
WHERE c.status = 'active';

/*
Possible join orders:
1. (c JOIN o) JOIN p - customers first
2. (c JOIN p) JOIN o - unlikely (no direct relation)
3. (o JOIN c) JOIN p - orders first
4. (o JOIN p) JOIN c - orders with products first
...

Optimizer evaluates costs and picks lowest
*/

Analyzing Join Order Decisions

EXPLAIN ANALYZE (WHY_NOT)
SELECT c.name, o.order_date, p.product_name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id
WHERE c.status = 'active';

/*
Optimizer Decision: Join Order

CHOSEN: (customers JOIN orders) JOIN products
  Reasoning:
  - customers filtered to 1000 rows (10% active)
  - Joining first reduces intermediate result
  - products joined last (lookup by id)

REJECTED: (orders JOIN products) JOIN customers
  Cost: 5x higher
  Reason: orders has 1M rows, no initial filter
         Large intermediate result

REJECTED: (customers JOIN products) JOIN orders
  Cost: 10x higher
  Reason: No direct join condition, creates cartesian product
*/

Controlling Join Order

-- Reduce optimization search space
SET join_collapse_limit = 8;  -- Default
SET from_collapse_limit = 8;  -- Default

-- For very complex queries, reduce to speed up planning
SET join_collapse_limit = 4;

-- Disable reordering (use query order exactly)
SET join_collapse_limit = 1;

-- Force specific join order with explicit syntax
SELECT c.name, o.order_date
FROM customers c
JOIN (
  SELECT * FROM orders WHERE order_date > '2025-01-01'
) o ON c.id = o.customer_id;

/*
Forces orders to be scanned/filtered before join
*/

---

Subquery Optimization

Correlated vs Non-Correlated Subqueries

-- NON-CORRELATED (executed once)
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE status = 'active'
);

/*
Hash Semi Join
  -> Seq Scan on orders
  -> Hash
       -> Seq Scan on customers
            Filter: (status = 'active')

Good: Subquery executed once, result cached
*/

-- CORRELATED (executed per row - SLOW!)
SELECT o.id, o.amount,
  (SELECT c.name FROM customers c WHERE c.id = o.customer_id)
FROM orders o;

/*
Seq Scan on orders o
  SubPlan 1
    -> Index Scan on customers c
         Index Cond: (id = o.customer_id)

Problem: Subquery runs for EACH order row
For 1M orders: 1M subquery executions!
*/

Rewriting Subqueries as Joins

-- BEFORE: Correlated subquery
SELECT o.id, o.amount,
  (SELECT c.name FROM customers c WHERE c.id = o.customer_id)
FROM orders o;
-- 1M subquery executions, very slow

-- AFTER: JOIN (much faster)
SELECT o.id, o.amount, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

/*
Hash Left Join
  -> Seq Scan on orders o
  -> Hash
       -> Seq Scan on customers c

1 hash build + 1M probes = much faster
*/

EXISTS vs IN Optimization

-- IN with large subquery
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE region = 'US'
);

-- EXISTS (often more efficient)
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM customers c
  WHERE c.id = o.customer_id
    AND c.region = 'US'
);

/*
Both produce Hash Semi Join, but EXISTS can short-circuit:
- Stops searching after first match
- More efficient when many matches exist
*/

Subquery to Lateral Join

-- BEFORE: Subquery returning multiple columns
SELECT o.id,
  (SELECT c.name FROM customers c WHERE c.id = o.customer_id),
  (SELECT c.email FROM customers c WHERE c.id = o.customer_id)
FROM orders o;
-- 2 subqueries per row!

-- AFTER: LATERAL join
SELECT o.id, c.name, c.email
FROM orders o
LEFT JOIN LATERAL (
  SELECT name, email
  FROM customers
  WHERE id = o.customer_id
) c ON true;

/*
Single lookup per row, returns multiple columns
*/

---

CTE Optimization

Common Table Expressions (CTEs) provide readability but require careful optimization.

CTE Materialization

-- Materialized CTE (computed once, stored in memory)
WITH active_customers AS MATERIALIZED (
  SELECT id, name
  FROM customers
  WHERE status = 'active'
)
SELECT o.*, ac.name
FROM orders o
JOIN active_customers ac ON o.customer_id = ac.id;

/*
CTE Scan on active_customers
  -> CTE active_customers
       -> Seq Scan on customers
            Filter: (status = 'active')

Use MATERIALIZED when:
- CTE referenced multiple times
- Subquery is expensive
- Want to force optimization barrier
*/

-- Non-materialized CTE (inlined into main query)
WITH active_customers AS NOT MATERIALIZED (
  SELECT id, name
  FROM customers
  WHERE status = 'active'
)
SELECT o.*, ac.name
FROM orders o
JOIN active_customers ac ON o.customer_id = ac.id;

/*
Hash Join
  -> Seq Scan on orders
  -> Hash
       -> Seq Scan on customers
            Filter: (status = 'active')

CTE is "flattened" - same as writing JOIN directly
Use NOT MATERIALIZED when:
- CTE referenced once
- Want optimizer to see full picture
*/

Recursive CTEs

-- Recursive CTE for hierarchical data
WITH RECURSIVE org_tree AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: subordinates
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.level < 10  -- Depth limit!
)
SELECT * FROM org_tree;

/*
CTE Scan on org_tree
  -> Recursive Union
       -> Seq Scan on employees
            Filter: (manager_id IS NULL)
       -> Hash Join
            -> WorkTable Scan on org_tree ot
            -> Hash
                 -> Seq Scan on employees e

Optimization tips:
1. Add depth limit (WHERE level < N)
2. Index on manager_id for join
3. Use UNION ALL (not UNION) to avoid dedup
*/

CTE Performance Tips

-- GOOD: Single-use CTE, let optimizer inline
WITH filtered_orders AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE date > '2025-01-01'
)
SELECT * FROM filtered_orders WHERE amount > 1000;

-- GOOD: Multi-use CTE, materialize
WITH order_stats AS MATERIALIZED (
  SELECT customer_id, COUNT(*) as cnt, SUM(amount) as total
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM order_stats WHERE cnt > 10
UNION ALL
SELECT * FROM order_stats WHERE total > 10000;

-- BAD: Unnecessary CTE barrier
WITH simple_filter AS (  -- No MATERIALIZED keyword
  SELECT * FROM orders WHERE status = 'pending'
)
SELECT * FROM simple_filter;
-- Just write: SELECT * FROM orders WHERE status = 'pending'

---

Window Function Optimization

Understanding Window Function Execution

EXPLAIN ANALYZE
SELECT
  id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total
FROM orders;

/*
WindowAgg (cost=50000.00..75000.00 rows=1000000 width=40)
  -> Sort (cost=50000.00..52500.00 rows=1000000 width=32)
       Sort Key: customer_id, order_date
       -> Seq Scan on orders

Execution flow:
1. Scan all rows
2. Sort by window PARTITION and ORDER
3. Compute window aggregate
*/

Index Optimization for Windows

-- Create index matching window specification
CREATE INDEX idx_orders_window
ON orders (customer_id, order_date);

EXPLAIN ANALYZE
SELECT
  id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total
FROM orders;

/*
WindowAgg
  -> Index Scan using idx_orders_window on orders

No sort needed! Index provides correct order.
*/

Multiple Windows Optimization

-- Multiple windows with same PARTITION
SELECT
  id,
  customer_id,
  SUM(amount) OVER w as total,
  AVG(amount) OVER w as avg,
  COUNT(*) OVER w as cnt
FROM orders
WINDOW w AS (PARTITION BY customer_id);

/*
Single WindowAgg node computes all functions
More efficient than separate window specs
*/

-- Different partitions require separate passes
SELECT
  id,
  SUM(amount) OVER (PARTITION BY customer_id) as cust_total,
  SUM(amount) OVER (PARTITION BY region) as region_total
FROM orders;

/*
WindowAgg  -- For region partition
  -> Sort
       Sort Key: region
       -> WindowAgg  -- For customer partition
            -> Sort
                 Sort Key: customer_id
                 -> Seq Scan

Two sorts required - optimize by minimizing different partitions
*/

Frame Clause Optimization

-- Default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
SUM(amount) OVER (PARTITION BY cust ORDER BY date)

-- Explicit ROWS frame is more efficient
SUM(amount) OVER (
  PARTITION BY cust ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

/*
ROWS vs RANGE:
- ROWS: Counts physical rows (faster)
- RANGE: Groups by value (requires value comparison)

For running totals, ROWS is usually sufficient and faster
*/

Pushing Filters Before Windows

-- SLOW: Filter after window
SELECT * FROM (
  SELECT
    id,
    customer_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
  FROM orders
) t
WHERE rn = 1;

/*
Problem: Computes ROW_NUMBER for ALL rows, then filters
*/

-- FASTER: Filter before window when possible
WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE order_date > '2025-01-01'  -- Filter first!
)
SELECT * FROM (
  SELECT
    id,
    customer_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
  FROM recent_orders
) t
WHERE rn = 1;

/*
Pre-filter reduces rows before expensive window computation
*/

---

Advanced Configuration

Memory Configuration

-- Work memory for sorts and hashes
SET work_mem = '256MB';

-- Check if operations spill to disk
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders ORDER BY customer_id;

/*
Sort (actual time=500..750 ms)
  Sort Method: external merge  Disk: 50000kB  <-- Spilled!

Increase work_mem:
*/
SET work_mem = '512MB';

/*
Sort (actual time=100..200 ms)
  Sort Method: quicksort  Memory: 256000kB  <-- In memory!
*/

Cost Model Tuning

-- For SSD storage (random reads are fast)
SET random_page_cost = 1.1;  -- Default is 4.0
SET seq_page_cost = 1.0;

-- For in-memory databases
SET random_page_cost = 0.1;
SET seq_page_cost = 0.1;

-- Effect: Optimizer will prefer index scans more

Parallelism Configuration

-- Global parallel workers
SET max_parallel_workers = 8;

-- Per-query parallel workers
SET max_parallel_workers_per_gather = 4;

-- Table size threshold for parallelism
SET min_parallel_table_scan_size = '8MB';
SET min_parallel_index_scan_size = '512KB';

-- Cost threshold for parallelism
SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;

Optimizer Feature Toggles

-- Disable specific features for testing/debugging
SET enable_hashjoin = false;
SET enable_mergejoin = false;
SET enable_nestloop = true;  -- Force nested loop

-- Disable parallel execution
SET max_parallel_workers_per_gather = 0;

-- Disable partition pruning
SET enable_partition_pruning = false;

-- Disable JIT compilation
SET jit = false;

---

Best Practices Summary

Statistics

1. Run ANALYZE after bulk loads
2. Create extended statistics for correlated columns
3. Monitor statistics freshness
4. Configure autovacuum appropriately

Indexing

1. Create indexes for JOIN and WHERE columns
2. Use composite indexes for multi-column predicates
3. Consider covering indexes for frequently accessed queries
4. Monitor and remove unused indexes

Query Design

1. Prefer JOINs over correlated subqueries
2. Use NOT MATERIALIZED CTEs for single-use
3. Match window function indexes to partition/order
4. Filter early, aggregate late

Configuration

1. Size work_mem for your largest sorts/hashes
2. Adjust random_page_cost for your storage type
3. Configure parallelism for your CPU count
4. Use hints sparingly, only when optimizer errs

---

Related Documentation

• Query Optimizer Overview
• Quick Start Guide
• Troubleshooting Guide
• EXPLAIN Plan Guide

---

Query Optimizer User Guide - Master every optimization technique.