Analyse des tables - Amazon Redshift

Analyse des tables

L'opération ANALYZE met à jour les métadonnées statistiques que le planificateur de requête utilise pour choisir les plans optimaux.

Dans la plupart des cas, vous n'avez pas besoin d'exécuter explicitement la commande ANALYZE. Amazon Redshift surveille les modifications apportées à votre application et met à jour automatiquement les statistiques en arrière-plan. De plus, la commande COPY effectue automatiquement une analyse lorsqu'elle charge des données dans une table vide.

Pour analyser explicitement une table ou la base de données complète, exécutez la commande ANALYZE.

Analyse automatique

Amazon Redshift surveille en permanence votre base de données et effectue automatiquement des opérations d'analyse en arrière-plan. Pour réduire au maximum l'impact sur vos performances système, l'analyse automatique s'exécute pendant les périodes où les charges de travail sont légères.

L'analyse automatique est activée par défaut. Pour désactiver l'analyse automatique, attribuez au paramètre auto_analyze la valeur false en modifiant le groupe de paramètres de votre cluster.

Pour réduire le temps de traitement et améliorer les performances globales du système, Amazon Redshift ignore l'analyse automatique pour toute table dont l'ampleur des modifications est petite.

Une opération d'analyse ignore les tables dont les statistiques sont à jour. Si vous exécutez ANALYZE dans le cadre de votre flux de travail ETL (extraction, transformation et chargement), l'analyse automatique ignore les tables dont les statistiques sont à jour. De façon similaire, une opération ANALYZE explicite ignore les tables pour lesquelles l'analyse automatique a mis à jour les statistiques.

Analyse des données des nouvelles tables

Par défaut, la commande COPY effectue une opération ANALYZE après avoir chargé des données dans une table vide. Vous pouvez forcer une opération ANALYZE, indépendamment du fait qu'une table soit vide ou non, en définissant STATUPDATE avec la valeur ON. Si vous spécifiez STATUPDATE OFF, l'opération ANALYZE n'est pas effectuée. Seul le propriétaire de la table ou un super-utilisateur peut exécuter la commande ANALYZE ou la commande COPY avec STATUPDATE ayant ON comme valeur.

Amazon Redshift analyse également les nouvelles tables que vous créez avec les commandes suivantes :

  • CREATE TABLE AS (CTAS)

  • CREATE TEMP TABLE AS

  • SELECT INTO

Amazon Redshift renvoie un message d'avertissement lorsque vous exécutez une requête sur une nouvelle table qui n'a pas été analysée après le chargement initial de ses données. Aucun avertissement n'est fourni lorsque vous interrogez une table après un chargement ou une mise à jour ultérieur(e). Le même message d'avertissement est renvoyé lorsque vous exécutez la commande EXPLAIN sur une requête qui référence des tables qui n'ont pas été analysées.

Chaque fois qu'un ajout de données à une table non vide modifie de façon importante la taille de la table, vous pouvez mettre à jour explicitement les statistiques. Pour ce faire, vous pouvez exécuter une commande ANALYZE ou utiliser l'option STATUPDATE ON avec la commande COPY. Pour afficher les détails du nombre de lignes qui ont été insérées ou supprimées depuis la dernière opération ANALYZE, interrogez la table catalogue système PG_STATISTIC_INDICATOR.

Vous pouvez définir la portée de la commande ANALYZE en spécifiant l'une des valeurs suivantes :

  • La totalité de la base de données actuelle

  • Une seule table

  • Une ou plusieurs colonnes spécifiques d'une seule table

  • Colonnes qui sont susceptibles d'être utilisées comme prédicats dans des requêtes

La commande ANALYZE obtient un échantillon de lignes de la table, effectue quelques calculs et enregistre les statistiques de colonnes obtenues. Par défaut, Amazon Redshift exécute un exemple de passage pour la colonne DISTKEY et un autre exemple de passage pour toutes les autres colonnes de la table. Si vous souhaitez générer des statistiques pour un sous-ensemble de colonnes, vous pouvez spécifier une liste de colonnes séparées par des virgules. Vous pouvez exécuter ANALYZE avec la clause PREDICATE COLUMNS pour ignorer les colonnes qui ne sont pas utilisées comme prédicats.

Comme les opérations ANALYZE sont gourmandes en ressources, exécutez-les uniquement sur des tables et des colonnes qui nécessitent des mises à jour des statistiques. Vous n'avez pas besoin d'analyser toutes les colonnes de toutes les tables régulièrement ou selon le même calendrier. Si les données changent considérablement, analysez les colonnes qui sont fréquemment utilisées dans les cas suivants :

  • Tri et regroupement d'opérations

  • Jointures

  • Prédicats de requête

Pour réduire le délai de traitement et améliorer les performances globales du système, Amazon Redshift ignore ANALYZE pour chaque table dont le pourcentage de lignes modifiées est faible, comme déterminé par le paramètre analyze_threshold_percent. Par défaut, le seuil d'analyse est défini sur 10 %. Vous pouvez modifier le seuil d'analyse pour la session en cours en exécutant une commande SET.

Les colonnes qui sont moins susceptibles d'exiger des analyses fréquentes sont celles qui représentent des faits et des mesures, ainsi que les attributs associés qui sont jamais réellement interrogés, comme les colonnes VARCHAR volumineuses. Par exemple, considérons la table LISTING de la base de données TICKIT.

select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'listing'; column | type | encoding | distkey | sortkey ---------------+--------------------+----------+---------+--------- listid | integer | none | t | 1 sellerid | integer | none | f | 0 eventid | integer | mostly16 | f | 0 dateid | smallint | none | f | 0 numtickets | smallint | mostly8 | f | 0 priceperticket | numeric(8,2) | bytedict | f | 0 totalprice | numeric(8,2) | mostly32 | f | 0 listtime | timestamp with... | none | f | 0

Si cette table est chargée tous les jours avec un grand nombre de nouveaux enregistrements, la colonne LISTID, qui est fréquemment utilisée dans les requêtes comme clé de jointure, doit être analysée régulièrement. Si TOTALPRICE et LISTTIME sont les contraintes fréquemment utilisées dans les requêtes, vous pouvez analyser ces colonnes et la clé de distribution sur chaque jour de la semaine.

analyze listing(listid, totalprice, listtime);

Supposons que les vendeurs et les événements dans l'application soient beaucoup plus statiques et que les ID de date se rapportent à un ensemble fixe de jours ne couvrant que deux ou trois années. Dans ce cas, les valeurs uniques de ces colonnes ne changent pas de façon significative. Cependant, le nombre d'instances de chaque valeur unique augmente de façon continue.

En outre, prenons l'exemple d'une situation où les mesures NUMTICKETS et PRICEPERTICKET sont interrogées peu fréquemment par rapport à la colonne TOTALPRICE. Dans ce cas, vous pouvez exécuter la commande ANALYZE sur l'ensemble de la table une fois tous les week-ends afin de mettre à jour les statistiques des cinq colonnes qui ne sont pas analysées quotidiennement :

Colonnes de prédicat

Comme solution pratique autre que spécifier une liste de colonnes, vous pouvez choisir d'analyser uniquement les colonnes susceptibles d'être utilisées comme prédicats. Vous pouvez exécuter une requête. Les colonnes qui sont utilisées dans une jointure, une condition de filtre ou une clause group by sont marquées en tant que comme colonnes de prédicat dans le catalogue système. Lorsque vous exécutez ANALYZE avec la clause PREDICATE COLUMNS, l'opération d'analyse inclut uniquement les colonnes qui remplissent les critères suivants :

  • La colonne est marquée en tant que colonne de prédicat.

  • La colonne est une clé de distribution.

  • La colonne fait pas partie d'une clé de tri.

Si aucune des colonnes d'une table n'est marquée comme prédicat, ANALYZE inclut toutes les colonnes, même si PREDICATE COLUMNS est spécifié. Si aucune colonne n'est marquée comme colonne de prédicat, c'est peut-être parce que la table n'a pas encore été interrogée.

Vous pouvez choisir d'utiliser PREDICATE COLUMNS lorsque votre modèle de requête de charge de travail est relativement stable. Lorsque le modèle de requête est variable, avec des colonnes différentes souvent utilisées comme prédicats, l'utilisation de PREDICATE COLUMNS peut se traduire temporairement par des statistiques obsolètes. Des statistiques obsolètes peuvent donner lieu à des plans d'exécution de requête peu efficaces et des lenteurs d'exécution. Cependant, la fois suivante où vous exécutez ANALYZE avec PREDICATE COLUMNS, les nouvelles colonnes de prédicat sont incluses.

Pour afficher des détails sur les colonnes de prédicat, utilisez la requête SQL suivante pour créer une vue nommée PREDICATE_COLUMNS.

CREATE VIEW predicate_columns AS WITH predicate_column_info as ( SELECT ns.nspname AS schema_name, c.relname AS table_name, a.attnum as col_num, a.attname as col_name, CASE WHEN 10002 = s.stakind1 THEN array_to_string(stavalues1, '||') WHEN 10002 = s.stakind2 THEN array_to_string(stavalues2, '||') WHEN 10002 = s.stakind3 THEN array_to_string(stavalues3, '||') WHEN 10002 = s.stakind4 THEN array_to_string(stavalues4, '||') ELSE NULL::varchar END AS pred_ts FROM pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_namespace ns ON c.relnamespace = ns.oid JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum) SELECT schema_name, table_name, col_num, col_name, pred_ts NOT LIKE '2000-01-01%' AS is_predicate, CASE WHEN pred_ts NOT LIKE '2000-01-01%' THEN (split_part(pred_ts, '||',1))::timestamp ELSE NULL::timestamp END as first_predicate_use, CASE WHEN pred_ts NOT LIKE '%||2000-01-01%' THEN (split_part(pred_ts, '||',2))::timestamp ELSE NULL::timestamp END as last_analyze FROM predicate_column_info;

Supposons que vous exécutiez la requête suivante sur la table LISTING. Notez que les colonnes LISTID, LISTTIME et EVENTID sont utilisées dans les clauses de jointure, de filtre et group by.

select s.buyerid,l.eventid, sum(l.totalprice) from listing l join sales s on l.listid = s.listid where l.listtime > '2008-12-01' group by l.eventid, s.buyerid;

Lorsque vous interrogez la vue PREDICATE_COLUMNS, comme illustré dans l'exemple suivant, vous voyez que les colonnes LISTID, EVENTID et LISTTIME sont marquées en tant que colonnes de prédicat.

select * from predicate_columns where table_name = 'listing';
schema_name | table_name | col_num | col_name       | is_predicate | first_predicate_use | last_analyze       
------------+------------+---------+----------------+--------------+---------------------+--------------------
public      | listing    |       1 | listid         | true         | 2017-05-05 19:27:59 | 2017-05-03 18:27:41
public      | listing    |       2 | sellerid       | false        |                     | 2017-05-03 18:27:41
public      | listing    |       3 | eventid        | true         | 2017-05-16 20:54:32 | 2017-05-03 18:27:41
public      | listing    |       4 | dateid         | false        |                     | 2017-05-03 18:27:41
public      | listing    |       5 | numtickets     | false        |                     | 2017-05-03 18:27:41
public      | listing    |       6 | priceperticket | false        |                     | 2017-05-03 18:27:41
public      | listing    |       7 | totalprice     | false        |                     | 2017-05-03 18:27:41
public      | listing    |       8 | listtime       | true         | 2017-05-16 20:54:32 | 2017-05-03 18:27:41

Tenir les statistiques à jour améliore les performances des requêtes en permettant au planificateur de requête de choisir les plans optimaux. Amazon Redshift actualise automatiquement les statistiques en arrière-plan. Vous pouvez également exécuter explicitement la commande ANALYZE. Si vous choisissez d'exécuter explicitement ANALYZE, procédez comme suit :

  • Exécutez la commande ANALYZE avant d'exécuter les requêtes.

  • Exécutez la commande ANALYZE sur la base de données systématiquement à la fin de chaque chargement ou cycle de mise à jour régulier.

  • Exécutez la commande ANALYZE sur les nouvelles tables que vous créez et sur les tables ou les colonnes existantes qui subissent des modifications significatives.

  • Envisagez d'exécuter les opérations ANALYZE selon différents échéanciers pour différents types de tables et de colonnes, en fonction de leur utilisation dans les requêtes et leur propension au changement.

  • Pour gagner du temps et économiser des ressources de cluster, utilisez la clause PREDICATE COLUMNS lorsque vous exécutez ANALYZE.

Une opération d'analyse ignore les tables dont les statistiques sont à jour. Si vous exécutez ANALYZE dans le cadre de votre flux de travail ETL (extraction, transformation et chargement), l'analyse automatique ignore les tables dont les statistiques sont à jour. De façon similaire, une opération ANALYZE explicite ignore les tables pour lesquelles l'analyse automatique a mis à jour les statistiques.