Indexation BRIN et partitionnement
(sujet préalablement traité avec la version 9.5)
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é.
Attention cet article s’appuie sur PostgreSQL 10 qui est encore en développement au 04/03/2017. N’utilisez pas une version "devel" en production. Cependant le partitionnement évolue si favorablement avec PostgreSQL 10 que je vous conseillerais presque d’attendre cette version pour le mettre en place si ce n’est pas encore fait !
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 2015 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 10.0devel :
\timing
Chronométrage activé.
select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 10devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-8) 6.3.0 20170221, 64-bit
(1 ligne)
Temps : 21,418 ms
set max_parallel_workers_per_gather=0;
SET
Temps : 0,155 ms
create table ventes_agg(dtv timestamp, id_prod varchar(32), mtv integer);
CREATE TABLE
Temps : 5,985 ms
WITH serie(i) AS (SELECT generate_series(15768000,1,-1)) INSERT INTO ventes_agg
(SELECT current_timestamp - (ceil(i/3) || ' minutes')::interval,
case
when i%3 = 0 then 'IBOL'
when i%3=1 then 'CARK'
else 'TIAL'
end,
trunc(random() *
(case
when i%3 = 0 then case when i >= 7884003 then 140000 when i >= 6307203 then 120000 else 90000 end
when i%3 = 1 then case when i >= 7884003 then 100000 when i >= 6307203 then 100000 else 100000 end
when i%3 = 2 then case when i >= 7884003 then 11000 when i >= 6307203 then 15000 else 19000 end
end) + 1) from serie);
INSERT 0 15768000
Time: 72837,606 ms (01:12,838)
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+------------+-------+--------------+--------+-------------
public | ventes_agg | table | postgres | 785 MB |
(1 ligne)
analyze ventes_agg;
ANALYZE
Temps : 299,304 ms
Alex lance sa requête :
explain select id_prod, sum(mtv) from ventes_agg where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=388002.10..388002.11 rows=3 width=13)
Sort Key: (sum(mtv)) DESC
-> HashAggregate (cost=388002.05..388002.08 rows=3 width=13)
Group Key: id_prod
-> Seq Scan on ventes_agg (cost=0.00..376374.00 rows=2325610 width=9)
Filter: (((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) AND (dtv >= to_timestamp('01/01/2015 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
(6 lignes)
Temps : 0,580 ms
select id_prod, sum(mtv) from ventes_agg where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc;
id_prod | sum
---------+-------------
CARK | 57184416251
IBOL | 51373702536
(2 lignes)
1ere ex : Time: 8562,683 ms (00:08,563)
2eme ex : Time: 8573,791 ms (00:08,574)
3eme ex : Time: 8599,930 ms (00:08,600)
Triomphe pour M.Krac mais pas pour Alex. M.Granpatron lui a fait remarquer pendant les 8 à 9 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 3 secondes vous nous devrez 1000 euros par seconde de retard.".
Vous l’aurez compris M.Granpatron est près de ses sous. Alex n’a droit ni à plus d’espace disque ni à plus de puissance CPU, il décide d’ajouter un index BRIN et relance la requête :
create index br_dtv on ventes_agg using brin(dtv);
CREATE INDEX
Time: 2337,710 ms (00:02,338)
\di+
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+--------+-------+--------------+------------+--------+-------------
public | br_dtv | index | postgres | ventes_agg | 64 kB |
(1 ligne)
explain select id_prod, sum(mtv) from ventes_agg where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Sort (cost=199435.71..199435.71 rows=3 width=13)
Sort Key: (sum(mtv)) DESC
-> HashAggregate (cost=199435.65..199435.68 rows=3 width=13)
Group Key: id_prod
-> Bitmap Heap Scan on ventes_agg (cost=26633.06..187807.60 rows=2325610 width=9)
Recheck Cond: (dtv >= to_timestamp('01/01/2015 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..26051.66 rows=3470888 width=0)
Index Cond: (dtv >= to_timestamp('01/01/2015 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))
(9 lignes)
Temps : 0,588 ms
select id_prod, sum(mtv) from ventes_agg where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc;
id_prod | sum
---------+-------------
CARK | 57184416251
IBOL | 51373702536
(2 lignes)
1ere ex : Time: 2966,468 ms (00:02,966)
2eme ex : Time: 2886,217 ms (00:02,886)
3eme ex : Time: 2888,100 ms (00:02,888)
Pas mal mais pas tout à fait suffisant. Les temps sont très proches des 3 secondes fatidiques et Alex ne veut prendre aucun risque. 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.
La syntaxe permettant de partitionner a évolué très favorablement avec PostgreSQL 10. Elle est plus directe et, surtout, ne néssite plus la création d’un trigger et d’une fonction. Cela consiste cependant toujours à découper VENTES_AGG en plusieurs tables, ce qui donne ici une table VENTES_AGG_P et une table par partition.
Alex sait que le partionnement de PostgreSQL permet le découpage se basant sur une liste de valeurs et le découpage par plages de valeurs. Il choisit ici le partitionnement par liste :
create table ventes_agg_p (dtv timestamp, id_prod varchar(32), mtv integer) partition by list(id_prod);
CREATE TABLE
Temps : 41,716 ms
create table ventes_agg_p_cark partition of ventes_agg_p for values in ('CARK');
CREATE TABLE
Temps : 20,225 ms
create table ventes_agg_p_tial partition of ventes_agg_p for values in ('TIAL');
CREATE TABLE
Temps : 16,931 ms
create table ventes_agg_p_ibol partition of ventes_agg_p for values in ('IBOL');
CREATE TABLE
Temps : 19,014 ms
insert into ventes_agg_p select * from ventes_agg;
INSERT 0 15768000
Time: 60497,713 ms (01:00,498)
\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 |
(5 lignes)
analyze ventes_agg_p_cark ;
ANALYZE
Temps : 426,518 ms
analyze ventes_agg_p_tial ;
ANALYZE
Temps : 411,574 ms
analyze ventes_agg_p_ibol ;
ANALYZE
Temps : 774,781 ms
explain select id_prod, sum(mtv) from ventes_agg_p where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=262224.04..262224.05 rows=3 width=13)
Sort Key: (sum(ventes_agg_p.mtv)) DESC
-> HashAggregate (cost=262223.99..262224.02 rows=3 width=13)
Group Key: ventes_agg_p.id_prod
-> Append (cost=0.00..250915.83 rows=2261633 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/2015 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
-> Seq Scan on ventes_agg_p_cark (cost=0.00..125457.91 rows=1126666 width=9)
Filter: (((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) AND (dtv >= to_timestamp('01/01/2015 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
-> Seq Scan on ventes_agg_p_ibol (cost=0.00..125457.91 rows=1134966 width=9)
Filter: (((id_prod)::text = ANY ('{IBOL,CARK}'::text[])) AND (dtv >= to_timestamp('01/01/2015 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
(11 lignes)
Temps : 0,845 ms
select id_prod, sum(mtv) from ventes_agg where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc;
id_prod | sum
---------+-------------
CARK | 57184416251
IBOL | 51373702536
(2 lignes)
1ere ex : Time: 2895,825 ms (00:02,896)
2eme ex : Time: 2845,087 ms (00:02,845)
3eme ex : Time: 2862,113 ms (00:02,862)
Les temps obtenus avec le partitionnement sont proches des temps obtenus avec la table originale et un index BRIN. La situation est donc toujours dangereuse pour Alex qui a heureusement un ultime atout à jouer. Il décide de combiner partitionnement et indexation BRIN :
create index br_dtv_ibol on ventes_agg_p_ibol using brin(dtv);
CREATE INDEX
Temps : 830,925 ms
create index br_dtv_cark on ventes_agg_p_cark using brin(dtv);
CREATE INDEX
Temps : 791,543 ms
create index br_dtv_tial on ventes_agg_p_tial using brin(dtv);
CREATE INDEX
Temps : 805,021 ms
\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 |
public | br_dtv_tial | index | postgres | ventes_agg_p_tial | 48 kB |
(4 lignes)
explain select id_prod, sum(mtv) from ventes_agg_p where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=135394.49..135394.49 rows=3 width=13)
Sort Key: (sum(ventes_agg_p.mtv)) DESC
-> HashAggregate (cost=135394.43..135394.46 rows=3 width=13)
Group Key: ventes_agg_p.id_prod
-> Append (cost=0.00..124086.26 rows=2261635 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/2015 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
-> Bitmap Heap Scan on ventes_agg_p_cark (cost=8743.67..61938.34 rows=1126667 width=9)
Recheck Cond: (dtv >= to_timestamp('01/01/2015 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..8462.00 rows=1126667 width=0)
Index Cond: (dtv >= to_timestamp('01/01/2015 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))
-> Bitmap Heap Scan on ventes_agg_p_ibol (cost=8807.99..62147.92 rows=1134967 width=9)
Recheck Cond: (dtv >= to_timestamp('01/01/2015 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..8524.25 rows=1134967 width=0)
Index Cond: (dtv >= to_timestamp('01/01/2015 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))
(17 lignes)
Temps : 0,747 ms
select id_prod, sum(mtv) from ventes_agg_p where id_prod in ('IBOL','CARK') and dtv >= to_timestamp('01/01/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS') group by id_prod order by 2 desc;
id_prod | sum
---------+-------------
CARK | 57184416251
IBOL | 51373702536
(2 lignes)
1ere ex : Time: 2076,715 ms (00:02,077)
2eme ex : Time: 2023,136 ms (00:02,023)
3eme ex : Time: 2054,085 ms (00:02,054)
BINGO ! Un peu plus de 2 secondes, la marge de sécurité devient suffisante par rapport à l’exigence du patron. 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 son défi.
Le partitionnement de PostgreSQL est bien plus simple à mettre en place en version 10+ qu’avec les versions 9.x. Il est toujours 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 : 04/03/2017