Otimização da estratégia de reutilização de CTE - Hologres: Central de Documentação do Alibaba Cloud

Quando uma consulta referencia uma CTE várias vezes, o Hologres Query Optimizer (HQO) decide entre materializá-la (calculando-a uma única vez e armazenando o resultado em cache) ou integrá-la (inline), recalculando a subconsulta a cada referência. Por padrão, o HQO toma essa decisão automaticamente. Se o comportamento padrão não atender à sua carga de trabalho, substitua-o usando parâmetros GUC.

Defina a estratégia de reutilização de CTE

O Hologres oferece dois parâmetros Grand Unified Configuration (GUC) para controlar o comportamento de reutilização de CTE.

Parâmetros

Parâmetro	Valores	Versões compatíveis
`optimizer_cte_inlining`	`ON` (padrão): força a integração (inlining). `OFF`: força a reutilização.	Todas as versões
`hg_cte_strategy`	`AUTO` (padrão): o otimizador decide automaticamente. `INLINING`: força a integração e recalcula a subconsulta sempre que a CTE for referenciada. `REUSE`: força a reutilização, calcula a CTE uma vez e armazena o resultado em cache.	V3.2 e posteriores

Sintaxe

Para versões do Hologres anteriores à V3.2:

SET optimizer_cte_inlining = {on|off};

Para Hologres V3.2 e posteriores:

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

Interação entre parâmetros

O parâmetro optimizer_cte_inlining tem precedência sobre hg_cte_strategy. Entenda a interação antes de combiná-los:

Se optimizer_cte_inlining = ON (padrão ou explícito), hg_cte_strategy controla o comportamento real e não assume automaticamente o valor INLINING.
Se optimizer_cte_inlining = OFF, a estratégia torna-se obrigatoriamente REUSE, independentemente do valor de hg_cte_strategy. Nesse estado, hg_cte_strategy aceita apenas AUTO ou REUSE.

Aviso

Defina optimizer_cte_inlining = OFF e hg_cte_strategy = INLINING simultaneamente causa erro. Ao definir optimizer_cte_inlining = OFF, mantenha hg_cte_strategy no valor padrão AUTO ou defina-o explicitamente como REUSE.

Como o HQO decide no modo AUTO

No modo AUTO, o HQO avalia cada CTE e escolhe a estratégia de menor custo com base nas seguintes heurísticas:

CTEs com agregações (como DISTINCT e GROUP BY) são candidatas à reutilização, pois o recálculo dessas operações tem alto custo.
CTEs compostas apenas por varreduras simples, sem agregação, geralmente sofrem integração. Isso permite ao otimizador aplicar predicados internamente e melhorar a eficiência dos filtros.
CTEs referenciadas apenas uma vez costumam ser integradas, independentemente da complexidade.

Se o comportamento AUTO não corresponder às suas expectativas, use a saída do comando EXPLAIN para verifique a estratégia escolhida pelo HQO e, se necessário, substitua-a com hg_cte_strategy.

Quando usar cada estratégia

Estratégia	Recomendada quando
`AUTO` (padrão)	Você prefere deixar a decisão com o otimizador. Funciona bem para a maioria das consultas.
`INLINING`	A computação da CTE é simples e o conjunto de resultados é pequeno. A integração permite ao otimizador aplicar predicados dentro da CTE e melhorar a eficiência dos filtros.
`REUSE`	A computação da CTE é custosa, o conjunto de resultados é grande ou a CTE é referenciada mais de uma vez. A reutilização evita cálculos redundantes.

Exemplos

Os exemplos a seguir mostram como o plano de execução muda conforme cada configuração de hg_cte_strategy.

Configuração: Crie uma tabela de exemplo.

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

Consulta EXPLAIN utilizada em todos os exemplos:

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 (padrão)

Cenário ideal: Sua consulta combina CTEs pesadas e leves, e você deseja que o otimizador minimize o custo total sem ajustes manuais.

O otimizador selecione cte1 para reutilização porque ela contém uma agregação DISTINCT cujo recálculo seria custoso. Já cte2 é uma varredura simples sem agregação, então o otimizador a integra.

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;

Saída:

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

O plano exibe um nó CTE cte1 (materializado), enquanto cte2 é resolvida por duas operações Seq Scan separadas (integrada).

INLINING

Cenário ideal: A CTE é uma varredura simples e a consulta externa aplica filtros intensivos nas colunas da CTE. A integração permite ao otimizador mover esses predicados de filtro para dentro da CTE, eliminar linhas antecipadamente e reduzir o custo geral de varredura.

Com INLINING, nenhuma CTE é materializada e cada referência dispara um recálculo completo.

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;

Saída:

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

Nenhum nó CTE aparece no plano. O HashAggregate referente a cte1 executa duas vezes, e cte2 gera duas operações Seq Scan distintas.

REUSE

Cenário ideal: A CTE é referenciada múltiplas vezes e contém operações custosas, como agregações, junções ou varreduras em tabelas grandes. Forçar a reutilização garante que o cálculo ocorra exatamente uma vez e reduz o custo total da consulta.

Com REUSE, ambas as CTEs são materializadas, independentemente da complexidade.

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;

Saída:

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

Os nós CTE cte1 e CTE cte2 aparecem no plano. Todas as quatro referências utilizam CTE Scan, com cada CTE calculada exatamente uma vez.