Index inutilisés

      Les index sont utiles et même indispensables. Cependant, si aucune instruction n'en tire parti, un index consomme inutilement de l'espace de stockage. Cela peut entraîner des problèmes de performance pour les opérations DML (insert, delete etc.).
      Les index peuvent aussi parfois être nuisibles pour les requêtes (select). Pour vous en convaincre, vous pouvez trouver des exemples dans cette page sur les statistiques étendues ou cette page sur le stockage orienté colonne.
      Nous allons ici nous intéresser principalement aux index (quasi) inutilisés et (potentiellement) inutiles, avec Oracle Database et PostgreSQL.
      Quels points d'entrée pour effectuer une telle évaluation ? Il est par exemple possible de consulter les statistiques d'usage des index.
      Oracle Database jusqu'à la version 12.1 incluse était très limité sur cet aspect. Je vais donc utiliser Oracle 18c (en fait une 12.2.0.2 dans l'ancien système de nommage) :

Connecte a : Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 create table geants( idg integer generated by default as identity, dtn timestamp, taille smallint, devise varchar(128), dicton varchar(128), berserk number); Table creee. insert into geants(dtn, taille, devise, dicton, berserk) with serie(i) as (select 1 from dual UNION ALL select i + 1 from serie where i < 1000) select current_date - 3650 - trunc(dbms_random.value * 10000 + 1), 200 + (trunc(dbms_random.value * 200 + 1)), DBMS_RANDOM.string('x',32),DBMS_RANDOM.string('x',32), case when dbms_random.value < 0.001 then 1 else 0 end from serie; 1000 lignes creees. create table lancers ( dtl timestamp, idg integer, perf integer ); Table creee. insert into lancers(dtl, idg, perf) with serie(i) as (select 1 from dual UNION ALL select i + 1 from serie where i < 100000) select current_timestamp - i/1440 - trunc(dbms_random.value * 100 + 1), trunc(dbms_random.value * 1000 + 1), trunc(dbms_random.value * 100000 + 1) from serie; 100000 lignes creees. create unique index geants_pk on geants(idg); Index cree. alter table geants add primary key(idg); Table modifiee. create index geants_i1 on geants(dtn); Index cree. create index geants_i2 on geants(taille); Index cree. create index geants_i3 on geants(devise); Index cree. create index geants_i4 on geants(dicton); Index cree. create index geants_i5 on geants(berserk); Index cree. create index geants_i6 on geants(taille, berserk); Index cree. create index geants_i7 on geants(devise, dicton); Index cree. create index lancers_i1 on lancers (idg, perf); Index cree. create index lancers_i2 on lancers (dtl, perf); Index cree. create index lancers_i3 on lancers (perf); Index cree. alter table lancers add foreign key(idg) references geants(idg) on delete cascade; Table modifiee. SELECT owner, name, total_access_count, total_exec_count, total_rows_returned, last_used FROM dba_index_usage WHERE owner = 'TEST' ORDER BY 1, 2; aucune ligne selectionnee -- relever les stats d'usage d'index lors de chaque requete -- uniquement en phase de test ALTER SESSION SET "_iut_stat_collection_type"=ALL; -- debut charge select max(perf) from lancers where dtl >= trunc(current_date); insert into lancers values(current_timestamp, trunc(dbms_random.value * 1000 + 1), trunc(dbms_random.value * 100000 + 1)); insert into lancers values(current_timestamp, trunc(dbms_random.value * 1000 + 1), trunc(dbms_random.value * 100000 + 1)); select max(perf) from lancers where dtl >= trunc(current_date); insert into lancers values(current_timestamp, trunc(dbms_random.value * 1000 + 1), trunc(dbms_random.value * 100000 + 1)); insert into lancers values(current_timestamp, trunc(dbms_random.value * 1000 + 1), trunc(dbms_random.value * 100000 + 1)); select max(perf) from lancers where dtl >= trunc(current_date); insert into lancers values(current_timestamp, trunc(dbms_random.value * 1000 + 1), trunc(dbms_random.value * 100000 + 1)); select max(perf) from lancers where dtl >= trunc(current_date); insert into lancers values(current_timestamp, trunc(dbms_random.value * 1000 + 1), trunc(dbms_random.value * 100000 + 1)); insert into lancers values(current_timestamp, trunc(dbms_random.value * 1000 + 1), trunc(dbms_random.value * 100000 + 1)); insert into lancers values(current_timestamp, trunc(dbms_random.value * 1000 + 1), trunc(dbms_random.value * 100000 + 1)); select idg, max(perf) from lancers where dtl >= trunc(current_date) group by idg; delete from geants where idg = 131; select avg(perf), to_char(dtl, 'YYYY-MM-DD') from lancers where dtl > current_date - 31 group by to_char(dtl, 'YYYY-MM-DD'); rollback; -- fin charge -- attente du flush periodique 15 minutes -- SELECT index_stats_enabled, index_stats_collection_type, active_elem_count, last_flush_time FROM v$index_usage_info; select name, total_access_count, total_exec_count, total_rows_returned, last_used from DBA_INDEX_USAGE where owner='TEST' order by total_exec_count desc; NAME TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT TOTAL_ROWS_RETURNED LAST_USE ------------------------------ ------------------ ---------------- ------------------- -------- LANCERS_I2 6 6 6640 22/11/18

      Nous choisissons ici de positionner un paramètre caché "_iut_stat_collection_type" à ALL afin de collecter les statistiques d'usage des index à chaque requête et pas par échantillonnage. C'est plus coûteux en ressources et doit donc être évité en production mais c'est à considérer pour améliorer la qualité des résultats en phase de test.
      La table DBA_INDEX_USAGE indique que l'index lancers_i2 a été utilisé, c'était attendu au vu des requêtes incluant un filtre sur dtl.
      Il semble en revanche surprenant que les index geants_pk et lancers_i1 n'apparaissent pas dans les résultats, notamment pour traiter "insert into lancers..." et "delete from geants where idg = 131;"
      Cette anomalie provient du mode d'évaluation utilisé : tous les usages ne sont pas détectés, notamment les usages indirects permettant de vérifier les contraintes de clé étrangères.
      À l'inverse, un calcul de statistiques pour l'optimiseur va se retrouver dans les statistiques d'usage des index, exemple :

      Les lignes indiquant 1000 ou 100000 pour TOTAL_ROWS_RETURNED sont ici uniquement présentes en raison de l'appel au package dbms_stats.
      Cette vue DBA_INDEX_USAGE donne donc de bonnes indications mais conclure qu'un index est utilisé ou non par l'activité applicative en se basant uniquement sur ses données est quasi impossible.
      À présent place à PostgreSQL dans sa version 11 :

select version(); version --------------------------------------------------------------------------------------------------------------- PostgreSQL 11.1 (Debian 11.1-1.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-9) 8.2.0, 64-bit (1 ligne) create table geants( idg integer generated by default as identity, dtn timestamp, taille smallint, devise varchar(128), dicton varchar(128), berserk boolean); CREATE TABLE with recursive serie(i) as (select 1 UNION ALL select i + 1 from serie where i < 1000) insert into geants(dtn, taille, devise, dicton, berserk) select current_date - 3650 - trunc(random() * 10000 + 1)::int, 200 + (trunc(random() * 200 + 1)), upper(md5(random()::text)),upper(md5(random()::text)), case when random() < 0.001 then true else false end from serie; INSERT 0 1000 create table lancers ( dtl timestamp, idg integer, perf integer ); CREATE TABLE insert into lancers(dtl, idg, perf) with recursive serie(i) as (select 1 UNION ALL select i + 1 from serie where i < 100000) select current_timestamp - ((i/1440)::int || ' minutes')::interval - (trunc(random() * 100 + 1)::int || ' days')::interval, trunc(random() * 1000 + 1), trunc(random() * 100000 + 1) from serie; INSERT 0 100000 create unique index geants_pk on geants(idg); CREATE INDEX alter table geants add primary key using index geants_pk; ALTER TABLE create index geants_i1 on geants(dtn); CREATE INDEX create index geants_i2 on geants(taille); CREATE INDEX create index geants_i3 on geants(devise); CREATE INDEX create index geants_i4 on geants(dicton); CREATE INDEX create index geants_i5 on geants(berserk); CREATE INDEX create index geants_i6 on geants(taille, berserk); CREATE INDEX create index geants_i7 on geants(devise, dicton); CREATE INDEX create index lancers_i1 on lancers (idg, perf); CREATE INDEX create index lancers_i2 on lancers (dtl, perf); CREATE INDEX create index lancers_i3 on lancers (perf); CREATE INDEX alter table lancers add foreign key(idg) references geants(idg) on delete cascade; ALTER TABLE select pg_stat_reset(); pg_stat_reset --------------- (1 ligne) select * from pg_stat_all_indexes where schemaname = 'postgres'; relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+---------+--------------+----------+--------------+--------------- 18534 | 18548 | postgres | geants | geants_i7 | 0 | 0 | 0 18534 | 18547 | postgres | geants | geants_i6 | 0 | 0 | 0 18534 | 18546 | postgres | geants | geants_i5 | 0 | 0 | 0 18534 | 18545 | postgres | geants | geants_i4 | 0 | 0 | 0 18534 | 18544 | postgres | geants | geants_i3 | 0 | 0 | 0 18534 | 18543 | postgres | geants | geants_i2 | 0 | 0 | 0 18534 | 18542 | postgres | geants | geants_i1 | 0 | 0 | 0 18534 | 18540 | postgres | geants | geants_pk | 0 | 0 | 0 18537 | 18551 | postgres | lancers | lancers_i3 | 0 | 0 | 0 18537 | 18550 | postgres | lancers | lancers_i2 | 0 | 0 | 0 18537 | 18549 | postgres | lancers | lancers_i1 | 0 | 0 | 0 (11 lignes) vacuum analyze lancers; VACUUM vacuum analyze geants; VACUUM -- debut charge start transaction; select max(perf) from lancers where dtl >= current_date; insert into lancers values(current_timestamp, trunc(random() * 1000 + 1), trunc(random() * 100000 + 1)); insert into lancers values(current_timestamp, trunc(random() * 1000 + 1), trunc(random() * 100000 + 1)); select max(perf) from lancers where dtl >= current_date; insert into lancers values(current_timestamp, trunc(random() * 1000 + 1), trunc(random() * 100000 + 1)); insert into lancers values(current_timestamp, trunc(random() * 1000 + 1), trunc(random() * 100000 + 1)); select max(perf) from lancers where dtl >= current_date; insert into lancers values(current_timestamp, trunc(random() * 1000 + 1), trunc(random() * 100000 + 1)); select max(perf) from lancers where dtl >= current_date; insert into lancers values(current_timestamp, trunc(random() * 1000 + 1), trunc(random() * 100000 + 1)); insert into lancers values(current_timestamp, trunc(random() * 1000 + 1), trunc(random() * 100000 + 1)); insert into lancers values(current_timestamp, trunc(random() * 1000 + 1), trunc(random() * 100000 + 1)); select idg, max(perf) from lancers where dtl >= current_date group by idg; delete from geants where idg = 131; select avg(perf), to_char(dtl, 'YYYY-MM-DD') from lancers where dtl > current_date - 31 group by to_char(dtl, 'YYYY-MM-DD'); rollback; -- fin charge select * from pg_stat_all_indexes where schemaname = 'postgres'; relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+---------+--------------+----------+--------------+--------------- 18534 | 18548 | postgres | geants | geants_i7 | 0 | 0 | 0 18534 | 18547 | postgres | geants | geants_i6 | 0 | 0 | 0 18534 | 18546 | postgres | geants | geants_i5 | 0 | 0 | 0 18534 | 18545 | postgres | geants | geants_i4 | 0 | 0 | 0 18534 | 18544 | postgres | geants | geants_i3 | 0 | 0 | 0 18534 | 18543 | postgres | geants | geants_i2 | 0 | 0 | 0 18534 | 18542 | postgres | geants | geants_i1 | 0 | 0 | 0 18534 | 18540 | postgres | geants | geants_pk | 9 | 9 | 9 18537 | 18576 | postgres | lancers | lancers_i3 | 0 | 0 | 0 18537 | 18550 | postgres | lancers | lancers_i2 | 15 | 31161 | 4909 18537 | 18549 | postgres | lancers | lancers_i1 | 1 | 92 | 0 (11 lignes)

Nous retrouvons bien tous les index attendus : geants_pk, lancers_i1 et lancers_i2. Les usages indirects sont donc bien relevés, au contraire de ce que nous avons observé avec Oracle.

Conclusion

      Les vues permettant de consulter les statistiques d'usage des index fournissent des informations très intéressantes. Elles doivent cependant être utilisées avec précaution si le but est d'identifier les index inutiles.
      Avec Oracle Database, un index peut être utilisé sans que son usage ne soit remonté au niveau de la vue dba_index_usage. A l'inverse, un index inutile peut apparaître dans les statistiques d'usage en raison de la collecte d'informations nécessaires à l'optimiseur.
      La vue pg_stat_all_indexes de PostgreSQL apparaît plus pertinente mais conclure qu'un index est inutile n'est pas trivial malgré tout. Bien sûr, il est facile de déterminer qu'un index n'est pas utilisé dans une base de test après avoir lancé une charge comportant quelques requêtes. C'est nettement moins évident avec une base de production réelle.
      La période d'évaluation doit être suffisante pour finalement envisager de détruire un index. Il faut tenir compte de manière exhaustive de toutes les activités de la base, l'activité transactionnelle mais aussi l'activité de type batch (certains batches peuvent être lancés de manière exceptionnelle). Attention également aux bases hybrides comportant activité transactionnelle et décisionnelle.
      Au vu de ces difficultés, il est préférable de réfléchir AVANT de créer un index pour un usage particulier. Sera-t-il utilisé ? Un index existant pourrait-il être modifié pour ce nouvel usage ? Quelle méthode d'indexation utiliser ? Les autres pages consacrées aux index vous donneront des pistes.

Indexation inutile

Mise à jour : 22/11/2018