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 quils 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 : lIBOL, le CARK et le TIAL. Un jour, le chef de produit de lIBOL, M.Lobi, crie à M.Krac, chef de produit de CARK, : "Nempêche que depuis 2014 je vends encore plus que toi balochard !"
"Balochard". Lattaque 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 dune table VENTES_AGG contenant le montant des ventes de lentreprise à 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 lenvironnement 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 dattente : "On a failli attendre M.Mou, ce nest 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 na pas droit à plus despace disque, il décide dajouter 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 quil 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 quun 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 daprè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 nest 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 à lavenir. Les index BRIN sont minuscules. Sans consommer despace, 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 savérer utile dans le cadre dun environnement décisionnel. Ici le cas était pourtant défavorable au partitionnement. En effet la requête nécessitait daccé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