how to use the index_adviser extension on RDS PostgreSQL - ApsaraDB RDS

L'extension index_adviser analyse vos requêtes et recommande des index pour améliorer les performances de charges de travail spécifiques. Elle identifie les colonnes à indexer et génère des instructions CREATE INDEX prêtes à l'emploi.

Limitation : L'extension index_adviser recommande uniquement des index B-tree sur une seule colonne ou des index composites. Elle ne recommande pas d'index GIN, GiST ou Hash.

Prérequis

Avant de commencer, assurez-vous que :

L'instance RDS exécute la version mineure du moteur 20230830 ou une version ultérieure.

Important

Si votre instance exécute une version mineure du moteur antérieure à 20230830 et que vous utilisez déjà cette extension, vous pouvez continuer à l'utiliser. Pour créer ou recréer l'extension, mettez à jour la version mineure du moteur vers la dernière version disponible. Pour plus d'informations sur les exigences de sécurité des plugins, consultez la rubrique Restrictions sur la création de plugins.

Composants

La création de l'extension génère automatiquement trois composants :

Composant	Type	Description
`index_advisory`	Table	Enregistre les suggestions d'indexation générées lors de l'analyse des requêtes
`show_index_advisory()`	Fonction PL/pgSQL	Renvoie des instructions `CREATE INDEX` formatées pour une session spécifique, identifiée par son ID de processus backend
`select_index_advisory`	Vue	Affiche les suggestions d'indexation pour toutes les sessions ; le format est identique à celui de la sortie de `show_index_advisory()`

Configuration de l'extension

Créez l'extension.

postgres=# create extension index_adviser;
CREATE EXTENSION

Chargez l'extension pour la session actuelle.
```
postgres=# LOAD 'index_adviser';
LOAD
```
Remarque
L'instruction LOAD s'applique uniquement à la session actuelle. Pour charger l'extension automatiquement pour toutes les sessions, ajoutez-la au paramètre shared_preload_libraries et redémarrez l'instance. Cette opération peut affecter les performances de l'instance.
```
shared_preload_libraries='index_adviser'
```

Obtention de recommandations d'index

Analyse d'une requête unique sans exécution

Préfixez n'importe quelle requête avec EXPLAIN pour obtenir des recommandations d'index sans exécuter la requête. L'extension intègre directement les recommandations dans la sortie du plan de requête.

L'exemple suivant crée une table de test et exécute trois requêtes EXPLAIN :

CREATE TABLE t( a INT, b INT );
INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
ANALYZE t;

postgres=# EXPLAIN SELECT * FROM t WHERE a < 10000;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=9983 width=8)
   Filter: (a < 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..256.52 rows=9983 width=8)
         Index Cond: (a < 10000)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE a = 100;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (a = 100)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (a = 100)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE b = 10000;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (b = 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_b_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (b = 10000)
(6 rows)

La ligne One-Time Filter: ' plan (using Index Adviser) ' indique que l'extension a enregistré des recommandations. Interrogez la table index_advisory pour les consulter.

Analyse des requêtes pendant l'exécution

Sans le préfixe EXPLAIN, l'extension analyse les requêtes lors de leur exécution et enregistre automatiquement les recommandations.

Remarque

N'utilisez pas l'extension index_adviser dans les transactions en lecture seule.

Interrogation de la table index_advisory

Après avoir exécuté des requêtes (avec ou sans EXPLAIN), interrogez la table index_advisory pour afficher les recommandations brutes :

postgres=# SELECT * FROM index_advisory;
 reloid | relname | attrs | benefit | original_cost | new_cost | index_size | backend_pid |            timestamp
--------+---------+-------+---------+---------------+----------+------------+-------------+----------------------------------
  16438 | t       | {1}   | 1337.43 |          1693 |  355.575 |       2624 |       79370 | 18-JUN-21 08:55:51.492388 +00:00
  16438 | t       | {1}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:00.319336 +00:00
  16438 | t       | {2}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:07.814453 +00:00
(3 rows)

Description des champs :

Champ	Type	Description
`reloid`	oid	Identifiant d'objet (OID) de la table
`relname`	name	Nom de la table
`attrs`	integer[]	Numéro de colonne pour l'index recommandé
`benefit`	real	Réduction estimée du coût de la requête grâce à l'index
`original_cost`	real	Temps moyen nécessaire à l'exécution de l'instruction SQL avant l'utilisation de l'index pour accélérer la requête
`new_cost`	real	Temps moyen nécessaire à l'exécution de l'instruction SQL après l'utilisation de l'index pour accélérer la requête
`index_size`	integer	Taille estimée de l'index en pages disque
`backend_pid`	integer	ID du processus backend de la session qui a généré cette recommandation
`timestamp`	timestamp	Moment où la recommandation a été générée

Obtention de recommandations au niveau de la session

La fonction show_index_advisory() et la vue select_index_advisory agrègent les recommandations par requête en résumés au niveau de la session et produisent des instructions CREATE INDEX prêtes à l'emploi.

Utilisation de show_index_advisory()

Appelez la fonction avec un ID de processus backend pour obtenir les recommandations de cette session. Passez la valeur null pour obtenir les recommandations de la session actuelle :

postgres=# SELECT show_index_advisory(null);
                                                             show_index_advisory
----------------------------------------------------------------------------------------------------------------------------------------------------
 create index idx_t_a on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
 create index idx_t_b on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)

Chaque ligne contient une instruction CREATE INDEX suivie d'un commentaire incluant ces métriques :

Métrique	Description
`size`	Taille estimée de l'index en Ko — `MAX(index_size)` pour toutes les requêtes bénéficiant de cet index
`benefit`	Réduction totale du coût pour toutes les requêtes bénéficiant de cet index — `SUM(benefit)` par requête
`gain`	Ratio bénéfice/taille : `SUM(benefit) / MAX(index_size)`. Une valeur plus élevée signifie que l'index offre un meilleur bénéfice par rapport à l'espace disque qu'il consomme
`original_cost`	Temps moyen nécessaire à l'exécution de l'instruction SQL avant l'utilisation de l'index pour accélérer la requête
`new_cost`	Temps moyen nécessaire à l'exécution de l'instruction SQL après l'utilisation de l'index pour accélérer la requête. Si plusieurs index sont recommandés pour une même requête, cette valeur reflète le coût après application de tous les index recommandés

Pour obtenir les recommandations d'une session spécifique, passez son ID de processus backend depuis la colonne backend_pid de la table index_advisory :

SELECT show_index_advisory(79370);

Utilisation de select_index_advisory

La vue select_index_advisory affiche les recommandations pour toutes les sessions de la table index_advisory, regroupées par ID de processus backend :

postgres=# SELECT * FROM select_index_advisory;
 backend_pid |                                                             show_index_advisory
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------
       79370 | create index t_a_idx on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
       79370 | create index t_b_idx on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)

Utilisez la valeur gain pour prioriser les index à créer en premier. Une valeur gain plus élevée signifie que l'index offre une réduction plus importante du coût des requêtes par rapport à l'espace disque qu'il consomme.