Trouver si toutes vos clés étrangères sont indexées
Par footcow le jeudi, 8 août 2019, 16:20 - PostgreSQL - Lien permanent
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.
