Optimiser les performances des requêtes - AnalyticDB - Centre de documentation Alibaba Cloud

AnalyticDB for PostgreSQL propose plusieurs outils pour diagnostiquer et corriger les requêtes lentes. Ce guide vous aide à identifier la cause racine du problème et à appliquer la correction appropriée.

Symptôme	Où chercher
Les requêtes sont lentes après des chargements massifs de données	Collecter les statistiques de table
Les requêtes complexes impliquant plusieurs tables sont lentes	Choisir un optimiseur de requête
Les recherches ponctuelles ou les requêtes par plage sont lentes	Utiliser des index pour accélérer les requêtes
Vous ne savez pas ce que fait la base de données	Afficher les plans de requête
Les jointures génèrent un trafic réseau important	Supprimer les opérateurs de distribution
Les colonnes de jointure utilisent des types de données différents	Utiliser des types de données identiques sur les colonnes de jointure
Un seul nœud de calcul effectue tout le travail	Identifier les déséquilibres de données
Trop de requêtes simultanées s'accumulent	Afficher les instructions SQL en cours d'exécution
Les requêtes restent bloquées en attente	Vérifier l'état des verrous
Les requêtes hautement sélectives sont lentes malgré la présence d'index	Activer les jointures par boucle imbriquée

Collecter les statistiques de table

L'optimiseur de requête génère des plans d'exécution à partir des statistiques de table. Des statistiques obsolètes ou manquantes conduisent l'optimiseur à produire des estimations erronées, générant ainsi des plans inefficaces.

Exécutez ANALYZE après un chargement important de données ou lorsque plus de 20 % des lignes d'une table ont été mises à jour.

-- Collect statistics on all tables
ANALYZE;

-- Collect statistics on all columns of table t
ANALYZE t;

-- Collect statistics on a specific column
ANALYZE t(a);

Pour la plupart des charges de travail, il suffit d'exécuter ANALYZE t sur la table modifiée. N'utilisez l'instruction ANALYZE au niveau des colonnes que si vous avez besoin d'un contrôle plus fin, par exemple sur les colonnes servant de clés de jointure, de conditions de filtrage ou de colonnes d'index.

Choisir un optimiseur de requête

AnalyticDB for PostgreSQL intègre deux optimiseurs de requête. Chacun est optimisé pour des charges de travail différentes.

	Ancien optimiseur	Optimiseur de requête ORCA
Par défaut dans	V4.3	V6.0
Idéal pour	Requêtes simples à forte concurrence ; jointures jusqu'à 3 tables ; charges de travail INSERT, UPDATE, DELETE	Requêtes complexes ; jointures de plus de 3 tables ; charges de travail ETL (extraction, transformation, chargement) et reporting ; SQL avec sous-requêtes (évite la nécessité de joindre les tables dans les sous-requêtes) ; requêtes sur des tables partitionnées avec des conditions de filtre spécifiées par paramètre (filtrage dynamique des partitions)
Compromis	Génération plus rapide du plan	Explore davantage de chemins d'exécution ; la génération du plan prend plus de temps

Basculez d'optimiseur au niveau de la session :

-- Enable the Legacy query optimizer
SET optimizer = off;

-- Enable the ORCA query optimizer
SET optimizer = on;

-- Check the current optimizer
SHOW optimizer;
-- on = ORCA query optimizer
-- off = Legacy optimizer

Pour modifier l'optimiseur au niveau de l'instance, soumettez un ticket.

Utiliser des index pour accélérer les requêtes

Les index accélèrent les requêtes qui analysent une petite fraction d'une table selon une condition de filtre. AnalyticDB for PostgreSQL prend en charge trois types d'index.

Type d'index	Quand l'utiliser
Index B-tree	La colonne contient de nombreuses valeurs uniques et sert à filtrer, joindre ou trier les données
Index Bitmap	La colonne comporte peu de valeurs uniques et est référencée par plusieurs conditions de filtre
Index GiST	La requête implique des localisations géographiques, des plages, des caractéristiques d'image ou des valeurs Geometry

Exemple : ajout d'un index B-tree

Sans index, l'optimiseur effectue une analyse complète de la table :

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
   ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=16)
         Filter: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

Créez un index B-tree sur la colonne b :

postgres=# CREATE INDEX i_t_b ON t USING btree (b);
CREATE INDEX

Avec l'index, l'optimiseur utilise plutôt une analyse d'index :

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.00 rows=1 width=16)
   ->  Index Scan using i_t_b on t  (cost=0.00..2.00 rows=1 width=16)
         Index Cond: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

Afficher les plans de requête

Un plan de requête est une arborescence d'opérateurs décrivant la manière dont la base de données exécute une requête. Lire le plan vous aide à comprendre pourquoi une requête est lente.

Comment lire un plan de requête

Le plan forme une arborescence. L'exécution commence aux nœuds feuilles (en bas) et remonte vers la racine.
cost=<startup>..<total> représente le coût estimé par l'optimiseur, et non le temps réel en millisecondes. Une estimation de coût élevée signifie que l'optimiseur s'attend à ce que l'opération soit coûteuse ; utilisez-la à titre comparatif, pas comme une mesure absolue.
rows= indique le nombre estimé de lignes. Un écart important entre les lignes estimées et réelles signale souvent des statistiques obsolètes ; exécutez ANALYZE pour corriger le problème.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN affiche le plan sans exécuter la requête.
EXPLAIN ANALYZE exécute la requête et ajoute les temps d'exécution réels ainsi que le nombre de lignes au plan.

-- Display plan only (no execution)
postgres=# EXPLAIN SELECT a, b FROM t;
                                      QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
 Optimizer status: legacy query optimizer
(3 rows)

-- Run query and show actual timings
postgres=# EXPLAIN ANALYZE SELECT a, b FROM t;
                                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   Rows out:  100 rows at destination with 2.728 ms to first row, 2.838 ms to end, start offset by 0.418 ms.
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
         Rows out:  Avg 33.3 rows x 3 workers.  Max 37 rows (seg2) with 0.088 ms to first row, 0.107 ms to end, start offset by 2.887 ms.
 Slice statistics:
   (slice0)    Executor memory: 131K bytes.
   (slice1)    Executor memory: 163K bytes avg x 3 workers, 163K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: legacy query optimizer
 Total runtime: 3.739 ms
(11 rows)

Identifier le goulot d'étranglement à partir de la sortie EXPLAIN ANALYZE

Utilisez les données de timing et de nombre de lignes réels pour classer le goulot d'étranglement :

Ce que vous observez	Goulot d'étranglement probable	Étape suivante
Temps élevé sur les opérateurs Redistribute Motion ou Broadcast Motion	Réseau — les données sont redistribuées entre les nœuds	Alignez les clés de distribution sur les colonnes de jointure (voir Supprimer les opérateurs de distribution)
Les lignes estimées diffèrent grandement des lignes réelles	Statistiques obsolètes	Exécutez `ANALYZE` sur les tables concernées
Temps élevé sur Seq Scan ou Table Scan avec un grand nombre de lignes	E/S — analyse complète de la table	Ajoutez un index sur la colonne de filtre (voir Utiliser des index pour accélérer les requêtes)
Utilisation mémoire élevée dans les statistiques de slice	Pression sur la mémoire	Réduisez la concurrence ou augmentez les ressources de l'instance

Types d'opérateurs pris en charge

Catégorie	Opérateurs
Analyse des données	Seq Scan, Table Scan, Index Scan, Bitmap Scan
Jointure	Hash Join, Nested Loop, Merge Join
Agrégation	Hash Aggregate, Group Aggregate
Distribution	Redistribute Motion, Broadcast Motion, Gather Motion
Autre	Hash, Sort, Limit, Append

Exemple : lecture d'un plan de requête de jointure

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.b = t2.b;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.b = t2.b
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.b
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.b
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

En lisant ce plan de bas en haut :

Table Scan — analyse t1 et t2.
Redistribute Motion — redistribue les lignes des deux tables entre les nœuds de calcul en fonction de la valeur de hachage de b, afin que les lignes correspondantes se retrouvent sur le même nœud.
Hash — construit une table de hachage sur t2 pour la jointure.
Hash Join — joint t1 et t2 sur la clé de hachage.
Gather Motion — envoie les résultats des nœuds de calcul au nœud coordinateur, qui les renvoie au client.

Supprimer les opérateurs de distribution

Lorsque AnalyticDB for PostgreSQL joint ou agrège des données entre plusieurs nœuds de calcul, il insère des opérateurs Redistribute Motion ou Broadcast Motion pour redistribuer les données. Ces opérateurs consomment une bande passante réseau significative. L'alignement des clés de distribution sur les colonnes de jointure élimine le besoin de redistribuer les données.

Fonctionnement

Si deux tables sont jointes sur la colonne a et que toutes deux sont distribuées par a, chaque nœud de calcul détient déjà les lignes correspondantes ; aucun mouvement de données n'est nécessaire.

Exemple

SELECT * FROM t1, t2 WHERE t1.a = t2.a;

t1 est distribuée par la colonne a. Si t2 est distribuée par la colonne b (incompatibilité), AnalyticDB for PostgreSQL doit redistribuer t2 :

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(10 rows)

Si t2 est également distribuée par a, la jointure s'exécute sans redistribution :

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

Pour corriger une incompatibilité, modifiez la clé de distribution de t2 :

ALTER TABLE t2 SET DISTRIBUTED BY (a);

Utiliser des types de données identiques sur les colonnes de jointure

Joindre des colonnes de types de données différents déclenche une conversion de type, ce qui force la redistribution des données. Utilisez le même type de données des deux côtés de la clé de jointure.

Conversion de type explicite

Un cast explicite dans l'instruction SQL modifie la fonction de hachage appliquée à la colonne, ce qui pousse l'optimiseur à redistribuer les deux tables :

-- No type conversion: no redistribution
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

-- Explicit cast to numeric: triggers redistribution of both tables
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a::numeric;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a::numeric = t2.a::numeric
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.a::numeric
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a::numeric
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

Conversion de type implicite

Lorsque deux colonnes de jointure utilisent des types incompatibles, la base de données convertit automatiquement l'un des types. Par exemple, timestamp without time zone et timestamp with time zone utilisent des fonctions de hachage différentes. L'optimiseur revient alors à Broadcast Motion au lieu d'une jointure basée sur le hachage :

postgres=# CREATE TABLE t1 (a timestamp without time zone);
CREATE TABLE
postgres=# CREATE TABLE t2 (a timestamp with time zone);
CREATE TABLE

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                               QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.04..0.11 rows=4 width=16)
   ->  Nested Loop  (cost=0.04..0.11 rows=2 width=16)
         Join Filter: t1.a = t2.a
         ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=8)
         ->  Materialize  (cost=0.04..0.07 rows=1 width=8)
               ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..0.04 rows=1 width=8)
                     ->  Seq Scan on t2  (cost=0.00..0.00 rows=1 width=8)
(7 rows)

Pour éliminer les conversions implicites, alignez les types de colonnes dès la définition de la table. Par exemple, définissez à la fois t1.a et t2.a comme timestamp without time zone.

Identifier les déséquilibres de données

Un déséquilibre de données survient lorsque les lignes sont réparties de manière inégale entre les nœuds de calcul. Une table déséquilibrée contraint un seul nœud de calcul à effectuer la majeure partie du travail tandis que les autres restent inactifs, ce qui ralentit l'ensemble de la requête.

Vérifiez la distribution des lignes entre les nœuds de calcul :

postgres=# SELECT gp_segment_id, count(1) FROM t1 GROUP BY 1 ORDER BY 2 DESC;
 gp_segment_id | count
---------------+-------
             0 | 16415
             2 |    37
             1 |    32
(3 rows)

Cette sortie révèle un déséquilibre sévère : le nœud 0 détient 99 % des lignes. Corrigez-le en réattribuant la clé de distribution :

-- Option 1: change the distribution key in place
ALTER TABLE t1 SET DISTRIBUTED BY (b);

-- Option 2: recreate the table with a better distribution key
-- Create the new table, bulk-load data, then swap it in place of the original

Choisissez une clé de distribution à forte cardinalité et sans valeurs dominantes pour garantir une répartition homogène.

Afficher les instructions SQL en cours d'exécution

Lorsque de nombreuses requêtes s'exécutent simultanément, l'instance peut signaler des réponses lentes ou un manque de ressources. Utilisez la vue pg_stat_activity pour voir ce qui est actuellement en cours d'exécution.

postgres=# SELECT * FROM pg_stat_activity;

Champs clés :

Champ	Description
`procpid`	ID du processus maître exécutant la requête
`usename`	Nom d'utilisateur de la session
`current_query`	Texte de la requête actuelle
`waiting`	Indique si la requête attend un verrou
`query_start`	Moment où la requête a commencé
`backend_start`	Moment où le processus backend a démarré
`xact_start`	Moment où la transaction actuelle a commencé
`waiting_reason`	Raison pour laquelle la requête est en attente

Filtrez pour n'afficher que les requêtes actives :

SELECT * FROM pg_stat_activity WHERE current_query != '<IDLE>';

Trouvez les cinq requêtes les plus longues :

SELECT current_timestamp - query_start AS runtime,
       datname,
       usename,
       current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>'
ORDER BY runtime DESC
LIMIT 5;

Vérifier l'état des verrous

Si une requête conserve un verrou pendant une période prolongée, les autres requêtes portant sur le même objet restent bloquées indéfiniment. Exécutez la requête suivante pour voir quelles tables sont verrouillées et quelles sessions détiennent les verrous :

SELECT pgl.locktype   AS locktype,
       pgl.database   AS database,
       pgc.relname    AS relname,
       pgl.relation   AS relation,
       pgl.transaction AS transaction,
       pgl.pid        AS pid,
       pgl.mode       AS mode,
       pgl.granted    AS granted,
       pgsa.current_query AS query
FROM pg_locks pgl
JOIN pg_class pgc ON pgl.relation = pgc.oid
JOIN pg_stat_activity pgsa ON pgl.pid = pgsa.procpid
ORDER BY pgc.relname;

Pour débloquer une requête en attente, annulez ou terminez la session qui détient le verrou :

-- Cancel the query (does not work if the session is IDLE)
SELECT pg_cancel_backend(pid);

-- Terminate the session and roll back its uncommitted transactions
SELECT pg_terminate_backend(pid);

pg_cancel_backend n'a aucun effet lorsque pg_stat_activity.current_query affiche IDLE . Dans ce cas, utilisez pg_terminate_backend .

Activer les jointures par boucle imbriquée

Les jointures par boucle imbriquée sont désactivées par défaut. Pour les requêtes qui retournent un petit nombre de lignes — grâce à des conditions de filtre hautement sélectives et une clause LIMIT — l'activation des jointures par boucle imbriquée peut réduire considérablement le temps de requête.

Quand activer cette option

L'optimisation est efficace lorsque :

Une condition de filtre sur une table retourne très peu de lignes.
Une clause LIMIT restreint davantage l'ensemble de résultats.
Des index sont disponibles sur les colonnes de jointure des deux tables.