Détecter les index redondants dans une base
Voici un petit cas d'école d'utilisation de Window Function… vu que c'est rare dans le travail de DBA d'en rencontrer qui nous servent à nous.
Intro: Pourquoi un si long article juste pour nous présenter une requête ?
Il y a quelques jours, je me suis retrouvé dans un environnement contenant beaucoup d'index inutiles, redondants, etc… Habituellement, pour dégrossir le terrain, on peut aller faire un tour du côte dé pg_stat_user_indexes, et se débarrasser des index non utilisés, s'ils ne servent à rien d'autre (contrainte d'unicité principalement).
Le problème est qu'il arrive quelquefois que les index soient utilisés tout de même. L'exemple classique est le suivant (pour ceux qui connaissent bien cette problématique, n'hésitez pas à sauter le paragraphe :) ):
test=> CREATE TABLE test (a int, b int, c int); CREATE TABLE test=> CREATE INDEX tst1 on test (b); CREATE INDEX test=> CREATE INDEX tst2 on test (c); CREATE INDEX test=> CREATE INDEX tst3 on test (b,c); CREATE INDEX
(On imagine que j'ai mis des données dedans, je suis trop paresseux pour le faire… ).
Si on fait
select * from test where b=1
on va passer par tst1.
De même, si la clause «where» est sur c uniquement, on va passer par tst2, et si on a une requête sur b et c à la fois, on utilisera tst3, puisque le moteur prendra à chaque fois l'index le plus efficace. À chaque fois, c'est le plus efficace, si on considère uniquement la requête en cours.
Mais l'index tst3 est tout à fait capable de répondre aux requêtes sur b uniquement. Il est même extrêmement proche en performances de tst2 (moins de 1% de perte, le plus souvent). Et si on ne gardait que tst3, on aurait tst2 de moins dans le cache. Donc plus de chance d'avoir tst3 dans le cache. Donc probablement de meilleures performances globales, sauf si bien sûr tout tenait déjà dans le cache.
Donc ce qui serait intéressant, c'est de détecter que tst2 est «compris» dans tst3.
Étape 1: la table système
Construisons la requête par morceau. Pour commencer, l'oid de ma table «test», c'est 16406. On a des informations sur ses index par la table système pg_index:
test=> SELECT indexrelid, indrelid, indkey from pg_index where indrelid=16406;
indexrelid | indrelid | indkey
------------+----------+--------
16409 | 16406 | 2
16410 | 16406 | 3
16411 | 16406 | 2 3
indkey est un tableau d'entiers (la liste des colonnes…).
Ce qu'on cherche, c'est, pour un indrelid donné (l'identifiant de la table), trouver les index pour lesquelles le tableau indkey est le début de celui d'un autre index.
Comme je suis paresseux, et que je préfère comparer des chaînes de caractère, commençons donc par transformer indkey en chaîne, avec array_to_string:
SELECT indexrelid, indrelid, array_to_string(indkey,'+') FROM pg_index WHERE indrelid=16406 ORDER BY indrelid, array_to_string(indkey,'+') DESC;
indexrelid | indrelid | array_to_string ------------+----------+----------------- 16410 | 16406 | 3 16411 | 16406 | 2+3 16409 | 16406 | 2 (4 lignes)
Tant qu'on y est, on voit qu'on peut trier sur cette chaîne. Si on lit la liste, on trouve très rapidement que 16411 contient 16409, qui est juste après lui.
Etape 2: la «Window Function»
C'est ici qu'arrive la Window Function : on veut comparer deux enregistrements consécutifs, ce qui n'est pas possible avec des opérateurs relationnels classiques (à moins de créer 2 fois la table, y numéroter les enregistrements, et faire une jointure sur ces numéros, belle galère en perspective) :
SELECT indexrelid, indrelid, array_to_string(indkey,'+') AS colindex, lag(array_to_string(indkey,'+')) OVER window_recherche AS colindexprecedent, lag(indexrelid) OVER window_recherche AS index_precedent FROM pg_index WHERE indrelid=16406 WINDOW window_recherche AS (PARTITION BY indrelid ORDER BY array_to_string(indkey,'+') DESC);
indexrelid | indrelid | colindex | colindexprecedent | index_precedent ------------+----------+----------+-------------------+----------------- 16410 | 16406 | 3 | | 16411 | 16406 | 2+3 | 3 | 16410 16409 | 16406 | 2 | 2+3 | 16411 (4 lignes)
On définit une fenêtre partitionnée par indrelid, c'est à dire que la fonction «Window» ne travaillera que sur les enregistrements de même indrelid. Elle sera remise à zéro à chaque changement d'indrelid.
On trie dans cette fenêtre par array_to_string(indkey,'+') DESC, comme précédemment.
Dans cette fenêtre, avec ce tri, on utilise la fonction lag, qui retourne pour chaque enregistrement de la fenêtre une fonction de l'enregistrement précédent.
Etape 3: Utiliser le résultat de la Window Function
On peut donc extraire 2 colonnes colindex et colindexprecedent, qu'il suffit de comparer comme deux chaînes pour trouver si les index sont redondants. Ils le sont si colindex est le début de colindexprecedent.
Pour plus de lisibilité, on place notre requête dans une sous-requête, histoire de pouvoir utiliser colindex et colindexprecedent plutôt que les fonctions lag dans la clause WHERE.
SELECT indexrelid,index_precedent FROM ( SELECT indexrelid, indrelid, array_to_string(indkey,'+') AS colindex, lag(array_to_string(indkey,'+')) OVER window_recherche AS colindexprecedent, lag(indexrelid) OVER window_recherche AS index_precedent FROM pg_index WHERE indrelid=16406 WINDOW window_recherche AS (PARTITION BY indrelid ORDER BY array_to_string(indkey,'+') DESC) ) AS tmp WHERE colindexprecedent like (colindex || '+%');
indexrelid | index_precedent ------------+----------------- 16409 | 16411
Étape 4, où l'on apprend que l'auteur aime les cerises sur les gâteaux
Cerise sur le gâteau donc, on va rendre notre résultat utilisable. Et arrêter de filtrer sur notre seule table, on veut que ça travaille sur toute la base…
SELECT pg_get_indexdef(indexrelid) as contenu, pg_get_indexdef(index_precedent) as contenant FROM ( SELECT indexrelid, indrelid, array_to_string(indkey,'+') AS colindex, lag(array_to_string(indkey,'+')) OVER window_recherche AS colindexprecedent, lag(indexrelid) OVER window_recherche AS index_precedent FROM pg_index WINDOW window_recherche AS (PARTITION BY indrelid ORDER BY array_to_string(indkey,'+') DESC) ) AS tmp WHERE colindexprecedent like (colindex || '+%');
contenu | contenant -------------------------------------------+---------------------------------------------- CREATE INDEX tst1 ON test USING btree (b) | CREATE INDEX tst3 ON test USING btree (b, c) (1 ligne)
Disclaimer
Attention tout de même à une chose: on ne distingue pas ici les index 'normaux' des index liés à des contraintes, comme les clés primaires. Ils vous sont tous présentés, avec la syntaxe CREATE INDEX, même si ils proviennent d'une contrainte. Ça veut donc dire qu'il reste un peu de travail à faire après avoir exécuté cette requête et récupéré son résultat…