Hologres:EXPLAIN e EXPLAIN ANALYZE

EXPLAIN <sql>;

EXPLAIN SELECT
        l_returnflag,
        l_linestatus,
        sum(l_quantity) AS sum_qty,
        sum(l_extendedprice) AS sum_base_price,
        sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
        sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
        avg(l_quantity) AS avg_qty,
        avg(l_extendedprice) AS avg_price,
        avg(l_discount) AS avg_disc,
        count(*) AS count_order
FROM
        lineitem
WHERE
        l_shipdate <= date '1998-12-01' - interval '120' day
GROUP BY
        l_returnflag,
        l_linestatus
ORDER BY
        l_returnflag,
        l_linestatus;

QUERY PLAN
Sort  (cost=0.00..7795.30 rows=3 width=80)
  Sort Key: l_returnflag, l_linestatus
  ->  Gather  (cost=0.00..7795.27 rows=3 width=80)
        ->  Project  (cost=0.00..7795.27 rows=3 width=80)
              ->  Project  (cost=0.00..7794.27 rows=3 width=104)
                    ->  Final HashAggregate  (cost=0.00..7793.27 rows=3 width=76)
                          Group Key: l_returnflag, l_linestatus
                          ->  Redistribution  (cost=0.00..7792.95 rows=1881 width=76)
                                Hash Key: l_returnflag, l_linestatus
                                ->  Partial HashAggregate  (cost=0.00..7792.89 rows=1881 width=76)
                                      Group Key: l_returnflag, l_linestatus
                                      ->  Local Gather  (cost=0.00..7791.81 rows=44412 width=76)
                                            ->  Decode  (cost=0.00..7791.80 rows=44412 width=76)
                                                  ->  Partial HashAggregate  (cost=0.00..7791.70 rows=44412 width=76)
                                                        Group Key: l_returnflag, l_linestatus
                                                        ->  Project  (cost=0.00..3550.73 rows=584421302 width=33)
                                                              ->  Project  (cost=0.00..2585.43 rows=584421302 width=33)
                                                                    ->  Index Scan using Clustering_index on lineitem  (cost=0.00..261.36 rows=584421302 width=25)
                                                                          Segment Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                          Cluster Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)

EXPLAIN ANALYZE <sql>;

EXPLAIN ANALYZE SELECT
        l_returnflag,
        l_linestatus,
        sum(l_quantity) AS sum_qty,
        sum(l_extendedprice) AS sum_base_price,
        sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
        sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
        avg(l_quantity) AS avg_qty,
        avg(l_extendedprice) AS avg_price,
        avg(l_discount) AS avg_disc,
        count(*) AS count_order
FROM
        lineitem
WHERE
        l_shipdate <= date '1998-12-01' - interval '120' day
GROUP BY
        l_returnflag,
        l_linestatus
ORDER BY
        l_returnflag,
        l_linestatus;

QUERY PLAN
Sort  (cost=0.00..7795.30 rows=3 width=80)
  Sort Key: l_returnflag, l_linestatus
[id=21 dop=1 time=2427/2427/2427ms rows=4(4/4/4) mem=3/3/3KB open=2427/2427/2427ms get_next=0/0/0ms]
  ->  Gather  (cost=0.00..7795.27 rows=3 width=80)
      [20:1 id=100003 dop=1 time=2426/2426/2426ms rows=4(4/4/4) mem=1/1/1KB open=0/0/0ms get_next=2426/2426/2426ms]
        ->  Project  (cost=0.00..7795.27 rows=3 width=80)
            [id=19 dop=20 time=2427/2426/2425ms rows=4(1/0/0) mem=87/87/87KB open=2427/2425/2425ms get_next=1/0/0ms]
              ->  Project  (cost=0.00..7794.27 rows=0 width=104)
                    ->  Final HashAggregate  (cost=0.00..7793.27 rows=3 width=76)
                          Group Key: l_returnflag, l_linestatus
                        [id=16 dop=20 time=2427/2425/2424ms rows=4(1/0/0) mem=574/570/569KB open=2427/2425/2424ms get_next=1/0/0ms]
                          ->  Redistribution  (cost=0.00..7792.95 rows=1881 width=76)
                                Hash Key: l_returnflag, l_linestatus
                              [20:20 id=100002 dop=20 time=2427/2424/2423ms rows=80(20/4/0) mem=3528/1172/584B open=1/0/0ms get_next=2426/2424/2423ms]
                                ->  Partial HashAggregate  (cost=0.00..7792.89 rows=1881 width=76)
                                      Group Key: l_returnflag, l_linestatus
                                    [id=12 dop=20 time=2428/2357/2256ms rows=80(4/4/4) mem=574/574/574KB open=2428/2357/2256ms get_next=1/0/0ms]
                                      ->  Local Gather  (cost=0.00..7791.81 rows=44412 width=76)
                                          [id=11 dop=20 time=2427/2356/2255ms rows=936(52/46/44) mem=7/6/6KB open=0/0/0ms get_next=2427/2356/2255ms pull_dop=9/9/9]
                                            ->  Decode  (cost=0.00..7791.80 rows=44412 width=76)
                                                [id=8 dop=234 time=2435/1484/5ms rows=936(4/4/4) mem=0/0/0B open=2435/1484/5ms get_next=4/0/0ms]
                                                  ->  Partial HashAggregate  (cost=0.00..7791.70 rows=44412 width=76)
                                                        Group Key: l_returnflag, l_linestatus
                                                      [id=5 dop=234 time=2435/1484/3ms rows=936(4/4/4) mem=313/312/168KB open=2435/1484/3ms get_next=0/0/0ms]
                                                        ->  Project  (cost=0.00..3550.73 rows=584421302 width=33)
                                                            [id=4 dop=234 time=2145/1281/2ms rows=585075720(4222846/2500323/3500) mem=142/141/69KB open=10/1/0ms get_next=2145/1280/2ms]
                                                              ->  Project  (cost=0.00..2585.43 rows=584421302 width=33)
                                                                  [id=3 dop=234 time=582/322/2ms rows=585075720(4222846/2500323/3500) mem=142/142/69KB open=10/1/0ms get_next=582/320/2ms]
                                                                    ->  Index Scan using Clustering_index on lineitem  (cost=0.00..261.36 rows=584421302 width=25)
                                                                          Segment Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                          Cluster Filter: (l_shipdate <= '1998-08-03 00:00:00+08'::timestamp with time zone)
                                                                        [id=2 dop=234 time=259/125/1ms rows=585075720(4222846/2500323/3500) mem=1418/886/81KB open=10/1/0ms get_next=253/124/0ms]

ADVICE:
[node id : 1000xxx] distribution key miss match! table lineitem defined distribution keys : l_orderkey; request distribution columns : l_returnflag, l_linestatus;
shuffle data skew in different shards! max rows is 20, min rows is 0

Query id:[300200511xxxx]
======================cost======================
Total cost:[2505] ms
Optimizer cost:[47] ms
Init gangs cost:[4] ms
Build gang desc table cost:[2] ms
Start query cost:[18] ms
- Wait schema cost:[0] ms
- Lock query cost:[0] ms
- Create dataset reader cost:[0] ms
- Create split reader cost:[0] ms
Get the first block cost:[2434] ms
Get result cost:[2434] ms
====================resource====================
Memory: 921(244/230/217) MB,  straggler worker id: 72969760xxx
CPU time: 149772(38159/37443/36736) ms, straggler worker id: 72969760xxx
Physical read bytes: 3345(839/836/834) MB, straggler worker id: 72969760xxx
Read bytes: 41787(10451/10446/10444) MB, straggler worker id: 72969760xxx
DAG instance count: 41(11/10/10), straggler worker id: 72969760xxx
Fragment instance count: 275(70/68/67), straggler worker id: 72969760xxx

QUERY PLAN

Leia o plano de baixo para cima, assim como no EXPLAIN. Cada nó apresenta duas linhas:

• Estimativas do otimizador (cost=... rows=... width=...): Possuem o mesmo significado que no EXPLAIN.

• Medições reais de tempo de execução [id=... dop=... time=... rows=... mem=... open=... get_next=...]: Valores reais coletados durante a execução.

Os campos de medição de tempo de execução são:

O valor de time de um operador inclui o tempo acumulado de seus operadores filhos. Para isolar o custo de um único operador, subtraia o tempo do filho. Os valores de rows e mem são por operador e não cumulativos.

ADVICE

A seção ADVICE lista sugestões automáticas de ajuste derivadas da execução atual:

• Chaves de distribuição, chaves de clustering ou índices bitmap ausentes ou incompatíveis (por exemplo, Table xxx misses bitmap index)

• Estatísticas de tabela desatualizadas (Table xxx Miss Stats! please run 'analyze xxx')

• Distorção de dados (shuffle data skew in different shards! max rows is 20, min rows is 0)

Essas sugestões baseiam-se exclusivamente na consulta atual. Analise-as considerando sua carga de trabalho específica antes de aplicar alterações.

Detalhamento de custos

A seção de custos mostra onde o tempo da consulta foi gasto:

Consumo de recursos

As métricas de recursos usam o formato total(max/avg/min), onde cada valor cobre o worker com maior consumo, a média por worker e o worker com menor consumo.

Seq scan

Um Seq Scan lê toda a tabela sem utilizar qualquer índice.

EXPLAIN SELECT * FROM public.holo_lineitem_100g;

Tabelas particionadas exibem o operador Seq Scan on Partitioned Table e informam "Partitions selected: x out of y":

EXPLAIN SELECT * FROM public.hologres_parent;

Tabelas externas incluem um rótulo Foreign Table Type que identifica a origem (MaxCompute, OSS ou Hologres):

EXPLAIN SELECT * FROM public.odps_lineitem_100;

Index Scan e Index Seek

O Hologres usa operadores de índice diferentes dependendo do formato de armazenamento da tabela:

• Index Scan (Clustering_index): Usado para tabelas de armazenamento colunar. Aparece quando uma consulta corresponde a um índice. O plano mostra suboperadores que identificam qual índice foi usado: Segment Filter (índice de segmento), Cluster Filter (índice de clustering) ou Bitmap Filter (índice bitmap). Para mais informações, consulte Princípios do armazenamento colunar. Exemplo 1: Consulta atinge o índice de clustering.

  BEGIN;
  CREATE TABLE column_test (
   "id" bigint not null ,
   "name" text not null ,
   "age" bigint not null
  );
  CALL set_table_property('column_test', 'orientation', 'column');
  CALL set_table_property('column_test', 'distribution_key', 'id');
  CALL set_table_property('column_test', 'clustering_key', 'id');
  COMMIT;
  
  INSERT INTO column_test VALUES(1,'tom',10),(2,'tony',11),(3,'tony',12);
  
  EXPLAIN SELECT * FROM column_test WHERE id>2;

  Exemplo 2: Consulta em uma coluna não indexada — nenhum Clustering_index no plano.

  EXPLAIN SELECT * FROM column_test WHERE age>10;

  

  

• Index Seek (pk_index): Usado para tabelas de armazenamento de linhas com índices de chave primária. Aparece quando uma consulta de chave primária não utiliza Fixed Plan. Para mais informações, consulte Princípios do armazenamento de linhas.

  BEGIN;
  CREATE TABLE row_test_1 (
      id bigint not null,
      name text not null,
      class text ,
  PRIMARY KEY (id)
  );
  CALL set_table_property('row_test_1', 'orientation', 'row');
  CALL set_table_property('row_test_1', 'clustering_key', 'name');
  COMMIT;
  INSERT INTO row_test_1 VALUES ('1','qqq','3'),('2','aaa','4'),('3','zzz','5');
  
  BEGIN;
  CREATE TABLE row_test_2 (
      id bigint not null,
      name text not null,
      class text ,
  PRIMARY KEY (id)
  );
  CALL set_table_property('row_test_2', 'orientation', 'row');
  CALL set_table_property('row_test_2', 'clustering_key', 'name');
  COMMIT;
  INSERT INTO row_test_2 VALUES ('1','qqq','3'),('2','aaa','4'),('3','zzz','5');
  
  --primary key index
  EXPLAIN SELECT * FROM (SELECT id FROM row_test_1 WHERE id = 1) t1 JOIN row_test_2 t2 ON t1.id = t2.id;

  

Filter

Um Filter simples significa que a condição não correspondeu a nenhum índice. Verifique a configuração de índices da tabela e adicione índices apropriados para melhorar o desempenho.

Se o plano mostrar One-Time Filter: false , o conjunto de resultados está vazio.

BEGIN;
CREATE TABLE clustering_index_test (
 "id" bigint not null ,
 "name" text not null ,
 "age" bigint not null
);
CALL set_table_property('clustering_index_test', 'orientation', 'column');
CALL set_table_property('clustering_index_test', 'distribution_key', 'id');
CALL set_table_property('clustering_index_test', 'clustering_key', 'age');
COMMIT;

INSERT INTO clustering_index_test VALUES (1,'tom',10),(2,'tony',11),(3,'tony',12);

EXPLAIN SELECT * FROM clustering_index_test WHERE id>2;

Segment Filter

A consulta correspondeu a um índice de segmento (Event Time Column). Aparece junto com Index Scan. Consulte Event Time Column (chave de segmento).

Cluster Filter

A consulta correspondeu a um índice de clustering. Consulte Chave de clustering.

Bitmap Filter

A consulta correspondeu a um índice bitmap. Consulte Índice bitmap.

Join Filter

Aplica filtragem adicional após uma operação de junção.

EXPLAIN SELECT * FROM public.lineitem;

BEGIN;
CREATE TABLE tbl1(
a int not null,
b text not null
);
CALL set_table_property('tbl1', 'distribution_key', 'a');
CREATE TABLE tbl2(
c int not null,
d text not null
);
CALL set_table_property('tbl2', 'distribution_key', 'd');
COMMIT;

EXPLAIN SELECT * FROM tbl1  JOIN tbl2 ON tbl1.a=tbl2.c;

Hash Join

Um Hash Join constrói uma tabela hash na memória a partir da tabela menor e depois a sonda linha por linha com dados da tabela maior.

Ao analisar um Hash Join, verifique também:

• hash cond: A condição de junção, por exemplo hash cond (tmp.a=tmp1.b).

• hash key: A chave usada para cálculo hash entre shards, tipicamente a chave de GROUP BY.

A tabela menor deve ser a tabela hash. Para verificar, procure a tabela rotulada como hash no plano — lendo de baixo para cima, ela aparece como o ramo inferior. Usar a tabela maior como tabela hash consome memória excessiva.

Ajuste: atualize estatísticas

Estatísticas desatualizadas fazem o QO julgar mal os tamanhos das tabelas. Por exemplo, estatísticas mostram rows=1000 para uma tabela que realmente tem 1 milhão de linhas, fazendo com que a tabela errada seja selecionada como tabela hash.

BEGIN ;
CREATE TABLE public.hash_join_test_1 (
    a integer not null,
    b text not null
);
CALL set_table_property('public.hash_join_test_1', 'distribution_key', 'a');
CREATE TABLE public.hash_join_test_2 (
    c integer not null,
    d text not null
);
CALL set_table_property('public.hash_join_test_2', 'distribution_key', 'c');
COMMIT ;

INSERT INTO hash_join_test_1 SELECT i, i+1 FROM generate_series(1, 10000) AS s(i);
INSERT INTO hash_join_test_2 SELECT i, i::text FROM generate_series(10, 1000000) AS s(i);

EXPLAIN SELECT * FROM hash_join_test_1 tbl1  JOIN hash_join_test_2 tbl2 ON tbl1.a=tbl2.c;

O plano usa incorretamente a tabela maior hash_join_test_2 como tabela hash:

Após atualizar as estatísticas, o plano seleciona corretamente a tabela menor:

ANALYZE hash_join_test_1;
ANALYZE hash_join_test_2;

Ajuste: ajuste a ordem de junção para consultas complexas

Atualizar estatísticas resolve a maioria dos problemas de ordem de junção. Para junções complexas com cinco ou mais tabelas, o QO pode gastar um tempo significativo encontrando o plano ideal. Use o parâmetro Grand Unified Configuration (GUC) optimizer_join_order para controlar isso:

SET optimizer_join_order = '<value>';

Nested Loop Join e Materialize

Um operador Nested Loop lê a tabela externa linha por linha e pesquisa a tabela interna para cada correspondência — essencialmente um produto cartesiano. A tabela interna tipicamente mostra um operador Materialize no plano.

BEGIN;
CREATE TABLE public.nestedloop_test_1 (
    a integer not null,
    b integer not null
);
CALL set_table_property('public.nestedloop_test_1', 'distribution_key', 'a');
CREATE TABLE public.nestedloop_test_2 (
    c integer not null,
    d text not null
);
CALL set_table_property('public.nestedloop_test_2', 'distribution_key', 'c');
COMMIT;

INSERT INTO nestedloop_test_1 SELECT i, i+1 FROM generate_series(1, 10000) AS s(i);
INSERT INTO nestedloop_test_2 SELECT i, i::text FROM generate_series(10, 1000000) AS s(i);

EXPLAIN SELECT * FROM nestedloop_test_1 tbl1,nestedloop_test_2 tbl2 WHERE tbl1.a>tbl2.c;

Para reduzir o custo do Nested Loop:

• Mantenha o conjunto de resultados externo pequeno para limitar quantas vezes a tabela interna é verificada.

• Evite junções não equitativas sempre que possível — elas tipicamente produzem planos Nested Loop.

Cross Join

A partir da V3.0, o operador Cross Join otimiza cenários de junção não equitativa envolvendo uma tabela pequena. Ao contrário do Nested Loop, que redefine o estado interno para cada linha externa, o Cross Join carrega toda a tabela pequena na memória e transmite a tabela grande através dela. Isso é significativamente mais rápido, mas usa mais memória.

Para desativar o Cross Join:

-- Disable for the current session
SET hg_experimental_enable_cross_join_rewrite = off;

-- Disable at the database level (takes effect for new connections)
ALTER database <database name> hg_experimental_enable_cross_join_rewrite = off;

Merge Join

Um Merge Join requer que suas entradas estejam pré-classificadas nas chaves de junção. O Hologres pode selecionar Merge Join para consultas onde os dados de entrada já estão ordenados.

BEGIN;
CREATE TABLE broadcast_test_1 (
    f1 int,
    f2 int);
CALL set_table_property('broadcast_test_1','distribution_key','f2');
CREATE TABLE broadcast_test_2 (
    f1 int,
    f2 int);
COMMIT;

INSERT INTO broadcast_test_1 SELECT i AS f1, i AS f2 FROM generate_series(1, 30)i;
INSERT INTO broadcast_test_2 SELECT i AS f1, i AS f2 FROM generate_series(1, 30000)i;

ANALYZE broadcast_test_1;
ANALYZE broadcast_test_2;

EXPLAIN SELECT * FROM broadcast_test_1 t1, broadcast_test_2 t2 WHERE t1.f1=t2.f1;

CREATE TABLE pqe_test(a text);
INSERT INTO pqe_test VALUES ('2023-01-28 16:25:19.082698+08');
EXPLAIN SELECT a::timestamp FROM pqe_test;

EXPLAIN SELECT to_timestamp(a,'YYYY-MM-DD HH24:MI:SS') FROM pqe_test;

EXPLAIN SELECT
        sum(l_extendedprice * l_discount) AS revenue
FROM
        lineitem
WHERE
        l_shipdate >= date '1996-01-01'
        AND l_shipdate < date '1996-01-01' + interval '1' year
        AND l_discount BETWEEN 0.02 - 0.01 AND 0.02 + 0.01
        AND l_quantity < 24;

SET optimizer_force_multistage_agg = on;

EXPLAIN SELECT l_shipdate FROM public.lineitem ORDER BY l_shipdate;

EXPLAIN SELECT * FROM public.lineitem limit 1;