Scrolls
5 min read
On this page

Advanced Optimization

Once you have proper indexes and have eliminated N+1 queries, these are the next tools in the toolbox. CTEs, prepared statements, JIT compilation, statistics tuning, parallel queries, and manual query rewrites.

CTEs: Optimization Fence vs Inline

The Old Behavior (Pre-v12)

Before PostgreSQL 12, CTEs were optimization fences. The planner materialized CTE results into a temp buffer and could not push predicates into them.

-- In PostgreSQL 11 and earlier, this materializes the entire orders table
WITH recent AS (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
)
SELECT * FROM recent WHERE customer_id = 42;

The planner would scan all orders since 2024-01-01, store them, and then filter by customer_id. It would not combine the two predicates and use an index on customer_id.

PostgreSQL 12+: MATERIALIZED & NOT MATERIALIZED

Starting in v12, the planner can inline CTEs — treating them like subqueries and pushing filters down.

-- The planner inlines this and can use an index on customer_id
WITH recent AS (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
)
SELECT * FROM recent WHERE customer_id = 42;

You can control this explicitly:

-- Force materialization (useful when the CTE is referenced multiple times)
WITH recent AS MATERIALIZED (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
)
SELECT * FROM recent r1
JOIN recent r2 ON r1.id = r2.related_order_id;

-- Force inlining (rarely needed, but available)
WITH recent AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
)
SELECT * FROM recent WHERE customer_id = 42;

When a CTE is referenced once, PostgreSQL 12+ inlines it by default. When referenced multiple times, it materializes to avoid redundant computation.

Prepared Statements

For queries you run thousands of times with different parameters, prepared statements skip the planning step after the first few executions.

-- Prepare a statement
PREPARE get_orders (int) AS
SELECT * FROM orders WHERE customer_id = $1;

-- Execute with a parameter
EXECUTE get_orders(42);
EXECUTE get_orders(99);

-- Deallocate when done
DEALLOCATE get_orders;

After five executions, PostgreSQL switches from custom plans (planned per-execution) to a generic plan (reused across executions). This saves planning time but can produce a suboptimal plan if the parameter values have wildly different data distributions.

-- Check if generic plans are being used
SELECT * FROM pg_prepared_statements;

Most ORMs and database drivers use prepared statements automatically through the extended query protocol. This is usually a net win.

When Prepared Statements Hurt

If a column has highly skewed data (e.g., 90% of orders are "completed", 1% are "pending"), a generic plan may pick a Seq Scan that is wrong for the rare value.

-- Force custom plans for a specific prepared statement
SET plan_cache_mode = 'force_custom_plan';

JIT Compilation

PostgreSQL 11+ includes a JIT compiler (using LLVM) that can compile expressions, tuple deforming, and other operations to native code.

When JIT Helps

JIT helps with CPU-bound analytical queries that process millions of rows:

-- JIT can speed up aggregate expressions on large scans
SELECT region,
       sum(amount),
       avg(amount),
       count(*) FILTER (WHERE status = 'completed')
FROM orders
GROUP BY region;

When JIT Hurts

JIT has compilation overhead. For OLTP queries that return in milliseconds, the compilation time exceeds the execution savings.

-- Check JIT settings
SHOW jit;
SHOW jit_above_cost;
SHOW jit_inline_above_cost;
SHOW jit_optimize_above_cost;

 jit                    | on
 jit_above_cost         | 100000
 jit_inline_above_cost  | 500000
 jit_optimize_above_cost| 500000

The defaults are conservative. JIT only kicks in for expensive queries. If JIT is slowing down your workload:

-- Disable JIT for the current session
SET jit = off;

-- Or raise the threshold
SET jit_above_cost = 1000000;

Checking JIT in EXPLAIN

EXPLAIN (ANALYZE, BUFFERS)
SELECT region, sum(amount) FROM orders GROUP BY region;

 HashAggregate  (cost=25000.00..25010.00 rows=50 width=40)
                (actual time=1245.678..1245.700 rows=50 loops=1)
   ...
 JIT:
   Functions: 8
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 2.345 ms, Inlining 15.678 ms, Optimization 45.234 ms,
           Emission 30.123 ms, Total 93.380 ms

If JIT's total time is a significant fraction of execution time, it may not be helping.

Statistics & pg_stats

The planner makes decisions based on statistics about your data. The system catalog pg_stats stores per-column statistics.

-- View statistics for a column
SELECT tablename, attname,
       null_frac,
       n_distinct,
       most_common_vals,
       most_common_freqs,
       correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Key columns in pg_stats:

  • null_frac: fraction of NULL values.
  • n_distinct: estimated number of distinct values. Negative values mean it is a fraction of the total rows (e.g., -0.5 means about half the rows are distinct).
  • most_common_vals: the most frequently occurring values.
  • most_common_freqs: frequency of each common value.
  • correlation: how physically ordered the column is on disk (1.0 or -1.0 means perfectly ordered, 0.0 means random).

Increasing Statistics Targets for Skewed Columns

The default statistics target is 100 — meaning PostgreSQL samples 100 * 300 = 30,000 rows and tracks the top 100 most common values. For skewed columns, this may not be enough.

-- Increase statistics target for a specific column
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;

-- Re-analyze to collect with the new target
ANALYZE orders;

This gives the planner better data about value distribution but makes ANALYZE take longer and uses more memory for the statistics.

Extended Statistics for Correlated Columns

When two columns are correlated, the planner underestimates the combined selectivity.

-- Tell the planner about the correlation
CREATE STATISTICS stat_orders_status_region (dependencies)
ON status, region FROM orders;

ANALYZE orders;

Parallel Queries

PostgreSQL can use multiple workers for a single query. This helps with large sequential scans, aggregations, and joins.

-- Check parallel query settings
SHOW max_parallel_workers_per_gather;
SHOW min_parallel_table_scan_size;
SHOW parallel_setup_cost;
SHOW parallel_tuple_cost;

EXPLAIN (ANALYZE)
SELECT region, count(*), sum(amount)
FROM orders
GROUP BY region;

 Finalize HashAggregate  (cost=20000..20010 rows=50 width=44)
                         (actual time=456.123..456.140 rows=50 loops=1)
   ->  Gather  (cost=19000..19500 rows=100 width=44)
               (actual time=400.000..455.000 rows=150 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial HashAggregate  (cost=18000..18050 rows=50 width=44)
                                    (actual time=395.000..395.010 rows=50 loops=3)
               ->  Parallel Seq Scan on orders  ...

The Gather node collects results from parallel workers. "loops=3" means 2 workers + 1 leader.

Queries That Cannot Parallelize

  • Writes (INSERT, UPDATE, DELETE).
  • Queries inside a non-read-only transaction with a non-default isolation level.
  • Queries using cursors.
  • Functions marked PARALLEL UNSAFE (the default for user-defined functions).
-- Mark a function as safe for parallel execution
CREATE OR REPLACE FUNCTION calculate_tax(amount numeric)
RETURNS numeric
LANGUAGE sql
PARALLEL SAFE
AS $$
    SELECT amount * 0.08;
$$;

Query Rewrites the Planner Cannot Do

Sometimes you know something the planner does not. Manual rewrites can make a big difference.

Replace NOT IN with NOT EXISTS

NOT IN with a subquery that can return NULL has surprising semantics and prevents the use of anti-joins.

-- Slow: NOT IN with potential NULLs
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Fast: NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Replace DISTINCT with EXISTS

-- Slow: scans all matching rows then deduplicates
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'pending';

-- Fast: stops at the first match per customer
SELECT c.*
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.status = 'pending'
);

Lateral Joins for Top-N Per Group

-- Get the 3 most recent orders per customer
SELECT c.id, c.name, recent.*
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.id AS order_id, o.created_at, o.amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.created_at DESC
    LIMIT 3
) recent;

This uses an index on orders(customer_id, created_at DESC) efficiently, which a window function approach may not.

Common Pitfalls

  • Using MATERIALIZED CTEs everywhere "for performance". In PostgreSQL 12+, the planner inlines CTEs when it makes sense. Forcing materialization can prevent predicate pushdown and make queries slower.
  • Disabling JIT globally because one query was slow. Adjust the cost thresholds or disable JIT per-session for OLTP workloads instead.
  • Not running ANALYZE after changing statistics targets. The new target only takes effect after the next ANALYZE.
  • Assuming parallel queries always help. The setup cost of parallel workers can exceed the benefit for small tables. The planner usually gets this right.
  • Writing complex CTEs when a simple JOIN works. CTEs do not make queries faster by themselves. Use them for readability, not as a performance tool.
  • Ignoring function volatility categories. A function marked VOLATILE (the default) prevents the planner from using indexes on its result and blocks parallel execution.

Key Takeaways

  • In PostgreSQL 12+, CTEs are inlined by default when referenced once. Use MATERIALIZED or NOT MATERIALIZED to override.
  • Prepared statements save planning time for repeated queries but can produce suboptimal generic plans for skewed data.
  • JIT compilation helps CPU-bound analytical queries. It hurts fast OLTP queries. Tune the cost thresholds.
  • Increase statistics targets for columns with skewed distributions. Create extended statistics for correlated columns.
  • Parallel queries scale reads on large tables. Mark your functions PARALLEL SAFE when appropriate.
  • Manual query rewrites (NOT EXISTS over NOT IN, EXISTS over DISTINCT, LATERAL for top-N) can fix what the planner cannot optimize on its own.