Indexation : un BRIN de folie - partie 2

(sujet mis à jour avec la version 10 beta)

Il ne faut pas TOUT indexer

      Dans cette première partie consacrée aux index BRIN nous avons vu qu’ils pouvaient être très efficaces. Rapides à créer, peu volumineux...faut-il pour autant en créer partout en se disant qu’au pire ça ne fera pas de mal ?
      Dans ce nouvel exemple nous allons considérer une table POPULATION contenant les noms et masses de 20 millions de Français. Apparemment plutôt des gens du Nord car la table contient environ 10% de "LEFEVRE". Les noms de famille ne sont pas du tout triés selon l’ordre d’insertion donc la situation est a priori très défavorable pour les index BRIN.
      Nous allons déterminer la masse moyenne des "LEFEVRE" et des "24D8C7EA0335467C90143834F2826884", un nom beaucoup moins répandu, en comparant les performances des requêtes tirant parti d’un index classique B-Tree et d’un index BRIN avec PostgreSQL 9.5.0 :

postgres=# \timing Chronométrage activé. postgres=# create table population(id serial, nom_marital text, masse smallint); CREATE TABLE Temps : 618,151 ms postgres=# do postgres-# $$ postgres$# begin postgres$# for i in 1..20000000 loop postgres$# insert into population(nom_marital, masse) values (CASE WHEN random() < 0.1 THEN 'LEFEVRE' ELSE upper(md5(random()::text)) END, trunc(random() * 50 + 50)); postgres$# end loop; postgres$# end$$; DO Temps : 725204,726 ms postgres=# select count(*) from population where nom_marital='LEFEVRE'; count --------- 2000693 (1 ligne) Temps : 5848,715 ms postgres=# \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-------------------+----------+--------------+------------+------------- public | population | table | postgres | 1260 MB | public | population_id_seq | séquence | postgres | 8192 bytes | (2 lignes) postgres=# explain select avg(masse) from population where nom_marital='LEFEVRE'; QUERY PLAN --------------------------------------------------------------------------- Aggregate (cost=416184.81..416184.82 rows=1 width=2) -> Seq Scan on population (cost=0.00..411179.85 rows=2001983 width=2) Filter: (nom_marital = 'LEFEVRE'::text) (3 lignes) Temps : 2,668 ms postgres=# explain select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; QUERY PLAN -------------------------------------------------------------------------- Aggregate (cost=411180.02..411180.03 rows=1 width=2) -> Seq Scan on population (cost=0.00..411179.85 rows=68 width=2) Filter: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text) (3 lignes) Temps : 1,950 ms postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4934520188754597 (1 ligne) Temps : 6451,122 ms postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4934520188754597 (1 ligne) Temps : 5186,698 ms postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4934520188754597 (1 ligne) Temps : 5166,795 ms postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 4774,511 ms postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 5018,838 ms postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 4937,004 ms postgres=# create index br_nmr on population using brin(nom_marital); CREATE INDEX Temps : 23754,541 ms postgres=# \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+--------+-------+--------------+------------+--------+------------- public | br_nmr | index | postgres | population | 120 kB | (1 ligne) postgres=# explain select avg(masse) from population where nom_marital='LEFEVRE'; QUERY PLAN --------------------------------------------------------------------------------------- Aggregate (cost=206775.50..206775.51 rows=1 width=2) -> Bitmap Heap Scan on population (cost=15563.50..201770.50 rows=2002000 width=2) Recheck Cond: (nom_marital = 'LEFEVRE'::text) -> Bitmap Index Scan on br_nmr (cost=0.00..15063.00 rows=2002000 width=0) Index Cond: (nom_marital = 'LEFEVRE'::text) (5 lignes) Temps : 2,637 ms postgres=# explain select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; QUERY PLAN ------------------------------------------------------------------------------------ Aggregate (cost=317.36..317.37 rows=1 width=2) -> Bitmap Heap Scan on population (cost=48.52..317.18 rows=68 width=2) Recheck Cond: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text) -> Bitmap Index Scan on br_nmr (cost=0.00..48.51 rows=68 width=0) Index Cond: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text) (5 lignes) Temps : 2,049 ms postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4934520188754597 (1 ligne) Temps : 6012,466 ms postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4934520188754597 (1 ligne) Temps : 5818,870 ms postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4934520188754597 (1 ligne) Temps : 6259,581 ms postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 6310,417 ms postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 5543,842 ms postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 5384,701 ms postgres=# drop index br_nmr; DROP INDEX Temps : 5,533 ms postgres=# create index bt_nmr on population(nom_marital); CREATE INDEX Temps : 448376,387 ms postgres=# \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+--------+-------+--------------+------------+---------+------------- public | bt_nmr | index | postgres | population | 1057 MB | (1 ligne) postgres=# explain select avg(masse) from population where nom_marital='LEFEVRE'; QUERY PLAN --------------------------------------------------------------------------------------- Aggregate (cost=260880.07..260880.08 rows=1 width=2) -> Bitmap Heap Scan on population (cost=69668.06..255875.06 rows=2002000 width=2) Recheck Cond: (nom_marital = 'LEFEVRE'::text) -> Bitmap Index Scan on bt_nmr (cost=0.00..69167.56 rows=2002000 width=0) Index Cond: (nom_marital = 'LEFEVRE'::text) (5 lignes) Temps : 9,444 ms postgres=# explain select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; QUERY PLAN ------------------------------------------------------------------------------------ Aggregate (cost=273.92..273.93 rows=1 width=2) -> Bitmap Heap Scan on population (cost=5.09..273.75 rows=68 width=2) Recheck Cond: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text) -> Bitmap Index Scan on bt_nmr (cost=0.00..5.07 rows=68 width=0) Index Cond: (nom_marital = '24D8C7EA0335467C90143834F2826884'::text) (5 lignes) Temps : 2,286 ms postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4934520188754597 (1 ligne) Temps : 5631,951 ms postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4934520188754597 (1 ligne) Temps : 6179,884 ms postgres=# select avg(masse) from population where nom_marital='LEFEVRE'; avg --------------------- 74.4934520188754597 (1 ligne) Temps : 6021,277 ms postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 2,192 ms postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 1,902 ms postgres=# select avg(masse) from population where nom_marital='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 2,006 ms

      Que remarque-t-on ?
      Lors de la recherche de la masse moyenne des 10% de LEFEVRE, l’utilisation des index B-Tree et BRIN n’apporte rien.
      Ensuite, la recherche de la masse moyenne des 24D8C7EA0335467C90143834F2826884 se révèle TRÈS efficace via le B-Tree alors qu’encore une fois l’index BRIN n’apporte rien.
      Le résultat est conforme à ce qui était attendu. Pour ces données non triées selon leur ordre d’insertion les index BRIN sont inutiles voire légèrement nuisibles dans les exemples fournis. Il convient donc de vraiment respecter les préconisations de la documentation PostgreSQL avant d’ajouter un index BRIN. Mais il faut de toute façon TOUJOURS réfléchir quel que soit le type d’index ! Que se passe-t-il par exemple en cas de modification des données de la colonne indexée par un index BRIN ?

Mise à jour : 28/01/2016