Indexation : un BRIN de folie - partie 2

(sujet préalablement traité avec la version 9.5)

Jean des Pois Verts ne peut pas tout faire

      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 GEANTS contenant les devises et masses de 20 millions de géants. Environ 10% des géants n'ont pas une devise perssonnelle mais la devise traditionnelle "MEMOIAJOTUNHJERETUNETTIVETIV". Les devises ne sont pas du tout triées 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 géants ayant pour devise "MEMOIAJOTUNHJERETUNETTIVETIV" et des géants ayant pour devise "24D8C7EA0335467C90143834F2826884", une devise beaucoup moins répandue. A cette occasion, nous allons comparer les performances des requêtes tirant parti d’un index classique B-Tree et d’un index BRIN avec PostgreSQL 10 :

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit (1 ligne) create unlogged table geants(idg integer generated always as identity, devise character varying, masse smallint); CREATE TABLE insert into geants(devise, masse) select CASE WHEN random() < 0.1 THEN 'MEMOIAJOTUNHJERETUNETTIVETIV' ELSE upper(md5(random()::text)) END, 300 + (trunc(random() * 200 + 1)) from generate_series(1,20000000,1); INSERT 0 20000000 Time: 158104,805 ms (02:38,105) analyze geants; ANALYZE Temps : 503,763 ms select count(*) from geants where devise ='MEMOIAJOTUNHJERETUNETTIVETIV'; count --------- 1980257 (1 ligne) \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+----------------+----------+--------------+------------+------------- public | geants | table | postgres | 1302 MB | public | geants_idg_seq | séquence | postgres | 8192 bytes | (2 lignes) explain select avg(masse) from geants where devise ='MEMOIAJOTUNHJERETUNETTIVETIV'; QUERY PLAN ------------------------------------------------------------------------------------------- Finalize Aggregate (cost=273880.62..273880.64 rows=1 width=32) -> Gather (cost=273880.41..273880.62 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=272880.41..272880.42 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..270833.88 rows=818613 width=2) Filter: ((devise)::text = 'MEMOIAJOTUNHJERETUNETTIVETIV'::text) (6 lignes) Temps : 1,338 ms explain select avg(masse) from geants where devise ='24D8C7EA0335467C90143834F2826884'; QUERY PLAN ----------------------------------------------------------------------------------------- Finalize Aggregate (cost=271834.16..271834.17 rows=1 width=32) -> Gather (cost=271833.95..271834.16 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=270833.95..270833.96 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..270833.88 rows=28 width=2) Filter: ((devise)::text = '24D8C7EA0335467C90143834F2826884'::text) (6 lignes) Temps : 0,849 ms select avg(masse) from geants where devise ='MEMOIAJOTUNHJERETUNETTIVETIV'; avg ---------------------- 400.3484860803420970 (1 ligne) Time: 1595,648 ms (00:01,596) ... Time: 1589,666 ms (00:01,590) ... Time: 1603,217 ms (00:01,603) select avg(masse) from geants where devise ='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Time: 1630,129 ms (00:01,630) ... Time: 1604,835 ms (00:01,605) ... Time: 1607,230 ms (00:01,607) create index geants_br1 on geants using brin(devise); CREATE INDEX Time: 11512,066 ms (00:11,512) \di+ geants_br1 Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+------------+-------+--------------+--------+--------+------------- public | geants_br1 | index | postgres | geants | 152 kB | (1 ligne) explain select avg(masse) from geants where devise ='MEMOIAJOTUNHJERETUNETTIVETIV'; QUERY PLAN ------------------------------------------------------------------------------------------- Finalize Aggregate (cost=273880.41..273880.42 rows=1 width=32) -> Gather (cost=273880.19..273880.40 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=272880.19..272880.20 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..270833.67 rows=818611 width=2) Filter: ((devise)::text = 'MEMOIAJOTUNHJERETUNETTIVETIV'::text) (6 lignes) Temps : 1,288 ms explain select avg(masse) from geants where devise ='24D8C7EA0335467C90143834F2826884'; QUERY PLAN --------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=194260.46..194260.47 rows=1 width=32) -> Gather (cost=194260.24..194260.45 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=193260.24..193260.25 rows=1 width=32) -> Parallel Bitmap Heap Scan on geants (cost=70.63..193260.17 rows=28 width=2) Recheck Cond: ((devise)::text = '24D8C7EA0335467C90143834F2826884'::text) -> Bitmap Index Scan on geants_br1 (cost=0.00..70.62 rows=5092327 width=0) Index Cond: ((devise)::text = '24D8C7EA0335467C90143834F2826884'::text) (8 lignes) select avg(masse) from geants where devise ='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Time: 2449,333 ms (00:02,449) ... Time: 2474,948 ms (00:02,475) ... Time: 2435,501 ms (00:02,436) set random_page_cost=1; SET Temps : 0,737 ms explain select avg(masse) from geants where devise ='MEMOIAJOTUNHJERETUNETTIVETIV'; QUERY PLAN ------------------------------------------------------------------------------------------- Finalize Aggregate (cost=273880.41..273880.42 rows=1 width=32) -> Gather (cost=273880.19..273880.40 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=272880.19..272880.20 rows=1 width=32) -> Parallel Seq Scan on geants (cost=0.00..270833.67 rows=818611 width=2) Filter: ((devise)::text = 'MEMOIAJOTUNHJERETUNETTIVETIV'::text) (6 lignes) Temps : 1,061 ms create index geants_i1 on geants(devise); CREATE INDEX Time: 166135,432 ms (02:46,135) explain select avg(masse) from geants where devise ='MEMOIAJOTUNHJERETUNETTIVETIV'; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=206576.06..206576.07 rows=1 width=32) -> Gather (cost=206575.84..206576.05 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=205575.84..205575.85 rows=1 width=32) -> Parallel Index Scan using geants_i1 on geants (cost=0.56..203529.31 rows=818611 width=2) Index Cond: ((devise)::text = 'MEMOIAJOTUNHJERETUNETTIVETIV'::text) (6 lignes) select avg(masse) from geants where devise ='MEMOIAJOTUNHJERETUNETTIVETIV'; avg ---------------------- 400.3484860803420970 (1 ligne) Time: 1046,080 ms (00:01,046) ... Time: 1053,231 ms (00:01,053) ... Time: 1038,756 ms (00:01,039) explain select avg(masse) from geants where devise ='24D8C7EA0335467C90143834F2826884'; QUERY PLAN --------------------------------------------------------------------------------- Aggregate (cost=68.88..68.89 rows=1 width=32) -> Index Scan using geants_i1 on geants (cost=0.56..68.72 rows=66 width=2) Index Cond: ((devise)::text = '24D8C7EA0335467C90143834F2826884'::text) (3 lignes) select avg(masse) from geants where devise ='24D8C7EA0335467C90143834F2826884'; avg ----- (1 ligne) Temps : 0,925 ms ... Temps : 0,801 ms ... Temps : 0,772 ms

      Que pouvons-nous constater ?
      Lors de la recherche de la masse moyenne des 10% de MEMOIAJOTUNHJERETUNETTIVETIV, le planner ignore l'index BRIN. Il utilise en revanche l'index B-Tree qui s'avère assez efficace avec un passage de 1s6 à 1s environ.
      Ensuite, la recherche de la masse moyenne des 24D8C7EA0335467C90143834F2826884 se révèle TRÈS efficace via le B-Tree alors que le passage par l’index BRIN se révèle nuisible (2s5 contre 1s6).
      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 au mieux inutiles et au pire 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 : 21/05/2017