Comment extraire les informations des colonnes indexées dans PostgreSQL ?

Extraire des informations sur les colonnes indexées dans PostgreSQL

Dans PostgreSQL, obtenir les colonnes incluses dans un index est une tâche précieuse pour l'analyse de la base de données et l'optimisation des performances . Contrairement à la commande SHOW INDEXES FOR de MySQL, PostgreSQL ne dispose pas d'un moyen simple pour récupérer ces informations. Cependant, en utilisant des tables système et des fonctions d'agrégation, il est possible d'obtenir des résultats similaires.

Récupération de colonnes et d'index indexés

La requête suivante exploite pg_class, pg_index, et les tables système pg_attribute pour recueillir des détails sur les index et leurs colonnes associées :

SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    a.attname AS column_name
FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
WHERE
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND t.relname LIKE 'table_name_pattern';

Cette requête récupère le nom de la table, le nom de l'index et les noms de colonnes de tous les index d'une table spécifiée ou d'un groupe de tables.

Regroupement des colonnes indexées

Pour obtenir une liste consolidée des colonnes indexées par chaque index, la requête peut être modifiée pour regrouper les résultats et agréger les noms de colonnes à l'aide de la fonction array_to_string() :

SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    array_to_string(array_agg(a.attname), ', ') AS column_names
FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
WHERE
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND t.relname LIKE 'table_name_pattern'
GROUP BY
    t.relname,
    i.relname;

Cette requête fournira une sortie tabulaire affichant le nom de la table, le nom de l'index et une liste de colonnes séparées par des virgules incluses dans chaque index. Ces informations peuvent être précieuses pour comprendre la stratégie d'indexation et optimiser les performances de la base de données.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!