Indexation : un BRIN de folie - partie 3

(sujet mis à jour avec la version 10 beta)

Réindexer ?

      Dans cette deuxième partie consacrée aux index BRIN nous avons vu qu’ils n’étaient pas adaptés dans tous les cas. Mais, même dans les cas oû ils sont adaptés, que se passe-t-il lorsque les données indexées sont modifiées ?
      Le principe d’un index BRIN est de stocker la valeur minimale et la valeur maximale pour une colonne dans un ensemble de lignes stockées dans N pages. Si la valeur de la colonne indexée est modifiée pour une ligne plusieurs cas se présentent :

      Revenons sur le dernier point. Supposons que la valeur minimale dans une colonne de type SMALLINT pour un ensemble de pages soit 2, la valeur maximale 10. Pour une des lignes la valeur dans la colonne qui était à 10 passe à 5. La nouvelle valeur maximale n’est pas forcément devenue 5. Il peut rester des lignes dans l’ensemble de pages pour lesquelles la colonne indexée a pour valeur 6, 7, 8, 9 ou encore 10 s’il n’y a pas de contrainte d’unicité sur cette colonne.
      Il y avait donc ici un choix à faire pour les concepteurs de PostgreSQL. Fallait-il sacrifier la vitesse de mise à jour ou la pertinence de l’index ?
      Pour comprendre ce qui a été choisi nous allons reprendre la table VENTES_AGG de la première partie et lui ajouter une colonne ID de type SERIAL (valeur entière autoincrémentée). La colonne DTV sera mise à jour pour chaque ligne en diminuant sa valeur de 2 fois ID secondes. Cela n’a aucun sens fonctionnel mais cela renversera complètement l’ordre de tri de la colonne DTV par rapport à l’ordre d’insertion. Les données seront au final toujours triées selon leur ordre d’insertion initial mais de manière décroissante au lieu de croissante. La colonne DTV est donc toujours une bonne candidate même après mise à jour mais l’index BRIN sera-t-il toujours pertinent sans intervention explicite au niveau de l’index ?
      Illustration avec PostgreSQL 9.5.0 en faisant une comparaison avec le comportement d’un index B-Tree :

postgres=# \timing Chronométrage activé. postgres=# create table ventes_agg(id serial, dtv timestamp, mtv integer); CREATE TABLE Temps : 7,953 ms postgres=# insert into ventes_agg(dtv,mtv) postgres=# select generate_series(date_trunc('second', current_timestamp-interval '1 year'), date_trunc('second', current_timestamp), '1 second'), postgres=# trunc(random() * 100000 + 1); INSERT 0 31536001 Temps : 199404,485 ms postgres=# \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-------------------+----------+--------------+------------+------------- public | ventes_agg | table | postgres | 1570 MB | public | ventes_agg_id_seq | séquence | postgres | 8192 bytes | (2 lignes) postgres=# create index br_dtv on ventes_agg using brin(dtv); CREATE INDEX Temps : 14974,848 ms postgres=# explain select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=239991.64..239991.65 rows=1 width=4) -> Bitmap Heap Scan on ventes_agg (cost=12269.85..237007.77 rows=1193546 width=4) Recheck Cond: ((dtv >= to_timestamp('13/03/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2015'::text, 'DD/MM/YYYY'::text))) -> Bitmap Index Scan on br_dtv (cost=0.00..11971.47 rows=1193546 width=0) Index Cond: ((dtv >= to_timestamp('13/03/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2015'::text, 'DD/MM/YYYY'::text))) (5 lignes) Temps : 3,373 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY'); avg -------------------- 50002.089811507937 (1 ligne) Temps : 4624,098 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY'); avg -------------------- 50002.089811507937 (1 ligne) Temps : 4584,642 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY'); avg -------------------- 50002.089811507937 (1 ligne) Temps : 4494,030 ms postgres=# update ventes_agg set dtv = dtv - (2*id || ' seconds')::interval; UPDATE 31536001 Temps : 347917,222 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY'); avg -------------------- 49977.457205687831 (1 ligne) Temps : 105802,894 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY'); avg -------------------- 49977.457205687831 (1 ligne) Temps : 105332,640 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY'); avg -------------------- 49977.457205687831 (1 ligne) Temps : 106016,737 ms postgres=# reindex index br_dtv; REINDEX Temps : 12695,392 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY'); avg -------------------- 49977.457205687831 (1 ligne) Temps : 4733,356 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY'); avg -------------------- 49977.457205687831 (1 ligne) Temps : 4495,150 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY'); avg -------------------- 49977.457205687831 (1 ligne) Temps : 4312,399 ms postgres=# drop table ventes_agg; DROP TABLE Temps : 1599,909 ms postgres=# create table ventes_agg(id serial, dtv timestamp, mtv integer); CREATE TABLE Temps : 9,270 ms postgres=# insert into ventes_agg(dtv,mtv) postgres=# select generate_series(date_trunc('second', current_timestamp-interval '1 year'), date_trunc('second', current_timestamp), '1 second'), postgres=# trunc(random() * 100000 + 1); INSERT 0 31536001 Temps : 209511,529 ms postgres=# create index id_dtv on ventes_agg(dtv); CREATE INDEX Temps : 73965,872 ms postgres=# explain select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=204779.78..204779.79 rows=1 width=4) -> Bitmap Heap Scan on ventes_agg (cost=3348.79..204385.58 rows=157680 width=4) Recheck Cond: ((dtv >= to_timestamp('13/03/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2015'::text, 'DD/MM/YYYY'::text))) -> Bitmap Index Scan on id_dtv (cost=0.00..3309.37 rows=157680 width=0) Index Cond: ((dtv >= to_timestamp('13/03/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2015'::text, 'DD/MM/YYYY'::text))) (5 lignes) Temps : 3,240 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY'); avg -------------------- 49986.775383597884 (1 ligne) Temps : 779,943 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY'); avg -------------------- 49986.775383597884 (1 ligne) Temps : 828,564 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2015','DD/MM/YYYY') and dtv < to_timestamp('27/03/2015','DD/MM/YYYY'); avg -------------------- 49986.775383597884 (1 ligne) Temps : 893,620 ms postgres=# update ventes_agg set dtv = dtv - (2*id || ' seconds')::interval; UPDATE 31536001 Temps : 506365,702 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY'); avg -------------------- 49994.196351686508 (1 ligne) Temps : 865,246 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY'); avg -------------------- 49994.196351686508 (1 ligne) Temps : 820,067 ms postgres=# select avg(mtv) from ventes_agg where dtv >= to_timestamp('13/03/2014','DD/MM/YYYY') and dtv < to_timestamp('27/03/2014','DD/MM/YYYY'); avg -------------------- 49994.196351686508 (1 ligne) Temps : 769,410 ms

      Que remarque-t-on ?
      La présence de l’index BRIN a nettement moins ralenti la mise à jour que la présence de l’index B-Tree : 5min48s contre 8min26s.
      Les index B-Tree nécessitent rarement d’être reconstruits bien qu’il existe des cas très précis où une réindexation est bénéfique. Ici, lors d’une recherche portant sur une plage de 14 jours sur 365, l’index B-Tree conserve toute son efficacité sans action manuelle après la mise à jour. En revanche l’index BRIN la perd complètement. Après réindexation l’index BRIN retrouve cependant son efficacité initiale.
      La vitesse de mise à jour a donc été privilégiée par rapport à la pertinence de l’index BRIN. Il convient donc après une mise à jour importante affectant une colonne indexée par un Block Range Index de considérer une réindexation voire une suppression de l’index. En effet, dans l’exemple fourni, la colonne DTV est toujours candidate a une indexation BRIN mais une mise à jour pourrait très bien entraîner le cas présenté dans la deuxième partie. Le DBA peut donner l’alerte mais il est déjà "trop tard" si un probléme d’indexation entraînant une perte de performance est découvert en production. L’indexation nécessite une connaissance fonctionnelle et doit avoir été réfléchie au niveau du développement.

Mise à jour : 30/01/2016