Plan de requête - Amazon Redshift
Opérateurs EXPLAINJointures dans EXPLAIN

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Plan de requête

Vous pouvez utiliser le plan de requête pour obtenir des informations sur les opérations individuelles requises pour exécuter une requête. Avant de travailler avec un plan de requête, nous vous recommandons de comprendre d’abord comment Amazon Redshift gère le traitement des requêtes et la création des plans de requête. Pour de plus amples informations, veuillez consulter Workflow d’exécution et de planification de requête.

Pour créer un plan de requête, exécutez la commande EXPLAIN suivie du texte de la requête réelle. Le plan de requête vous fournit les informations suivantes :

  • Les opérations effectuées par le moteur d’exécution, en lisant les résultats de bas en haut.

  • Le type d’étape effectué par chaque opération.

  • Les tables et les colonnes utilisées dans chaque opération.

  • La quantité de données traitée dans chaque opération, en termes de nombre de lignes et de largeur de données en octets.

  • Le coût relatif de l’opération. Cost est une mesure qui compare les durées d’exécution relatives des étapes au sein d’un plan. Cost ne fournit pas d’informations précises sur les durées d’exécution ou la consommation de mémoire réelle, ni de comparaison significative des plans d’exécution. Il vous donne une indication des opérations d’une requête qui consomment le plus de ressources.

La commande EXPLAIN n’exécute pas réellement la requête. Elle montre seulement le plan que Amazon Redshift exécutera si la requête est exécutée dans les conditions d’utilisation actuelles. Si vous modifiez le schéma ou les données d’une table et que vous exécutez ANALYSE à nouveau pour mettre à jour les métadonnées statistiques, le plan de requête peut être différent.

La sortie du plan de requête par EXPLAIN est une vue simplifiée et d’ensemble de l’exécution des requêtes. Il n’illustre pas les détails du traitement de requête parallèle. Pour consulter des informations détaillées, vous devez exécuter la requête elle-même, puis obtenir des informations récapitulatives sur la requête dans la vue SVL_QUERY_SUMMARY ou SVL_QUERY_REPORT. Pour plus d’informations sur l’utilisation de ces vues, consultez Analyse du résumé de la requête.

L’exemple suivant illustre la sortie EXPLAIN pour une requête GROUP BY simple sur la table EVENT :

explain select eventname, count(*) from event group by eventname;

                            QUERY PLAN
-------------------------------------------------------------------
XN HashAggregate  (cost=131.97..133.41 rows=576 width=17)
  ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=17)

EXPLAIN renvoie les métriques suivantes pour chaque opération :

Coût

Valeur relative utile pour comparer les opérations au sein d’un plan. Cost se compose de deux valeurs décimales séparées par des deux points, par exemple cost=131.97..133.41. La première valeur, dans le cas présent 131.97, fournit le coût relatif du renvoi de la première ligne pour cette opération. La seconde valeur, dans le cas présent 133.41, fournit le coût relatif de l’exécution de l’opération. Les coûts du plan de requête sont cumulatifs au fur et à mesure que vous lisez le plan. Le HashAggregate coût de cet exemple (131.97.. 133.41) inclut le coût du Seq Scan situé en dessous (0,00.. 87,98).

Lignes

Estimation du nombre de lignes à renvoyer. Dans cet exemple, l’analyse devrait renvoyer 8 798 lignes. L' HashAggregate opérateur lui-même est censé renvoyer 576 lignes (une fois que les noms d'événements dupliqués ont été supprimés du jeu de résultats).

Note

L’estimation de lignes repose sur les statistiques disponibles générées par la commande ANALYZE. Si ANALYZE n’a pas été exécutée récemment, l’estimation sera moins fiable.

Largeur

Largeur estimée de la ligne moyenne, en octets. Dans cet exemple, la ligne moyenne devrait avoir une largeur de 17 octets.

Opérateurs EXPLAIN

Cette section décrit brièvement les opérateurs que vous voyez le plus souvent dans la sortie EXPLAIN. Pour obtenir une liste complète des opérateurs, consultez EXPLAIN dans la section Commandes SQL.

Opérateur d’analyse séquentielle

L’opérateur d’analyse séquentiel (Seq Scan) indique une analyse de table. Seq Scan analyse chaque colonne de la tableau de manière séquentielle du début à la fin et évalue les contraintes de requête (dans la clause WHERE) de chaque ligne.

Opérateurs de jointure

Amazon Redshift sélectionne les opérateurs de jointure en fonction de la conception physique des tables jointes, de l’emplacement des données requises pour la jointure et des exigences spécifiques à la requête elle-même.

  • Boucle imbriquée

    La jointure la moins optimale, une boucle imbriquée, est utilisée principalement pour les jointures croisées (produits cartésiens) et certaines jointures d’inégalité.

  • Hash Join and Hash

    Généralement plus rapide qu’une boucle imbriquée, une jointure par hachage et un hachage sont utilisés pour les jointures internes et les jointures externes gauche et droite. Ces opérateurs sont utilisés lors de la jonction de tables, lorsque les colonnes de jointure ne sont pas des clés de distribution et des clés de tri. L’opérateur de hachage crée la table de hachage pour la table interne de la jointure ; l’opérateur de jointure par hachage lit la table externe, hache la colonne de jointure et recherche des correspondances dans la table de hachage interne.

  • Joindre par fusion

    Généralement la jointure la plus rapide, une jointure par fusion est utilisée pour les jointures internes et externes. La jointure par fusion n’est pas utilisée pour les jointures complètes. Cet opérateur est utilisé lors de la jonction de tables lorsque les colonnes de jointure sont des clés de distribution et des clés de tri, et lorsque moins de 20 % des tables jointes sont non triées. Il lit les deux tables triées dans l’ordre et recherche les lignes correspondantes. Pour afficher le pourcentage de lignes non triées, interrogez la table système SVV_TABLE_INFO.

  • Jointure spatiale

    Il s’agit généralement d’une jointure rapide basée sur la proximité des données spatiales, utilisées pour les types de données GEOMETRY et GEOGRAPHY.

Opérateurs d’agrégation

Le plan de requête utilise les opérateurs suivants dans les requêtes impliquant des fonctions d’agrégation et des opérations GROUP BY.

  • Regrouper

    Opérateur de fonctions d’agrégation scalaires telles que AVG et SUM.

  • HashAggregate

    Opérateur des fonctions d’agrégation groupées non triées.

  • GroupAggregate

    Opérateur des fonctions d’agrégation groupées triées.

Opérateurs de tri

Le plan de requête utilise les opérateurs suivants lorsque les requêtes doivent trier ou fusionner des jeux de résultats.

  • Tri

    Evalue la clause ORDER BY et d’autres opérations de tri, telles que les tris requis par les requêtes UNION et les requêtes SELECT DISTINCT de jointure et les fonctions de fenêtrage.

  • Fusionner

    Produit des résultats triés finaux selon les résultats triés intermédiaires qui proviennent d’opérations parallèles.

Opérateurs UNION, INTERSECT et EXCEPT

Le plan de requête utilise les opérateurs suivants pour des requêtes impliquant des opérations de jeu avec UNION, INTERSECT et EXCEPT.

  • Subquery

    Utilisé pour exécuter des requêtes UNION.

  • Hash Intersect Distinct

    Utilisé pour exécuter les requêtes INTERSECT .

  • SetOp Sauf

    Utilisé pour exécuter des requêtes EXCEPT (ou MINUS).

Autres opérateurs

Les opérateurs suivants apparaissent également fréquemment dans la sortie EXPLAIN pour les requêtes courantes.

  • Unique

    Supprime les doublons des requêtes SELECT DISTINCT et UNION.

  • Limite

    Traite la clause LIMIT.

  • Fenêtre

    Exécute les fonctions de fenêtrage.

  • Result

    Exécute les fonctions scalaires qui n’impliquent pas un accès aux tables.

  • Subplan

    Utilisé pour certaines sous-requêtes.

  • Réseau

    Envoie des résultats intermédiaires au nœud principal en vue d’un traitement ultérieur.

  • Materialize

    Enregistre les lignes pour l’entrée des jointures de boucles imbriquées et quelques autres jointures de fusion.

Jointures dans EXPLAIN

L’optimiseur de requête utilise différents types de jointures pour récupérer les données de la table, en fonction de la structure de la requête et des tables sous-jacentes. La sortie EXPLAIN fait référence au type de jointure, aux tables utilisées et à la manière dont les données de la table sont distribuées dans le cluster afin de décrire le traitement de la requête.

Exemples de types de jointures

Les exemples suivants illustrent les différents types de jointures que l’optimiseur de requête peut utiliser. Le type de jointure utilisé dans le plan de requête dépend de la conception physique des tables impliquées.

Exemple : Joindre par hachage deux tables

La requête suivante joint EVENT et CATEGORY sur la colonne CATID. CATID est la clé de tri et de distribution pour CATEGORY, mais pas pour EVENT. Une jointure par hachage est effectuée avec EVENT en tant que table externe et avec CATEGORY en tant que table interne. Etant donné que CATEGORY est la table la plus petite, le planificateur en diffuse une copie aux nœuds de calcul au cours du traitement de la requête à l’aide de DS_BCAST_INNER. Le coût de la jointure de cet exemple représente la majeure partie du coût cumulé du plan.

explain select * from category, event where category.catid=event.catid;

                               QUERY PLAN
-------------------------------------------------------------------------
 XN Hash Join DS_BCAST_INNER  (cost=0.14..6600286.07 rows=8798 width=84)
   Hash Cond: ("outer".catid = "inner".catid)
   ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=35)
   ->  XN Hash  (cost=0.11..0.11 rows=11 width=49)
         ->  XN Seq Scan on category  (cost=0.00..0.11 rows=11 width=49)
Note

Les retraits alignés pour les opérateurs dans la sortie EXPLAIN indiquent parfois que ces opérations ne dépendent pas l’une de l’autre et peuvent démarrer en parallèle. Dans l’exemple précédent, bien que l’analyse de la table EVENT et l’opération de hachage soient alignées, l’analyse EVENT doit attendre que l’opération de hachage soit entièrement terminée.

Exemple : Joindre par fusion deux tables

La requête suivante utilise également SELECT *, mais elle joint SALES et LISTING sur la colonne LISTID, dans laquelle LISTID a été défini comme clé de tri et de distribution des deux tables. Une jointure par fusion est choisie et aucun redistribution des données n’est requise pour la jointure (DS_DIST_NONE).

explain select * from sales, listing where sales.listid = listing.listid;
QUERY PLAN
-----------------------------------------------------------------------------
XN Merge Join DS_DIST_NONE  (cost=0.00..6285.93 rows=172456 width=97)
  Merge Cond: ("outer".listid = "inner".listid)
  ->  XN Seq Scan on listing  (cost=0.00..1924.97 rows=192497 width=44)
  ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=53)

L’exemple suivant illustre les différents types de jointures au sein de la même requête. Comme dans l’exemple précédent, SALES et LISTING sont jointes par fusion, mais la troisième table, EVENT, doit être jointe par hachage aux résultats de la jointure par fusion. Une fois encore, la jointure par hachage implique un coût de diffusion.

explain select * from sales, listing, event
where sales.listid = listing.listid and sales.eventid = event.eventid;
                                  QUERY PLAN
----------------------------------------------------------------------------
XN Hash Join DS_BCAST_INNER  (cost=109.98..3871130276.17 rows=172456 width=132)
  Hash Cond: ("outer".eventid = "inner".eventid)
  ->  XN Merge Join DS_DIST_NONE  (cost=0.00..6285.93 rows=172456 width=97)
        Merge Cond: ("outer".listid = "inner".listid)
        ->  XN Seq Scan on listing  (cost=0.00..1924.97 rows=192497 width=44)
        ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=53)
  ->  XN Hash  (cost=87.98..87.98 rows=8798 width=35)
        ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=35)

Exemple : Joindre, regrouper et trier

La requête suivante exécute une jointure par hachage des tables SALES et EVENT, suivie d’opérations d’agrégation et de tri afin de tenir compte de la fonction SUM groupée et de la clause ORDER BY. L’opérateur Sort initial s’exécute en parallèle sur les nœuds de calcul. Puis, l’opérateur Network envoie les résultats au nœud principal, dans lequel l’opérateur Merge produit les résultats triés finaux.

explain select eventname, sum(pricepaid) from sales, event 
where sales.eventid=event.eventid group by eventname
order by 2 desc;
                                           QUERY PLAN
---------------------------------------------------------------------------------
 XN Merge  (cost=1002815366604.92..1002815366606.36 rows=576 width=27)
  Merge Key: sum(sales.pricepaid)
  ->  XN Network  (cost=1002815366604.92..1002815366606.36 rows=576 width=27)
        Send to leader
        ->  XN Sort  (cost=1002815366604.92..1002815366606.36 rows=576 width=27)
              Sort Key: sum(sales.pricepaid)
              ->  XN HashAggregate  (cost=2815366577.07..2815366578.51 rows=576 width=27)
                    ->  XN Hash Join DS_BCAST_INNER  (cost=109.98..2815365714.80 rows=172456 width=27)
                          Hash Cond: ("outer".eventid = "inner".eventid)
                          ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=14)
                          ->  XN Hash  (cost=87.98..87.98 rows=8798 width=21)
                                ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=21)

Redistribution des données

La sortie EXPLAIN des jointures spécifie également une méthode permettant de déplacer les données autour d’un cluster pour faciliter la jointure. Ce mouvement des données peut être une diffusion ou une redistribution. Dans une diffusion, les valeurs de données d’un côté d’une jointure sont copiées à partir de chaque nœud de calcul dans tous les autres nœuds de calcul, afin que chaque nœud de calcul se retrouve avec une copie complète des données. Dans une redistribution, les valeurs de données participantes sont envoyés de leur tranche actuelle vers une nouvelle tranche (éventuellement sur un autre nœud). Les données sont généralement redistribuées pour correspondre à la clé de distribution de l’autre table participant à la jointure si cette clé de distribution est l’une des colonnes de jointure. Si aucune des tables ne dispose de clés de distribution sur l’une des tables de jointure, les deux tables sont distribuées ou la table interne est diffusée à chaque nœud.

La sortie EXPLAIN fait également référence aux tables internes et externes. La table interne est analysée d’abord et s’affiche près de bas du plan de requête. La table interne est la table qui fait l’objet d’une recherche de correspondances. Elle est généralement conservée en mémoire, est généralement la table source pour le hachage et, si possible, est la plus petite des deux tables qui sont jointes. La table externe est la source des lignes à mettre en correspondant avec la table interne. Elle est généralement lue à partir du disque. L’optimiseur de requête choisit la table interne et la table externe en fonction des statistiques de la base de données obtenues lors de la dernière exécution de la commande ANALYZE. L’ordre des tables dans la clause FROM d’une requête ne détermine pas quelle table est interne et quelle table est externe.

Utilisez les attributs suivants dans les plans de requête pour identifier la manière dont les données sont déplacées afin de simplifier une requête :

  • DS_BCAST_INNER

    Une copie de la totalité de la table interne est diffusée à tous les nœuds de calcul.

  • DS_DIST_ALL_NONE

    Aucun redistribution n’est obligatoire, car la table interne a déjà été distribuée à chaque nœud à l’aide de DISTSTYLE ALL.

  • DS_DIST_NONE

    Aucune table n’est redistribuée. Les jointures colocalisées sont possibles, car les tranches correspondantes sont jointes sans transfert de données entre les nœuds.

  • DS_DIST_INNER

    La table interne est redistribuée.

  • DS_DIST_OUTER

    La table externe est redistribuée.

  • DS_DIST_ALL_INNER

    La totalité de la table interne est redistribué à une seule tranche, car la table externe utilise DISTSTYLE ALL.

  • DS_DIST_BOTH

    Les deux tables sont redistribuées.