Linux, Base de données Postgresql, développement, Internet, emailing et déliverabilité.

Aller au contenu | Aller au menu | Aller à la recherche

Trouver les si toutes vos clés étrangères sont indexées

PostgreSQL comme toutes les bases de données modernes, permet de lier une table à une autre pour valider son intégrité. Ainsi vous n'aurez pas une table qui s'appuie sur les données d'une autre table sans cohérence. Vous ne pourrez donc écrire dans le champ utilisé en référence d'une autre table que des données qui existent bien dans la table de référence. Cette possibilité est pratique, rassurante et surtout preuve d'une conception cohérente du modèle de données. Mais, elle n'est pas sans impacte sur la vitesse de la base de données si les bons index ne sont pas placés sur ces champs. PostgreSQL vous laisse cette liberté. Cela peut donc engendrer parfois des surprises, en particulier quand on efface des données de la table de référence. L'absence d'index va engendrer un ralentissement considérable au moment du DELETE et donc vous pénaliser.

J'ai donc trouvé une méthode qui permet de scanner toutes les tables et d'analyser tous les index manquants sur les clés étrangères de la base données. Le résultat est classé par la taille des tables et donc les plus importantes à traiter en urgence :

--Trouver les index manquants
SELECT c.conrelid::regclass AS table,
       /* liste des colonnes */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.conrelid)) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table
  FROM pg_catalog.pg_constraint c
       /* liste des clés par clés étrangères */
 CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
       /* nom de chaque clé */
  JOIN pg_catalog.pg_attribute a ON (a.attnum = x.attnum AND a.attrelid = c.conrelid) /* y-a-t-il un index qui correspond ? */
 WHERE NOT EXISTS (SELECT 1
                     FROM pg_catalog.pg_index i
                    WHERE i.indrelid = c.conrelid
                      AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1] @> c.conkey)
   AND c.contype = 'f'
 GROUP BY c.conrelid, c.conname, c.confrelid
 ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;

Note: Vous n'êtes pas obligé de mettre un index quand la table de référence est trop petite, car l'index ne serait pas utilisé.

Merci à Laurenz Albe pour son aide précieuse sur ce sujet.

Partager

  • Favories


Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

La discussion continue ailleurs

URL de rétrolien : http://www.footcow.com/index.php/trackback/111

Fil des commentaires de ce billet