Indexation BRIN et partitionnement

(sujet mis à jour avec la version 10 devel)

Plus de service pour 0 euro...octet de plus ?

      Dans cette page présentant les index BRIN nous avons vu qu’ils prenaient peu de place et pouvaient être très efficaces. Nous allons voir comment ils peuvent être combinés avec le partitionnement pour encore plus de réactivité.
      Il était une fois une entreprise qui vendait principalement 3 produits : l’IBOL, le CARK et le TIAL. Un jour, le chef de produit de l’IBOL, M.Lobi, crie à M.Krac, chef de produit de CARK, : "N’empêche que depuis 2014 je vends encore plus que toi balochard !"
      "Balochard". L’attaque est sévère. M.Krac demande à Alex Mou, le stagiaire, de sortir IMMÉDIATEMENT les chiffres pour contrer cette affirmation, en présence de M.Granpatron, le PDG de la boîte.
      Alex dispose d’une table VENTES_AGG contenant le montant des ventes de l’entreprise à chaque minute pour chaque produit. Pour le produit ID_PROD, le montant de vente MTV a été réalisé dans la minute suivant le temps stocké dans la colonne DTV.
      Préparation de l’environnement de test sous PostgreSQL 9.5.0 :

postgres=# \timing Chronométrage activé. postgres=# drop table ventes_agg; DROP TABLE Temps : 5,175 ms postgres=# create table ventes_agg(dtv timestamp, id_prod varchar(32), mtv integer); CREATE TABLE Temps : 788,437 ms postgres=# do postgres-# $$ postgres$# begin postgres$# for i in reverse 5256000..2628001 loop postgres$# insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, 'IBOL', trunc(random() * 140000 + 1)); postgres$# insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, 'CARK', trunc(random() * 100000 + 1)); postgres$# insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, 'TIAL', trunc(random() * 11000 + 1)); postgres$# end loop; postgres$# end$$; DO Temps : 195236,868 ms postgres=# do postgres-# $$ postgres$# begin postgres$# for i in reverse 2628000..2102401 loop postgres$# insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, 'IBOL', trunc(random() * 120000 + 1)); postgres$# insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, 'CARK', trunc(random() * 100000 + 1)); postgres$# insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, 'TIAL', trunc(random() * 15000 + 1)); postgres$# end loop; postgres$# end$$; DO Temps : 39817,755 ms postgres=# do postgres-# $$ postgres$# begin postgres$# for i in reverse 2102400..1 loop postgres$# insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, 'IBOL', trunc(random() * 90000 + 1)); postgres$# insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, 'CARK', trunc(random() * 100000 + 1)); postgres$# insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, 'TIAL', trunc(random() * 19000 + 1)); postgres$# end loop; postgres$# end$$; DO Temps : 155305,550 ms postgres=# \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+------------+-------+--------------+--------+------------- public | ventes_agg | table | postgres | 785 MB |

      Alex lance sa requête :

postgres=# explain select id_prod, sum(mtv) from ventes_agg where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2014 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=387277.00..387277.01 rows=1 width=9) Sort Key: (sum(mtv)) DESC -> HashAggregate (cost=387276.98..387276.99 rows=1 width=9) Group Key: id_prod -> Seq Scan on ventes_agg (cost=0.00..376374.00 rows=2180596 width=9) Filter: (((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) AND (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))) (6 lignes) Temps : 3,376 ms postgres=# select id_prod, sum(mtv) from ventes_agg where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2014 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc; id_prod | sum ---------+------------- CARK | 54878073045 IBOL | 49417802623 (2 lignes) Temps : 35902,453 ms

      Triomphe pour M.Krac mais pas pour Alex. M.Granpatron lui a fait remarquer pendant les 36 secondes d’attente : "On a failli attendre M.Mou, ce n’est pas parce que vous n’êtes pas payé que vous devez glander. La prochaine fois si cela dure plus de 10 secondes vous nous devrez 1000 euros par seconde de retard.".
      Alex n’a pas droit à plus d’espace disque, il décide d’ajouter un index BRIN et relance la requête :

postgres=# create index br_dtv on ventes_agg using brin(dtv); CREATE INDEX Temps : 5296,107 ms postgres=# \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+--------+-------+--------------+------------+--------+------------- public | br_dtv | index | postgres | ventes_agg | 64 kB | (1 ligne) postgres=# explain select id_prod, sum(mtv) from ventes_agg where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2014 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Sort (cost=194122.60..194122.61 rows=1 width=9) Sort Key: (sum(mtv)) DESC -> HashAggregate (cost=194122.58..194122.59 rows=1 width=9) Group Key: id_prod -> Bitmap Heap Scan on ventes_agg (cost=25231.29..183219.60 rows=2180596 width=9) Recheck Cond: (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) Filter: ((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) -> Bitmap Index Scan on br_dtv (cost=0.00..24686.14 rows=3288818 width=0) Index Cond: (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) (9 lignes) Temps : 2,861 ms postgres=# select id_prod, sum(mtv) from ventes_agg where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2014 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc; id_prod | sum ---------+------------- CARK | 54878073045 IBOL | 49417802623 (2 lignes) Temps : 12105,069 ms

      Pas mal mais pas tout à fait suffisant. La plupart des requêtes concernent un produit donné. Alex décide donc de partitionner la table VENTES_AGG au niveau de la colonne ID_PROD. Cela consiste avec PostgreSQL à découper VENTES_AGG en plusieurs tables, ce qui donne une table VENTES_AGG_P et une table par partition héritant de VENTES_AGG_P.
      Alex sait que le partionnement de PostgreSQL ne supporte que le découpage par liste (celui qu’il a choisi ici) ou par plage de valeurs. Il sait aussi que cela oblige à programmer une fonction et un trigger mais il décide de tenter sa chance. Pour faire la reprise de données une RULE serait plus efficace qu’un TRIGGER. Cependant Alex compte remplacer définitivement VENTES_AGG par VENTES_AGG_P. Hors, par la suite, le TRIGGER donnera de meilleures performances pour insérer des lignes individuelles d’après la documentation PostgreSQL :

postgres=# create table ventes_agg_p (dtv timestamp, id_prod varchar(32), mtv integer); CREATE TABLE Temps : 5,671 ms postgres=# CREATE TABLE ventes_agg_p_ibol postgres-# ( postgres(# CONSTRAINT ventes_agg_p_ibol CHECK ( id_prod = 'IBOL' ) postgres(# ) postgres-# INHERITS (ventes_agg_p) postgres-# ; CREATE TABLE Temps : 219,362 ms postgres=# CREATE TABLE ventes_agg_p_tial postgres-# ( postgres(# CONSTRAINT ventes_agg_p_tial CHECK ( id_prod = 'TIAL' ) postgres(# ) postgres-# INHERITS (ventes_agg_p) postgres-# ; CREATE TABLE Temps : 5,822 ms postgres=# CREATE TABLE ventes_agg_p_cark postgres-# ( postgres(# CONSTRAINT ventes_agg_p_cark CHECK ( id_prod = 'CARK' ) postgres(# ) postgres-# INHERITS (ventes_agg_p) postgres-# ; CREATE TABLE Temps : 4,665 ms postgres=# CREATE TABLE ventes_agg_p_xxxx postgres-# ( postgres(# CONSTRAINT ventes_agg_p_xxxx CHECK ( id_prod not in ('IBOL','TIAL','CARK' )) postgres(# ) postgres-# INHERITS (ventes_agg_p) postgres-# ; CREATE TABLE Temps : 21,650 ms postgres=# CREATE OR REPLACE FUNCTION ventes_agg_p_insert() postgres-# RETURNS trigger AS postgres-# $BODY$ postgres$# BEGIN postgres$# IF ( NEW.id_prod = 'IBOL' ) THEN postgres$# INSERT INTO ventes_agg_p_ibol VALUES (NEW.*); postgres$# ELSIF ( NEW.id_prod = 'TIAL' ) THEN postgres$# INSERT INTO ventes_agg_p_tial VALUES (NEW.*); postgres$# ELSIF ( NEW.id_prod = 'CARK' ) THEN postgres$# INSERT INTO ventes_agg_p_cark VALUES (NEW.*); postgres$# ELSIF ( NEW.id_prod not in ('IBOL','TIAL','CARK' ) ) THEN postgres$# INSERT INTO ventes_agg_p_xxxx VALUES (NEW.*); postgres$# ELSE postgres$# RAISE EXCEPTION 'ERREUR : verifier la fonction ventes_agg_p_insert()'; postgres$# END IF; postgres$# RETURN NULL; postgres$# END; postgres$# $BODY$ postgres-# LANGUAGE plpgsql; CREATE FUNCTION Temps : 16,575 ms postgres=# CREATE TRIGGER ventes_agg_p_insert_trg postgres-# BEFORE INSERT postgres-# ON ventes_agg_p postgres-# FOR EACH ROW postgres-# EXECUTE PROCEDURE ventes_agg_p_insert(); CREATE TRIGGER Temps : 98,184 ms postgres=# insert into ventes_agg_p select * from ventes_agg; INSERT 0 0 Temps : 592341,877 ms postgres=# \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-------------------+-------+--------------+---------+------------- public | ventes_agg | table | postgres | 785 MB | public | ventes_agg_p | table | postgres | 0 bytes | public | ventes_agg_p_cark | table | postgres | 262 MB | public | ventes_agg_p_ibol | table | postgres | 262 MB | public | ventes_agg_p_tial | table | postgres | 262 MB | public | ventes_agg_p_xxxx | table | postgres | 0 bytes | (6 lignes) postgres=# explain select id_prod, sum(mtv) from ventes_agg_p where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2014 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=261935.35..261935.85 rows=200 width=9) Sort Key: (sum(ventes_agg_p.mtv)) DESC -> HashAggregate (cost=261925.71..261927.71 rows=200 width=9) Group Key: ventes_agg_p.id_prod -> Append (cost=0.00..250915.91 rows=2201959 width=9) -> Seq Scan on ventes_agg_p (cost=0.00..0.00 rows=1 width=86) Filter: (((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) AND (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))) -> Seq Scan on ventes_agg_p_ibol (cost=0.00..125458.00 rows=1098423 width=9) Filter: (((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) AND (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))) -> Seq Scan on ventes_agg_p_cark (cost=0.00..125457.91 rows=1103535 width=9) Filter: (((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) AND (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))) (11 lignes) Temps : 3,136 ms postgres=# select id_prod, sum(mtv) from ventes_agg_p where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2014 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc; id_prod | sum ---------+------------- CARK | 54878073045 IBOL | 49417802623 (2 lignes) Temps : 33162,847 ms

      33s, ce n’est pas impressionnant mais avec en plus les index BRIN qui vont bien ?

postgres=# create index br_dtv_ibol on ventes_agg_p_ibol using brin(dtv); CREATE INDEX Temps : 1552,927 ms postgres=# create index br_dtv_cark on ventes_agg_p_cark using brin(dtv); CREATE INDEX Temps : 1709,473 ms postgres=# \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+-------------+-------+--------------+-------------------+--------+------------- public | br_dtv | index | postgres | ventes_agg | 64 kB | public | br_dtv_cark | index | postgres | ventes_agg_p_cark | 48 kB | public | br_dtv_ibol | index | postgres | ventes_agg_p_ibol | 48 kB | (3 lignes) postgres=# explain select id_prod, sum(mtv) from ventes_agg_p where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2014 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=133598.92..133599.42 rows=200 width=9) Sort Key: (sum(ventes_agg_p.mtv)) DESC -> HashAggregate (cost=133589.27..133591.27 rows=200 width=9) Group Key: ventes_agg_p.id_prod -> Append (cost=0.00..122579.47 rows=2201960 width=9) -> Seq Scan on ventes_agg_p (cost=0.00..0.00 rows=1 width=86) Filter: (((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) AND (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))) -> Bitmap Heap Scan on ventes_agg_p_ibol (cost=8524.78..61225.18 rows=1098423 width=9) Recheck Cond: (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) Filter: ((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) -> Bitmap Index Scan on br_dtv_ibol (cost=0.00..8250.18 rows=1098423 width=0) Index Cond: (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) -> Bitmap Heap Scan on ventes_agg_p_cark (cost=8564.41..61354.29 rows=1103536 width=9) Recheck Cond: (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) Filter: ((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) -> Bitmap Index Scan on br_dtv_cark (cost=0.00..8288.53 rows=1103536 width=0) Index Cond: (dtv >= to_timestamp('01/01/2014 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) (17 lignes) Temps : 3,837 ms postgres=# select id_prod, sum(mtv) from ventes_agg_p where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2014 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc; id_prod | sum ---------+------------- CARK | 54878073045 IBOL | 49417802623 (2 lignes) Temps : 7965,516 ms

      La table VENTES_AGG_P pourrait remplacer la table VENTES_AGG à l’avenir. Les index BRIN sont minuscules. Sans consommer d’espace, le stagiaire a donc réussi le défi du patron : le temps de réponse est passé de 36 secondes à moins de 8 secondes.
      Le partitionnement de PostgreSQL est perfectible mais peut s’avérer utile dans le cadre d’un environnement décisionnel. Ici le cas était pourtant défavorable au partitionnement. En effet la requête nécessitait d’accéder à 2 des 3 partitions de taille égale.
      Et si rien de tout ça ne convient ? Vous pouvez envisager le parallélisme en dernier recours...

Mise à jour : 02/02/2016