Hologres:EXPLAIN et 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

Lisez le plan de bas en haut, comme pour EXPLAIN. Chaque nœud affiche deux lignes :

• Estimations de l'optimiseur (cost=... rows=... width=...) : Même signification que dans EXPLAIN.

• Mesures réelles du temps d'exécution [id=... dop=... time=... rows=... mem=... open=... get_next=...] : Valeurs réelles collectées pendant l'exécution.

Les champs de mesure du temps d'exécution sont les suivants :

La valeur time d'un opérateur inclut le temps cumulé de ses opérateurs enfants. Pour isoler le coût d'un seul opérateur, soustrayez le temps de son enfant. Les valeurs rows et mem sont spécifiques à chaque opérateur et ne sont pas cumulatives.

ADVICE

La section ADVICE liste les suggestions d'optimisation automatique dérivées de l'exécution actuelle :

• Clés de distribution, clés de clustering ou index bitmap manquants ou incorrects (par exemple, Table xxx misses bitmap index)

• Statistiques de table obsolètes (Table xxx Miss Stats! please run 'analyze xxx')

• Distorsion des données (shuffle data skew in different shards! max rows is 20, min rows is 0)

Ces suggestions sont basées uniquement sur la requête actuelle. Examinez-les par rapport à votre charge de travail spécifique avant d'appliquer des modifications.

Détail des coûts

La section des coûts indique où le temps de requête est dépensé :

Consommation des ressources

Les métriques de ressource utilisent le format total(max/avg/min), où chaque valeur couvre le worker ayant la consommation la plus élevée, la moyenne par worker et le worker ayant la consommation la plus faible.

Seq scan

Un Seq Scan lit l'intégralité de la table sans utiliser aucun index.

EXPLAIN SELECT * FROM public.holo_lineitem_100g;

Les tables partitionnées affichent l'opérateur Seq Scan on Partitioned Table et indiquent « Partitions selected: x out of y » :

EXPLAIN SELECT * FROM public.hologres_parent;

Les tables externes incluent une étiquette Foreign Table Type qui identifie la source (MaxCompute, OSS ou Hologres) :

EXPLAIN SELECT * FROM public.odps_lineitem_100;

Index Scan et Index Seek

Hologres utilise différents opérateurs d'index selon le format de stockage de la table :

• Index Scan (Clustering_index) : Utilisé pour les tables en mode column-store. Apparaît lorsqu'une requête atteint un index. Le plan affiche des sous-opérateurs qui identifient l'index utilisé : Segment Filter (index de segment), Cluster Filter (index de clustering) ou Bitmap Filter (index bitmap). Pour plus d'informations, consultez Principes du stockage en colonnes. Exemple 1 : La requête atteint l'index 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;

  Exemple 2 : Requête sur une colonne non indexée — aucun Clustering_index dans le plan.

  EXPLAIN SELECT * FROM column_test WHERE age>10;

  

  

• Index Seek (pk_index) : Utilisé pour les tables en mode row-store avec des index de clé primaire. Apparaît lorsqu'une requête de clé primaire n'utilise pas Fixed Plan. Pour plus d'informations, consultez Principes du stockage en lignes.

  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

Un opérateur Filter simple signifie que la condition ne correspond à aucun index. Vérifiez la configuration des index de la table et ajoutez les index appropriés pour améliorer les performances.

Si le plan affiche One-Time Filter: false , l'ensemble de résultats est vide.

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

La requête correspond à un index de segment (colonne Event Time). Cet opérateur apparaît conjointement avec Index Scan. Consultez la section Colonne Event Time (clé de segment).

Cluster Filter

La requête correspond à un index de regroupement. Consultez la section Clustering key.

Bitmap Filter

La requête correspond à un index bitmap. Consultez la section Index bitmap.

Join Filter

Applique un filtrage supplémentaire après une opération de jointure.

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

Une opération Hash Join construit une table de hachage en mémoire à partir de la table la plus petite, puis l'interroge ligne par ligne avec les données de la table la plus grande.

Lors de l'analyse d'une opération Hash Join, vérifiez également :

• hash cond : la condition de jointure, par exemple hash cond (tmp.a=tmp1.b).

• hash key : la clé utilisée pour le calcul de hachage entre les shards, généralement la clé GROUP BY.

La table la plus petite doit servir de table de hachage. Pour le vérifier, repérez la table étiquetée hash dans le plan : en lecture ascendante (de bas en haut), elle apparaît comme la branche inférieure. L'utilisation de la table la plus grande comme table de hachage consomme une quantité excessive de mémoire.

Optimisation : mettez à jour les statistiques

Des statistiques obsolètes conduisent l'optimiseur de requêtes (QO) à mal estimer la taille des tables. Par exemple, les statistiques indiquent rows=1000 pour une table qui contient réellement 1 million de lignes, ce qui entraîne la sélection incorrecte de la table servant de table de hachage.

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;

Le plan utilise incorrectement la table plus grande hash_join_test_2 comme table de hachage :

Après actualisation des statistiques, le plan sélectionne correctement la table la plus petite :

ANALYZE hash_join_test_1;
ANALYZE hash_join_test_2;

Optimisation : ajustez l'ordre des jointures pour les requêtes complexes

L'actualisation des statistiques résout la plupart des problèmes liés à l'ordre des jointures. Pour les jointures complexes impliquant cinq tables ou plus, le QO peut consacrer un temps significatif à la recherche du plan optimal. Utilisez le paramètre de configuration globale unifiée (GUC) optimizer_join_order pour contrôler ce comportement :

SET optimizer_join_order = '<value>';

Nested Loop Join et Materialize

Un opérateur Nested Loop lit la table externe ligne par ligne et recherche chaque correspondance dans la table interne — il s'agit essentiellement d'un produit cartésien. La table interne affiche généralement un opérateur Materialize dans le plan.

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;

Pour réduire le coût d'une opération Nested Loop :

• Maintenez un petit ensemble de résultats externes afin de limiter le nombre d'analyses de la table interne.

• Évitez autant que possible les jointures non équivalentes, car elles génèrent généralement des plans de type Nested Loop.

Cross Join

À partir de la version 3.0, l'opérateur Cross Join optimise les scénarios de jointure non équivalente impliquant une petite table. Contrairement à Nested Loop, qui réinitialise l'état interne pour chaque ligne externe, Cross Join charge la totalité de la petite table en mémoire et y fait passer la grande table en flux continu. Cette méthode est nettement plus rapide, mais consomme davantage de mémoire.

Pour désactiver 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

Une opération Merge Join nécessite que ses entrées soient pré-triées selon les clés de jointure. Hologres peut sélectionner Merge Join pour les requêtes dont les données d'entrée sont déjà ordonnées.

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;