Indexation partielle

Des tonnes de données d’index inutiles ?

      Nous avons vu dans cet article sur la corrélation que l’intérêt de passer par un index B-Tree avant d’accéder à la table n’était pas toujours évident lorsque le filtrage rejette 90% des lignes . Mais il y a des situations plus simples. Si, pour une valeur donnée, le filtrage n’élimine qu’une infime minorité des lignes alors cette valeur ne doit souvent pas être indexée du tout. Gain d’espace, gain de temps au niveau DML, PostgreSQL propose les index partiels pour répondre à cette situation.
      Dans cet article nous allons considérer une table tickets. Elle comprend plusieurs colonnes mais celle qui nous intéresse est la colonne etat de type char(1). etat vaut ’F’ pour la plupart des lignes. Pour le reste des lignes etat est nulle ou vaut ’A’. Nous allons indexer la colonne etat pour toutes les valeurs puis uniquement pour les valeurs différentes de ’F’ :

select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-10) 6.3.0 20170321, 64-bit (1 ligne) create table tickets( ticket_id serial primary key, dto timestamp, dtp timestamp, dtf timestamp, etat char(1), severite smallint, inter_id smallint, titre character varying(128), description character varying); CREATE TABLE WITH serie(i, r) AS (SELECT generate_series(1000000,1,-1)::real, random()) insert into tickets(dto, dtp, dtf, etat, severite, inter_id, titre, description) select (current_timestamp - (ceil(i/3) || ' minutes')::interval - (trunc(random() * 100 + 1) || ' minutes')::interval), (case when r >= 0.001 and r <= 0.002 then NULL else current_timestamp - (ceil(i/3) || ' minutes')::interval end), (case when r <= 0.002 then NULL else current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 360 + 1) || ' minutes')::interval end), (case when r <= 0.001 then NULL when r <= 0.002 then 'A' else 'F' end), (case when r < 0.1 then 1 when r < 0.8 then 2 else 3 end), (trunc(random() * 1000 + 1)), (md5(random()::text)), (md5(random()::text)) from serie; INSERT 0 1000000 \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-----------------------+----------+--------------+------------+------------- public | tickets | table | postgres | 128 MB | public | tickets_ticket_id_seq | séquence | postgres | 8192 bytes | (2 lignes) select avg(current_timestamp - dto) from tickets where etat is null; avg -------------------------- 114 days 29:13:52.026701 (1 ligne) Temps : 178,969 ms select count(*) from tickets where etat = 'A'; count ------- 950 (1 ligne) Temps : 145,508 ms with ts_les_tickets(nb) as (select count(*) from tickets), tickets_ouverts(nb) as (select count(*) from tickets where etat = 'A' or etat is null) select ts_les_tickets.nb - tickets_ouverts.nb from ts_les_tickets, tickets_ouverts; ?column? ---------- 998040 (1 ligne) Temps : 214,215 ms select count(*) from tickets where etat = 'F'; count -------- 998040 (1 ligne) Temps : 162,483 ms create index tickets_etat_i1 on tickets(etat); CREATE INDEX Temps : 954,655 ms postgres=# \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+-----------------+-------+--------------+---------+--------+------------- public | tickets_etat_i1 | index | postgres | tickets | 21 MB | public | tickets_pkey | index | postgres | tickets | 21 MB | (2 lignes) select avg(current_timestamp - dto) from tickets where etat is null; avg -------------------------- 114 days 29:13:52.026701 (1 ligne) Temps : 1,906 ms select count(*) from tickets where etat = 'A'; count ------- 950 (1 ligne) Temps : 3,027 ms with ts_les_tickets(nb) as (select count(*) from tickets), tickets_ouverts(nb) as (select count(*) from tickets where etat = 'A' or etat is null) select ts_les_tickets.nb - tickets_ouverts.nb from ts_les_tickets, tickets_ouverts; ?column? ---------- 998040 (1 ligne) Temps : 94,985 ms select count(*) from tickets where etat = 'F'; count -------- 998040 (1 ligne) Temps : 188,218 ms drop index tickets_etat_i1; DROP INDEX Temps : 292,469 ms create index tickets_etat_i2 on tickets(etat) where etat = 'A ' or etat is null; CREATE INDEX Temps : 178,385 ms \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+-----------------+-------+--------------+---------+--------+------------- public | tickets_etat_i2 | index | postgres | tickets | 64 kB | public | tickets_pkey | index | postgres | tickets | 21 MB | (2 lignes) select avg(current_timestamp - dto) from tickets where etat is null; avg -------------------------- 114 days 29:13:52.026701 (1 ligne) Temps : 1,532 ms select count(*) from tickets where etat = 'A'; count ------- 950 (1 ligne) Temps : 1,103 ms with ts_les_tickets(nb) as (select count(*) from tickets), tickets_ouverts(nb) as (select count(*) from tickets where etat = 'A' or etat is null) select ts_les_tickets.nb - tickets_ouverts.nb from ts_les_tickets, tickets_ouverts; ?column? ---------- 998040 (1 ligne) Temps : 89,632 ms select count(*) from tickets where etat = 'F'; count -------- 998040 (1 ligne) Temps : 159,928 ms


      Pour l’ensemble de nos requêtes le minuscule index partiel de 64ko a logiquement fait au moins aussi bien que l’index de 21mo. La syntaxe est directe, en ajoutant "where etat = ’A ’ or etat is null", nous indiquons simplement ce que nous voulons réellement indexer.

      Si vous devez maintenir une double compatibilité avec Oracle Database, bienvenue en enfer sur le sujet. Vous trouverez quelque chose avec les mot clés PARTIAL INDEX dans la documentation Oracle car les index partiels existent à partir de la 12.1 mais ils sont liés à l’option de partitionnement. Ils sont donc indisponibles en Standard Edition ou Enterprise Edition sans option et sont de toute façon moins généraux et moins souples à utiliser.
      En dehors du partitionnement, ce SGBD permet de faire de l’indexation partielle grâce à ce qu’il faut bien appeler une bidouille. Les valeurs nulles ne sont pas prises en compte par défaut dans les index basiques donc nous allons créer une fonction renvoyant NULL pour les valeurs que nous ne voulons PAS indexer. Attention : la situation est particulièrement corsée car la colonne contient des valeurs nulles...que nous voulons elles indexer ! Pas simple mais cela nous permettra ensuite de créer un function-based index. Nous modifierons les requêtes pour en tirer parti. Démonstration avec Oracle 11.2.0.2 :

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production create table tickets nologging as with serie(tid, i, r) as ( select rownum tid, 1000001 - rownum i, dbms_random.value() r from dual connect by level <= 1000000 ) select tid ticket_id, (current_timestamp - (ceil(i/3))/1440 - (trunc(dbms_random.value() * 100 + 1))/1440) dto, (case when r >= 0.001 and r <= 0.002 then NULL else current_timestamp - (ceil(i/3))/1440 end) dtp, (case when r <= 0.002 then NULL else current_timestamp - (ceil(i/3))/1440 + (trunc(dbms_random.value() * 360 + 1))/1440 end) dtf, (case when r <= 0.001 then NULL when r <= 0.002 then 'A' else 'F' end) etat, (case when r < 0.1 then 1 when r < 0.8 then 2 else 3 end) severite, (trunc(dbms_random.value() * 1000 + 1)) inter_id, (dbms_random.string('L', 32)) titre, (dbms_random.string('L', 32)) description from serie; Table created. alter table tickets add primary key(ticket_id); Table altered. desc tickets; Name Null? Type ----------------------------------------- -------- ---------------------------- TICKET_ID NOT NULL NUMBER DTO DATE DTP DATE DTF DATE ETAT CHAR(1) SEVERITE NUMBER INTER_ID NUMBER TITRE VARCHAR2(4000) DESCRIPTION VARCHAR2(4000) select bytes/1024/1024 from user_segments where segment_name = 'TICKETS'; BYTES/1024/1024 --------------- 120 CREATE OR REPLACE FUNCTION ouverts(etat CHAR) RETURN CHAR DETERMINISTIC AS BEGIN IF etat = 'F' THEN RETURN null; ELSIF etat is null THEN RETURN 'O'; ELSE RETURN 'A'; END IF; END; / Function created. create index tickets_etat_i2 on tickets(ouverts(etat)); Index created. select bytes/1024/1024 from user_segments where segment_name = 'TICKETS_ETAT_I2'; BYTES/1024/1024 --------------- ,0625 CREATE OR REPLACE FUNCTION interval_to_sec ( i IN INTERVAL DAY TO SECOND ) RETURN NUMBER IS numSecs NUMBER; BEGIN numSecs := ((extract(day from i) * 24 + extract(hour from i) )*60 + extract(minute from i) )*60 + extract(second from i); RETURN numSecs; END; / Function created. explain plan for select avg(interval_to_sec(current_timestamp - dto)) from tickets where ouverts(etat) = 'O'; Explained. select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- Plan hash value: 4141308402 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 8 | 994 (0)| 00:00:12 | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TICKETS | 1022 | 8176 | 994 (0)| 00:00:12 | |* 3 | INDEX RANGE SCAN | TICKETS_ETAT_I2 | 1022 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ESSAI"."OUVERTS"("ETAT")='O') 15 rows selected. explain plan for select count(*) from tickets where ouverts(etat) = 'A'; Explained. select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------- Plan hash value: 1213017162 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 1 | | | |* 2 | INDEX RANGE SCAN| TICKETS_ETAT_I2 | 1022 | 1022 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------- --------------------------------------------------- 2 - access("ESSAI"."OUVERTS"("ETAT")='A') 14 rows selected. explain plan for with ts_les_tickets(nb) as (select count(*) from tickets), tickets_ouverts(nb) as (select count(*) from tickets where ouverts(etat) in ('A','O')) select ts_les_tickets.nb - tickets_ouverts.nb from ts_les_tickets, tickets_ouverts; Explained. select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- Plan hash value: 3910676987 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 574 (1)| 00:00:07 | | 1 | NESTED LOOPS | | 1 | 26 | 574 (1)| 00:00:07 | | 2 | VIEW | | 1 | 13 | 571 (1)| 00:00:07 | | 3 | SORT AGGREGATE | | 1 | | | | | 4 | INDEX FAST FULL SCAN| SYS_C007277 | 1000K| | 571 (1)| 00:00:07 | | 5 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 | | 6 | SORT AGGREGATE | | 1 | 1 | | | | 7 | INLIST ITERATOR | | | | | | |* 8 | INDEX RANGE SCAN | TICKETS_ETAT_I2 | 2043 | 2043 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("ESSAI"."OUVERTS"("ETAT")='A' OR "ESSAI"."OUVERTS"("ETAT")='O') 20 rows selected. explain plan for select count(*) from tickets where etat = 'F'; Explained. select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- Plan hash value: 2870398729 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 1 | 4165 (1)| 00:00:50 | | 1 | SORT AGGREGATE | | 1 | 1 | | | |* 2 | TABLE ACCESS FULL| TICKETS | 499K| 487K| 4165 (1)| 00:00:50 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- --------------------------------------------------- 2 - filter("ETAT"='F') 14 rows selected. explain plan for select count(*) from tickets where ouverts(etat) is null; Explained. select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- Plan hash value: 2870398729 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 1 | 4242 (3)| 00:00:51 | | 1 | SORT AGGREGATE | | 1 | 1 | | | |* 2 | TABLE ACCESS FULL| TICKETS | 997K| 974K| 4242 (3)| 00:00:51 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- --------------------------------------------------- 2 - filter("ESSAI"."OUVERTS"("ETAT") IS NULL) 14 rows selected.

Conclusion

      Avec Oracle Database, l’indexation partielle pose un problème similaire à celui que posait le partitionnement dans PostgreSQL avant les avancées majeures de la version 10. En théorie, la fonctionnalité est disponible. Cependant, en pratique, elle est très peu utilisée. Elle oblige en effet à produire du code PL/SQL et même à modifier le code SQL des requêtes. La mise en place est donc peu intuitive et la maintenance problématique.
      Avec PostgreSQL, l’indexation partielle est disponible depuis la version 7.2 sortie en 2001. Elle est simple à mettre en place, ne nécessite aucune adaptation du code SQL et se révèle très efficace à l’usage.

Mise à jour : 02/04/2017