---
title: "Query Optimizer User Guide"
---

# Query Optimizer User Guide

---

## Table of Contents

1. [Optimizer Hints](#optimizer-hints)
2. [Statistics Management](#statistics-management)
3. [Join Order Optimization](#join-order-optimization)
4. [Subquery Optimization](#subquery-optimization)
5. [CTE Optimization](#cte-optimization)
6. [Window Function Optimization](#window-function-optimization)
7. [Advanced Configuration](#advanced-configuration)

---

## Optimizer Hints

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

### Index Hints

```sql
-- 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
*/
```

```sql
-- 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
*/
```

```sql
-- 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

```sql
-- 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)
*/
```

```sql
-- 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
*/
```

```sql
-- 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

```sql
-- 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
*/
```

```sql
-- 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

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

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

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

/*
Aggregate
  -> Seq Scan on orders

Use case: Reduce resource usage, debugging
*/
```

### Multiple Hints

```sql
-- 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

```sql
-- 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
*/
```

```sql
-- 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` in the current release:** `ANALYZE` (including `VACUUM ANALYZE`) is accepted for PostgreSQL compatibility and returns immediately; it does not collect planner statistics yet. Statistics collection is planned. `EXPLAIN ANALYZE`, which runs the query and reports actual rows and timing, works today.

```sql
-- 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.

```sql
-- 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
*/
```

```sql
-- 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)
*/
```

```sql
-- 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

```sql
-- 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
*/
```

```sql
-- 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

```sql
-- 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

```sql
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

```sql
-- 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;
```

```sql
-- 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

```sql
-- 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
*/
```

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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
*/
```

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
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

```sql
-- 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

```sql
-- 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
*/
```

```sql
-- 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

```sql
-- 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

```sql
-- 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
*/
```

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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](https://www.heliosdb.com/docs/full/features/query-optimizer/readme/)
- [Quick Start Guide](https://www.heliosdb.com/docs/full/features/query-optimizer/quick_start/)
- [Troubleshooting Guide](https://www.heliosdb.com/docs/full/features/query-optimizer/troubleshooting/)
- [EXPLAIN Plan Guide](https://www.heliosdb.com/docs/full/guides/user/explain_plan_guide/)

---

**Query Optimizer User Guide** - Master every optimization technique.