CTE reuse strategy optimization - Hologres - Alibaba Cloud Documentation Center

When a CTE is referenced multiple times in a query, the Hologres Query Optimizer (HQO) must decide whether to materialize it — computing it once and caching the result — or inline it, recomputing the subquery each time it is referenced. By default, HQO makes this decision automatically. When the default behavior does not match your workload, you can override it using GUC parameters.

Set the CTE reuse strategy

Hologres provides two Grand Unified Configuration (GUC) parameters to control CTE reuse behavior.

Parameters

Parameter	Values	Supported versions
`optimizer_cte_inlining`	`ON` (default): forces inlining. `OFF`: forces reuse.	All versions
`hg_cte_strategy`	`AUTO` (default): the optimizer decides automatically. `INLINING`: forces inlining — the subquery is recomputed each time the CTE is referenced. `REUSE`: forces reuse — the CTE is computed once and the result is cached.	V3.2 and later

Syntax

For Hologres versions earlier than V3.2:

SET optimizer_cte_inlining = {on|off};

For Hologres V3.2 and later:

SET optimizer_cte_inlining = {on|off};
SET hg_cte_strategy = {AUTO|INLINING|REUSE};

Parameter interaction

optimizer_cte_inlining takes precedence over hg_cte_strategy. Understand how they interact before combining them:

When optimizer_cte_inlining = ON (default or explicit), hg_cte_strategy controls the actual behavior — it is not automatically set to INLINING.
When optimizer_cte_inlining = OFF, the strategy is forced to REUSE regardless of hg_cte_strategy. In this state, hg_cte_strategy can only be AUTO or REUSE.

Warning

Setting optimizer_cte_inlining = OFF and hg_cte_strategy = INLINING at the same time causes an error. If you set optimizer_cte_inlining = OFF, leave hg_cte_strategy at its default AUTO or explicitly set it to REUSE.

How HQO decides in AUTO mode

In AUTO mode, HQO evaluates each CTE and chooses the lower-cost strategy based on the following heuristics:

CTEs that contain aggregations (such as DISTINCT, GROUP BY) are candidates for reuse, because recomputing them is expensive.
CTEs that are plain scans with no aggregation are typically inlined, because the optimizer can push predicates through them to improve filter efficiency.
CTEs referenced only once are usually inlined regardless of complexity.

If the AUTO behavior does not match your expectations, use the EXPLAIN output to verify which strategy HQO chose, then override with hg_cte_strategy.

When to use each strategy

Strategy	Use when
`AUTO` (default)	Let the optimizer decide. Works well for most queries.
`INLINING`	The CTE computation is simple and the result set is small. Inlining allows the optimizer to push predicates into the CTE, which can improve filter efficiency.
`REUSE`	The CTE computation is expensive, the result set is large, or the CTE is referenced more than once. Reuse avoids redundant computation.

Examples

The following examples show how the execution plan changes under each hg_cte_strategy setting.

Setup: Create a sample table.

CREATE TABLE t1 (
    a INT,
    b INT,
    c INT
);

The EXPLAIN query used in all examples:

EXPLAIN
WITH cte1 AS (SELECT DISTINCT a, b, c FROM t1),
     cte2 AS (SELECT a, b, c FROM t1)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
UNION ALL
SELECT * FROM cte2;

AUTO (default)

When to use: You have a mix of expensive and lightweight CTEs in the same query and want the optimizer to minimize total cost without manual tuning.

The optimizer selects cte1 for reuse because it contains a DISTINCT aggregation that is expensive to recompute. cte2 is a plain scan with no aggregation, so the optimizer inlines it.

EXPLAIN
WITH cte1 AS (SELECT DISTINCT a, b, c FROM t1),
     cte2 AS (SELECT a, b, c FROM t1)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
UNION ALL
SELECT * FROM cte2;

Output:

QUERY PLAN
Gather  (cost=0.00..25.00 rows=4 width=12)
  CTE cte1  (cost=0.00..5.00 rows=1 width=12)
      ->  Forward  (cost=0.00..5.00 rows=1 width=12)
            ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
                  Group Key: t1_2.a, t1_2.b, t1_2.c
                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                        Hash Key: t1_2.a, t1_2.b, t1_2.c
                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                              ->  Seq Scan on t1 t1_2  (cost=0.00..5.00 rows=1 width=12)
  ->  Append  (cost=0.00..20.00 rows=4 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=12)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 3.2.0

The plan shows a CTE cte1 node (materialized), while cte2 is resolved by two separate Seq Scan operations (inlined).

INLINING

When to use: Your CTE is a simple scan and the outer query filters heavily on the CTE columns. Inlining lets the optimizer push those filter predicates inside the CTE, which can eliminate rows early and reduce overall scan cost.

With INLINING, no CTE is materialized — every reference triggers a full recomputation.

SET hg_cte_strategy = INLINING;
EXPLAIN
WITH cte1 AS (SELECT DISTINCT a, b, c FROM t1),
     cte2 AS (SELECT a, b, c FROM t1)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
UNION ALL
SELECT * FROM cte2;

Output:

QUERY PLAN
Gather  (cost=0.00..20.00 rows=4 width=12)
  ->  Append  (cost=0.00..20.00 rows=4 width=12)
        ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
              Group Key: t1.a, t1.b, t1.c
              ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                    Hash Key: t1.a, t1.b, t1.c
                    ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                          ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=12)
        ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
              Group Key: t1_1.a, t1_1.b, t1_1.c
              ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                    Hash Key: t1_1.a, t1_1.b, t1_1.c
                    ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                          ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1 t1_2  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1 t1_3  (cost=0.00..5.00 rows=1 width=12)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 3.2.0

No CTE nodes appear in the plan. The HashAggregate for cte1 runs twice, and cte2 produces two separate Seq Scan operations.

REUSE

When to use: Your CTE is referenced multiple times and contains expensive operations such as aggregations, joins, or scans over large tables. Forcing reuse ensures the computation runs exactly once, reducing total query cost.

With REUSE, both CTEs are materialized regardless of complexity.

SET hg_cte_strategy = REUSE;
EXPLAIN
WITH cte1 AS (SELECT DISTINCT a, b, c FROM t1),
     cte2 AS (SELECT a, b, c FROM t1)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
UNION ALL
SELECT * FROM cte2;

Output:

QUERY PLAN
Gather  (cost=0.00..30.00 rows=4 width=12)
  CTE cte1  (cost=0.00..5.00 rows=1 width=12)
      ->  Forward  (cost=0.00..5.00 rows=1 width=12)
            ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
                  Group Key: t1_1.a, t1_1.b, t1_1.c
                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                        Hash Key: t1_1.a, t1_1.b, t1_1.c
                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                              ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=12)
  CTE cte2  (cost=0.00..5.00 rows=1 width=12)
      ->  Forward  (cost=0.00..5.00 rows=1 width=12)
            ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                  ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=12)
  ->  Append  (cost=0.00..20.00 rows=4 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte2  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte2  (cost=0.00..5.00 rows=1 width=12)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 3.2.0

Both CTE cte1 and CTE cte2 nodes appear in the plan. All four references use CTE Scan, with each CTE computed exactly once.