SQL Query Performance Optimization CTE Patterns for Large-Scale Data

Quick Answer: CTE patterns optimize SQL query performance by extracting repeated logic into named stages, pre-aggregating data before joins to reduce row counts, and giving the query optimizer cleaner execution paths. The key is combining CTEs with proper indexing and understanding your database engine's materialization behavior.

Most SQL performance problems we see in APAC e-commerce systems aren't caused by missing indexes or bad hardware — they're caused by poorly structured queries that force the database engine to do redundant work. SQL query performance optimization CTE patterns give you a structured way to decompose complex queries, pre-aggregate early, and reduce the row counts your database has to process at each stage.

Related reading: UK Brand Entering Singapore E-Commerce Market Guide: 9 Steps

Related reading: Snowflake vs Databricks for APAC Retail Data Teams: A Practical Guide

Related reading: How to Build an AI-Ready Data Foundation for Retail in Asia-Pacific

Related reading: dbt Data Transformation Best Practices for E-Commerce: A Step-by-Step Guide

Related reading: LLM Model Hallucination Risk Mitigation for Enterprise: A Step-by-Step APAC Playbook

I learned this the hard way. In 2022, we were rebuilding the reporting layer for a Hong Kong retail client processing 1.2 million order line items daily across 400+ stores. Their nightly batch jobs were running past opening hours. The root cause wasn't the database server — it was a single 380-line SQL statement with nested subqueries repeating the same aggregation logic four times. Refactoring that query into a CTE-based pipeline cut execution time from 47 minutes to under 90 seconds.

This tutorial walks through the exact CTE patterns we use at Branch8 to improve SQL query performance on large tables across our APAC retail and logistics clients. Each step includes copy-pasteable examples you can adapt for your own data.

Prerequisites

Before starting, make sure you have the following:

Environment

• PostgreSQL 14+, SQL Server 2019+, or Oracle 21c+ — the examples use standard SQL with notes on engine-specific behavior
• A database client that shows execution plans (pgAdmin, Azure Data Studio, SQL Developer)
• At least one table with 500K+ rows for meaningful benchmarking

Knowledge

• Comfortable writing JOIN, GROUP BY, and subqueries
• Basic understanding of EXPLAIN / EXPLAIN ANALYZE output
• Familiarity with index types (B-tree, hash)

Sample Schema

We'll use a simplified e-commerce schema throughout. Run this to set up the test data:

1CREATE TABLE orders (
2  order_id BIGINT PRIMARY KEY,
3  customer_id BIGINT NOT NULL,
4  store_id INT NOT NULL,
5  order_date DATE NOT NULL,
6  total_amount DECIMAL(12,2) NOT NULL,
7  status VARCHAR(20) NOT NULL
8);
9
10CREATE TABLE order_items (
11  item_id BIGINT PRIMARY KEY,
12  order_id BIGINT NOT NULL REFERENCES orders(order_id),
13  product_id BIGINT NOT NULL,
14  quantity INT NOT NULL,
15  unit_price DECIMAL(10,2) NOT NULL,
16  discount_pct DECIMAL(5,2) DEFAULT 0
17);
18
19CREATE TABLE products (
20  product_id BIGINT PRIMARY KEY,
21  category_id INT NOT NULL,
22  product_name VARCHAR(200) NOT NULL,
23  brand VARCHAR(100)
24);
25
26-- Generate 1M orders for testing (PostgreSQL)
27INSERT INTO orders
28SELECT 
29  g AS order_id,
30  (random() * 50000)::BIGINT AS customer_id,
31  (random() * 400)::INT + 1 AS store_id,
32  CURRENT_DATE - (random() * 730)::INT AS order_date,
33  (random() * 5000 + 10)::DECIMAL(12,2) AS total_amount,
34  CASE WHEN random() > 0.1 THEN 'completed' ELSE 'cancelled' END
35FROM generate_series(1, 1000000) g;

Step 1: Baseline Your Query Before Refactoring

You cannot optimize what you haven't measured. Before rewriting anything as a CTE, capture the current execution profile.

Run the problematic query with EXPLAIN ANALYZE (PostgreSQL) or SET STATISTICS IO ON (SQL Server):

1-- PostgreSQL
2EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
3SELECT 
4  o.store_id,
5  COUNT(DISTINCT o.customer_id) AS unique_customers,
6  SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct/100)) AS net_revenue,
7  AVG(sub.avg_order_value) AS avg_order_value
8FROM orders o
9JOIN order_items oi ON o.order_id = oi.order_id
10JOIN (
11  SELECT store_id, AVG(total_amount) AS avg_order_value
12  FROM orders
13  WHERE status = 'completed'
14  AND order_date >= CURRENT_DATE - INTERVAL '90 days'
15  GROUP BY store_id
16) sub ON o.store_id = sub.store_id
17WHERE o.status = 'completed'
18  AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
19GROUP BY o.store_id;

Record three numbers:

• Total execution time (in milliseconds)
• Total rows scanned (from the plan output)
• Buffers hit vs. read (cache efficiency)

According to Markus Winand's research on use-the-index-luke.com, the single biggest cause of slow analytical queries is scanning the same large table multiple times — exactly what the subquery above does with the orders table.

What to look for in the plan

• Seq Scan on tables with 100K+ rows (missing index or full-table scan)
• The same table appearing multiple times in the plan tree
• Sort nodes consuming more than 20% of total time
• Hash Join spilling to disk (Batches: N where N > 1)

Step 2: Extract Repeated Logic Into Named CTEs

The first CTE optimization pattern — and the most impactful — is deduplication of logic. Every time a subquery scans the same table with the same filters, you're asking the engine to repeat work. A 2023 analysis by Brent Ozar showed that redundant table scans account for 30-40% of wasted I/O in typical OLTP reporting queries.

Refactor the query above:

1WITH completed_orders AS (
2  -- Single scan of orders with all shared filters
3  SELECT 
4    order_id,
5    customer_id,
6    store_id,
7    total_amount
8  FROM orders
9  WHERE status = 'completed'
10    AND order_date >= CURRENT_DATE - INTERVAL '90 days'
11),
12store_avg AS (
13  -- Pre-aggregate once, reference multiple times
14  SELECT 
15    store_id,
16    AVG(total_amount) AS avg_order_value
17  FROM completed_orders
18  GROUP BY store_id
19)
20SELECT 
21  co.store_id,
22  COUNT(DISTINCT co.customer_id) AS unique_customers,
23  SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct/100)) AS net_revenue,
24  sa.avg_order_value
25FROM completed_orders co
26JOIN order_items oi ON co.order_id = oi.order_id
27JOIN store_avg sa ON co.store_id = sa.store_id
28GROUP BY co.store_id, sa.avg_order_value;

Expected result

Run EXPLAIN ANALYZE again. You should see:

• The orders table scanned once instead of twice
• A smaller Hash Join on the pre-aggregated store_avg (hundreds of rows, not millions)
• 30-60% reduction in execution time on datasets above 500K rows

Engine-specific behavior matters

This is where the W3Schools-level CTE tutorials mislead people. CTEs are not universally materialized. Behavior varies:

• PostgreSQL 12+: CTEs are inlined by default (optimized like subqueries) unless you add MATERIALIZED
• SQL Server: CTEs are always inlined — they compile to the same plan as equivalent subqueries
• Oracle: The /*+ MATERIALIZE */ hint forces temporary table creation for CTE patterns in Oracle

1-- PostgreSQL: Force materialization when CTE is referenced 2+ times
2WITH completed_orders AS MATERIALIZED (
3  SELECT order_id, customer_id, store_id, total_amount
4  FROM orders
5  WHERE status = 'completed'
6    AND order_date >= CURRENT_DATE - INTERVAL '90 days'
7)
8-- rest of query...

1-- Oracle: Materialization hint
2WITH completed_orders AS (
3  SELECT /*+ MATERIALIZE */ order_id, customer_id, store_id, total_amount
4  FROM orders
5  WHERE status = 'completed'
6    AND order_date >= CURRENT_DATE - '90'
7)
8-- rest of query...

Step 3: Apply the Pre-Aggregation Pattern to Reduce Row Counts Early

This is the pattern that delivered the 900x improvement highlighted in recent case studies on Medium. The principle: aggregate before you join, not after.

Consider a report that calculates revenue by product category across stores. The naive approach joins everything first, then aggregates:

1-- Slow: joins 1M orders × 3M line items BEFORE aggregating
2SELECT 
3  p.category_id,
4  o.store_id,
5  SUM(oi.quantity * oi.unit_price) AS revenue
6FROM orders o
7JOIN order_items oi ON o.order_id = oi.order_id
8JOIN products p ON oi.product_id = p.product_id
9WHERE o.order_date >= '2024-01-01'
10GROUP BY p.category_id, o.store_id;

The CTE pre-aggregation pattern flips the order:

1WITH filtered_items AS (
2  -- Step 1: Filter order_items to only relevant orders
3  SELECT 
4    oi.product_id,
5    o.store_id,
6    SUM(oi.quantity * oi.unit_price) AS item_revenue
7  FROM order_items oi
8  JOIN orders o ON oi.order_id = o.order_id
9  WHERE o.order_date >= '2024-01-01'
10  GROUP BY oi.product_id, o.store_id
11),
12category_revenue AS (
13  -- Step 2: Join the small result set to products
14  SELECT 
15    p.category_id,
16    fi.store_id,
17    SUM(fi.item_revenue) AS revenue
18  FROM filtered_items fi
19  JOIN products p ON fi.product_id = p.product_id
20  GROUP BY p.category_id, fi.store_id
21)
22SELECT * FROM category_revenue
23ORDER BY revenue DESC;

Why this works

The filtered_items CTE reduces 3 million line items down to perhaps 50,000 product-store combinations. The subsequent join to products operates on 50K rows instead of 3M. According to Microsoft's SQL Server query processing architecture documentation, reducing the input size to a join operator has a near-linear impact on hash join memory consumption.

When we applied this pattern for a Taiwanese logistics client running on SQL Server 2019, their daily shipment reconciliation report dropped from 12 minutes to 18 seconds — a 40x improvement with zero hardware changes.

Step 4: Chain CTEs as a Data Pipeline for Complex Transformations

Real-world SQL query optimization and performance tuning often involves multi-stage transformations. Rather than nesting five levels of subqueries (which are nearly impossible to debug), chain CTEs as named stages:

1WITH
2-- Stage 1: Filter and clean
3active_orders AS (
4  SELECT 
5    order_id, customer_id, store_id, 
6    order_date, total_amount
7  FROM orders
8  WHERE status = 'completed'
9    AND total_amount > 0
10    AND order_date >= CURRENT_DATE - INTERVAL '365 days'
11),
12
13-- Stage 2: Customer-level aggregation
14customer_metrics AS (
15  SELECT 
16    customer_id,
17    store_id,
18    COUNT(*) AS order_count,
19    SUM(total_amount) AS lifetime_value,
20    MAX(order_date) AS last_order_date,
21    MIN(order_date) AS first_order_date
22  FROM active_orders
23  GROUP BY customer_id, store_id
24),
25
26-- Stage 3: RFM segmentation
27customer_segments AS (
28  SELECT 
29    customer_id,
30    store_id,
31    lifetime_value,
32    order_count,
33    CASE 
34      WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days' 
35        AND order_count >= 5 THEN 'champion'
36      WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days' 
37        THEN 'active'
38      WHEN last_order_date >= CURRENT_DATE - INTERVAL '90 days' 
39        THEN 'at_risk'
40      ELSE 'dormant'
41    END AS segment
42  FROM customer_metrics
43),
44
45-- Stage 4: Store-level summary
46store_summary AS (
47  SELECT 
48    store_id,
49    segment,
50    COUNT(*) AS customer_count,
51    AVG(lifetime_value)::DECIMAL(12,2) AS avg_ltv,
52    SUM(lifetime_value)::DECIMAL(14,2) AS total_ltv
53  FROM customer_segments
54  GROUP BY store_id, segment
55)
56
57SELECT * FROM store_summary
58ORDER BY store_id, total_ltv DESC;

Pipeline pattern benefits

• Debuggable: Replace the final SELECT with SELECT * FROM customer_metrics LIMIT 10 to inspect any stage
• Testable: Each CTE can be validated independently
• Composable: Add or remove stages without rewriting the entire query

This pipeline pattern is how we structure all analytical queries for clients running on PostgreSQL 15 or SQL Server 2022. The readability payoff is substantial when you have a team of 6-8 developers across Hong Kong and Vietnam working on the same codebase.

Step 5: Handle Recursive CTEs Without Blowing Up Performance

Recursive CTEs are essential for hierarchical data — org charts, category trees, bill-of-materials. They're also the easiest CTE pattern to get catastrophically wrong.

Here's a product category tree traversal:

1WITH RECURSIVE category_tree AS (
2  -- Anchor: top-level categories
3  SELECT 
4    category_id,
5    category_name,
6    parent_id,
7    1 AS depth,
8    ARRAY[category_id] AS path  -- PostgreSQL array for cycle detection
9  FROM categories
10  WHERE parent_id IS NULL
11  
12  UNION ALL
13  
14  -- Recursive: child categories
15  SELECT 
16    c.category_id,
17    c.category_name,
18    c.parent_id,
19    ct.depth + 1,
20    ct.path || c.category_id
21  FROM categories c
22  JOIN category_tree ct ON c.parent_id = ct.category_id
23  WHERE ct.depth < 10  -- CRITICAL: depth limit
24    AND c.category_id != ALL(ct.path)  -- Cycle prevention
25)
26SELECT * FROM category_tree
27ORDER BY path;

Three non-negotiable rules for recursive CTEs

• Always set a depth limit. Without one, a single bad data row (circular reference) will run the query until it hits a timeout or crashes.
• Index the join column. The recursive member executes once per level. An index on parent_id turns each iteration from a full scan into an index seek.
• Select only the columns you need. Each recursion level materializes a temporary result set. Wide rows multiply memory consumption at every level.

Oracle handles recursive CTEs differently — use CONNECT BY for better performance on Oracle-specific workloads, or the standard WITH RECURSIVE syntax on Oracle 21c+ with the /*+ NO_CONNECT_BY_COST_BASED */ hint to control the optimizer.

Step 6: Improve SQL Query Performance on Large Tables With CTE + Index Alignment

CTEs don't magically bypass missing indexes. The most common mistake in CTE optimization examples is assuming the CTE itself makes the query fast. It doesn't — the CTE restructures the logic so the optimizer can use existing indexes effectively.

Check whether your CTEs' filter columns are indexed:

1-- PostgreSQL: Check existing indexes on orders table
2SELECT 
3  indexname,
4  indexdef
5FROM pg_indexes
6WHERE tablename = 'orders'
7ORDER BY indexname;

For the patterns above, you need at minimum:

1-- Composite index for the common filter pattern
2CREATE INDEX idx_orders_status_date 
3  ON orders (status, order_date) 
4  INCLUDE (customer_id, store_id, total_amount);
5
6-- Covering index for order_items join
7CREATE INDEX idx_order_items_order_id 
8  ON order_items (order_id) 
9  INCLUDE (product_id, quantity, unit_price, discount_pct);

The INCLUDE clause (available in PostgreSQL 11+, SQL Server 2005+) creates a covering index that satisfies the CTE's column list without a heap lookup. According to PostgreSQL documentation, covering indexes can reduce I/O by 50-80% for analytical queries that access a subset of columns.

Verify alignment with EXPLAIN

1EXPLAIN (ANALYZE, BUFFERS)
2WITH completed_orders AS (
3  SELECT order_id, customer_id, store_id, total_amount
4  FROM orders
5  WHERE status = 'completed'
6    AND order_date >= CURRENT_DATE - INTERVAL '90 days'
7)
8SELECT store_id, COUNT(*) FROM completed_orders GROUP BY store_id;

You should see Index Only Scan using idx_orders_status_date in the output. If you see Seq Scan or Index Scan (not "Only"), your INCLUDE list is missing columns.

Step 7: Benchmark CTE vs. Subquery vs. Temp Table — Pick the Right Tool

CTEs are not always the fastest option. Here's how each approach compares for SQL query performance optimization CTE patterns in practice:

CTE (best for: read-once, moderate complexity)

1WITH summary AS (
2  SELECT store_id, SUM(total_amount) AS revenue
3  FROM orders WHERE status = 'completed'
4  GROUP BY store_id
5)
6SELECT * FROM summary WHERE revenue > 100000;

• Pros: No disk I/O for temp storage, optimizer can inline
• Cons: Re-executed if referenced multiple times (SQL Server), no statistics for optimizer

Temp Table (best for: multi-reference, very large intermediate results)

1CREATE TEMP TABLE tmp_summary AS
2SELECT store_id, SUM(total_amount) AS revenue
3FROM orders WHERE status = 'completed'
4GROUP BY store_id;
5
6CREATE INDEX ON tmp_summary (store_id);
7ANALYZE tmp_summary;
8
9SELECT * FROM tmp_summary WHERE revenue > 100000;

• Pros: Materialized once, has real statistics, can be indexed
• Cons: Disk I/O overhead, requires cleanup, breaks single-statement composability

Decision framework

Use a CTE when the intermediate result is referenced once or twice and contains fewer than ~500K rows. Switch to temp tables when you reference the same result 3+ times or need the optimizer to have accurate cardinality estimates for downstream joins.

In our Branch8 projects on PostgreSQL 15, we default to CTEs with MATERIALIZED for 2-reference cases and switch to temp tables only when EXPLAIN shows cardinality misestimates greater than 10x.

Avoiding Common CTE Anti-Patterns

After optimizing queries across dozens of APAC retail and e-commerce deployments, here are the patterns we actively avoid:

Anti-pattern 1: CTE wrapping a single table with no filter

1-- Don't do this — adds zero value
2WITH all_products AS (
3  SELECT * FROM products
4)
5SELECT * FROM all_products WHERE category_id = 5;

Anti-pattern 2: Deeply nested CTEs referencing each other linearly

If CTE_D references CTE_C which references CTE_B which references CTE_A, and each adds minimal transformation, you likely have a readability problem masquerading as optimization. Collapse stages that don't reduce row counts.

Anti-pattern 3: Using CTEs as a substitute for proper indexing

A CTE that pre-filters to reduce rows is good. A CTE that pre-filters because there's no index on the filter column is a workaround, not a solution. Fix the index first.

Anti-pattern 4: SELECT * inside CTEs

Every unnecessary column in a CTE's projection increases memory consumption. A study by Percona found that selecting only needed columns reduced temp buffer usage by up to 60% in materialized CTEs.

What to Do Next

You now have a repeatable process for applying SQL query performance optimization CTE patterns to real-world queries. Here's your decision checklist:

Before refactoring

• ☐ Baseline the current query with EXPLAIN ANALYZE
• ☐ Identify how many times each base table is scanned
• ☐ Confirm indexes exist on all filter and join columns

During refactoring

• ☐ Extract repeated subquery logic into named CTEs
• ☐ Pre-aggregate before joining to reduce row counts
• ☐ Chain CTEs as a pipeline for multi-stage transformations
• ☐ Add depth limits and cycle detection to recursive CTEs
• ☐ Use MATERIALIZED (PostgreSQL) or temp tables for multi-reference CTEs

After refactoring

• ☐ Re-run EXPLAIN ANALYZE and compare total time, rows scanned, buffer hits
• ☐ Verify Index Only Scan appears where expected
• ☐ Load test with production-scale data (not just dev samples)
• ☐ Document the pipeline stages for your team

If your team is running complex analytical queries across APAC retail operations and the nightly batch is creeping past business hours, reach out to Branch8. We've done this for multi-hundred-store retailers across Hong Kong, Taiwan, and Singapore — and we can tell you exactly where the bottlenecks are within a one-week audit.

Sources

• PostgreSQL Documentation — WITH Queries (Common Table Expressions): https://www.postgresql.org/docs/current/queries-with.html
• Microsoft SQL Server — WITH common_table_expression: https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql
• Oracle Documentation — Subquery Factoring: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html
• Markus Winand — Use The Index, Luke: https://use-the-index-luke.com/sql/partial-results/window-functions
• Brent Ozar — How to Think Like the SQL Server Engine: https://www.brentozar.com/archive/2013/06/the-sql-server-query-optimizer/
• Percona — PostgreSQL Query Optimization: https://www.percona.com/blog/postgresql-query-optimization-techniques/