Statistiques étendues
Permettre à PostgreSQL de ne pas boucler jusquà la folie
En complément du "clustering factor", la sélectivité est un critère important pour savoir si le passage par un index peut être bénéfique et, plus généralement, déterminer un plan dexécution optimal. Des statistiques sont ainsi collectées afin de savoir si tel critère présent dans la clause de filtrage permet décarter un grand pourcentage de lignes du jeu de résultats. Ces statistiques sont calculées colonne par colonne.
Le contexte est toujours celui du clan de géants lançant des rochers. Nous allons supposer en renseignant la colonne GABARIT de la table "geants" que chaque géant a environ 30% de chances davoir un PETIT gabarit, 30% de chance davoir un gabarit MOYEN tandis que les autres géants ont un GRAND gabarit. Nous allons faire quasi de même pour les colonnes COULEUR, ERE, BERSERK et CYCLOPE. Mais, au lieu dutiliser une valeur aléatoire différente pour chaque colonne, nous allons réutiliser la même valeur aléatoire. Elle sera donc aléatoire pour une ligne donnée mais pas pour une colonne. Autrement dit, dans quasi tous les cas, si un géant a un PETIT gabarit alors il aime le GRIS, il est de lère du TAUREAU, est BERSERK et CYCLOPE. La table "lancers" est générée entièrement aléatoirement, chaque géant comptera environ 1000 lancers. Nous allons déterminer la performances moyenne des "PETITS TAUREAUX GRIS BERSERKS CYCLOPES". Démonstration avec PostgreSQL 10 devel :
select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 10devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-12) 6.3.0 20170406, 64-bit
(1 ligne)
create table geants(
idg integer generated always as identity primary key,
genre char(1),
taille smallint,
masse smallint,
actif boolean,
devise varchar(128),
pw smallint,
heureux boolean,
couleur varchar(8),
veteran boolean,
clan smallint,
gabarit varchar(8),
revenu integer,
pm smallint,
berserk boolean,
tutelaire smallint,
ere varchar(10),
cyclope boolean);
CREATE TABLE
WITH serie(i, r1) AS (SELECT generate_series(1,100000,1), random())
insert into geants(genre, taille, masse, actif, devise, pw, heureux, couleur, veteran, clan, gabarit, revenu, pm, berserk, tutelaire, ere, cyclope)
select
case when random() < 0.45 then 'M' else 'F' end,
200 + (trunc(random() * 200 + 1)),
300 + (trunc(random() * 200 + 1)),
case when random() < 0.01 then false when random() > 0.5 and random() < 0.99 then false else true end,
upper(md5(random()::text)),
(trunc(random()*100 + 1)),
case when random() < 0.1 then false else true end,
case when r1 <= 0.29998 then 'GRIS' when r1 <= 0.59998 then 'NOIR' else 'BLEU' end,
case when random() < 0.9 then false else true end,
(trunc(random()*1000 + 1)),
case when r1 <= 0.29999 then 'PETIT' when r1 <= 0.59999 then 'MOYEN' else 'GRAND' end,
(trunc(random()*1000000 + 1)),
(trunc(random()*10 + 1)),
case when r1 <= 0.3 then true when r1 <= 0.6 then false else null end,
(trunc(random()*10 + 1)),
case when r1 <= 0.30001 then 'TAUREAU' when r1 <= 0.60001 then 'LICORNE' else 'DRAGON' end,
case when r1 <= 0.30002 then true when r1 <= 0.60002 then false else null end
from serie;
INSERT 0 100000
select couleur, gabarit, berserk, ere, cyclope , count(*) from geants group by couleur, gabarit, berserk, ere, cyclope order by 6 desc;
couleur | gabarit | berserk | ere | cyclope | count
---------+---------+---------+---------+---------+-------
BLEU | GRAND | | DRAGON | | 40057
NOIR | MOYEN | f | LICORNE | f | 30168
GRIS | PETIT | t | TAUREAU | t | 29767
BLEU | GRAND | | DRAGON | f | 4
NOIR | MOYEN | t | TAUREAU | t | 1
NOIR | PETIT | t | TAUREAU | t | 1
NOIR | MOYEN | f | TAUREAU | t | 1
BLEU | MOYEN | f | LICORNE | f | 1
(8 lignes)
create table lancers(dtl timestamp, idg integer, perf integer);
CREATE TABLE
WITH serie(i) AS (SELECT generate_series(100000000,1,-1))
insert into lancers(dtl, idg, perf)
select
current_timestamp - (i || ' minutes')::interval,
trunc(random() * 100000 + 1),
case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end
from serie;
INSERT 0 100000000
analyze geants;
ANALYZE
analyze lancers;
ANALYZE
explain analyze select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=1498350.43..1499133.96 rows=236 width=69) (actual time=38413.365..43407.921 rows=29767 loops=1)
Group Key: g.idg
-> Gather Merge (cost=1498350.43..1499128.65 rows=472 width=69) (actual time=38413.223..43340.402 rows=89301 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=1497350.40..1498074.14 rows=236 width=69) (actual time=36245.188..41145.477 rows=29767 loops=3)
Group Key: g.idg
-> Sort (cost=1497350.40..1497590.86 rows=96184 width=41) (actual time=36244.758..38688.001 rows=9918549 loops=3)
Sort Key: g.idg
Sort Method: external merge Disk: 520488kB
-> Hash Join (cost=3181.95..1486428.49 rows=96184 width=41) (actual time=43.189..20808.381 rows=9918549 loops=3)
Hash Cond: (l.idg = g.idg)
-> Parallel Seq Scan on lancers l (cost=0.00..957208.03 rows=41666703 width=8) (actual time=0.017..6278.902 rows=33333333 loops=3)
-> Hash (cost=3179.00..3179.00 rows=236 width=37) (actual time=43.150..43.150 rows=29767 loops=3)
Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2262kB
-> Seq Scan on geants g (cost=0.00..3179.00 rows=236 width=37) (actual time=0.012..34.166 rows=29767 loops=3)
Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text))
Rows Removed by Filter: 70233
Planning time: 0.276 ms
Execution time: 61465.142 ms
Lexécution de la requête a pris un peu plus d1 minute. Un balayage complet des tables GEANTS et LANCERS est effectué et la jointure réalisée est de type HASH JOIN. Nous pouvons remarquer que lestimation du planner en ce qui concerne le nombre de ligne renvoyées était complètement erronée. Si les données avaient été générées aléatoirement nous aurions bien autour de 0,3 X 0,3 X 0,3 X 0,3 X 0,3 X 100 000 = 243 géants de PETIT gabarit, aimant le GRIS, de lère du TAUREAU, BERSERK et CYCLOPE. Lestimation de 236 apparaissant dans le plan serait donc excellente. Mais malheureusement ce nest pas le cas, nous avons une quasi dépendance entre les valeurs de ces 5 colonnes et le nombre de "PETITS TAUREAUX GRIS BERSERKS CYCLOPES" est bien plus élevé. Lexécution le confirme puisque 29767 géants sont dans ce cas.
Cette estimation erronée na pas prêté à conséquence car, en labsence dindex sur la table lancers, le planner navait pas trop le choix. Mais nous allons corser laffaire. Un index composite est créé sur la table "lancers" pour les colonnes idg, dtl. Quel impact sur la requête ?
create index lancers_i1 on lancers(idg, dtl);
CREATE INDEX
explain analyze select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=1024.45..316631.48 rows=236 width=69) (actual time=187839.599..4376609.290 rows=29767 loops=1)
Group Key: g.idg
-> Gather Merge (cost=1024.45..316626.17 rows=472 width=69) (actual time=171130.278..4376519.464 rows=29767 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=24.42..315571.67 rows=236 width=69) (actual time=15587.501..4083171.466 rows=9922 loops=3)
Group Key: g.idg
-> Nested Loop (cost=24.42..315088.39 rows=96184 width=41) (actual time=110.304..4079736.384 rows=9918549 loops=3)
-> Parallel Index Scan using geants_pkey on geants g (cost=0.29..3765.46 rows=98 width=37) (actual time=10.629..227.190 rows=9922 loops=3)
Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text))
Rows Removed by Filter: 23411
-> Bitmap Heap Scan on lancers l (cost=24.13..3166.98 rows=978 width=8) (actual time=9.512..410.745 rows=1000 loops=29767)
Recheck Cond: (idg = g.idg)
Heap Blocks: exact=6601169
-> Bitmap Index Scan on lancers_i1 (cost=0.00..23.89 rows=978 width=0) (actual time=8.853..8.853 rows=1000 loops=29767)
Index Cond: (idg = g.idg)
Planning time: 0.322 ms
Execution time: 4376616.033 ms
Catastrophe, la durée de la requêe explose : 1h13 ! Croyant à tort que le nombre de "PETITS TAUREAUX GRIS BERSERKS CYCLOPES" était faible le planner a choisi les boucles imbriquées pour effectuer la jointure. Mon environnement a de plus un problème particulier de ressources I/O et le parallélisme empire donc encore les choses.
Rassurez-vous. Nous avons déjà tous les moyens de revenir au plan initial, par exemple en montant random_page_cost. Démonstration :
set random_page_cost=20;
SET
explain analyze select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=1487979.11..1487985.60 rows=236 width=69) (actual time=27916.753..27979.588 rows=29767 loops=1)
Group Key: g.idg
-> Sort (cost=1487979.11..1487980.29 rows=472 width=69) (actual time=27916.738..27936.696 rows=89301 loops=1)
Sort Key: g.idg
Sort Method: external merge Disk: 7352kB
-> Gather (cost=1487908.59..1487958.15 rows=472 width=69) (actual time=27711.261..27836.767 rows=89301 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial HashAggregate (cost=1486908.59..1486910.95 rows=236 width=69) (actual time=27606.392..27622.364 rows=29767 loops=3)
Group Key: g.idg
-> Hash Join (cost=3181.95..1486427.67 rows=96184 width=41) (actual time=84.643..21408.035 rows=9918549 loops=3)
Hash Cond: (l.idg = g.idg)
-> Parallel Seq Scan on lancers l (cost=0.00..957207.67 rows=41666667 width=8) (actual time=0.032..6438.013 rows=33333333 loops=3)
-> Hash (cost=3179.00..3179.00 rows=236 width=37) (actual time=84.566..84.566 rows=29767 loops=3)
Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2262kB
-> Seq Scan on geants g (cost=0.00..3179.00 rows=236 width=37) (actual time=0.014..76.150 rows=29767 loops=3)
Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text))
Rows Removed by Filter: 70233
Planning time: 0.364 ms
Execution time: 27983.977 ms
Cela fonctionne, nous retournons sur de la jointure par HASH JOIN et le temps dexécution passe sous les 30 secondes. Il serait cependant intéressant de pouvoir fournir au planner linformation dont nous disposons, cest à dire la quasi dépendance entre les 5 colonnes.
Cest là quinterviennent les statistiques étendues introduites avec PostgreSQL 10, elles sont créées avec CREATE STATISTICS mais sont calculées avec ANALYZE :
create statistics geants_couleur_gabarit_ere_berserk_cyclope with(dependencies, ndistinct) on (couleur, gabarit, ere, berserk, cyclope) from geants;
CREATE STATISTICS
analyze geants;
ANALYZE
Time: 3808,133 ms (00:03,808)
select * from pg_statistic_ext;
starelid | 57502
staname | geants_couleur_gabarit_ere_berserk_cyclope
stanamespace | 2200
staowner | 10
stakeys | 9 12 15 17 18
staenabled | {d,f}
standistinct | [{(b 9 12), 3.000000}, {(b 9 15), 3.000000}, {(b 9 17), 3.000000}, {(b 9 18), 4.000000}, {(b 12 15), 3.000000}, {(b 12 17), 3.000000}, {(b 12 18), 4.000000}, {(b 15 17), 3.000000}, {(b 15 18), 4.000000}, {(b 17 18), 4.000000}, {(b 9 12 15), 3.000000}, {(b 9 12 17), 3.000000}, {(b 9 12 18), 4.000000}, {(b 9 15 17), 3.000000}, {(b 9 15 18), 4.000000}, {(b 9 17 18), 4.000000}, {(b 12 15 17), 3.000000}, {(b 12 15 18), 4.000000}, {(b 12 17 18), 4.000000}, {(b 15 17 18), 4.000000}, {(b 9 12 15 17), 3.000000}, {(b 9 12 15 18), 4.000000}, {(b 9 12 17 18), 4.000000}, {(b 9 15 17 18), 4.000000}, {(b 12 15 17 18), 4.000000}, {(b 9 12 15 17 18), 4.000000}]
stadependencies | [{9 => 12 : 1.000000}, {9 => 15 : 1.000000}, {9 => 17 : 1.000000}, {9 => 18 : 0.602767}, {12 => 9 : 1.000000}, {12 => 15 : 1.000000}, {12 => 17 : 1.000000}, {12 => 18 : 0.602767}, {15 => 9 : 1.000000}, {15 => 12 : 1.000000}, {15 => 17 : 1.000000}, {15 => 18 : 0.602767}, {17 => 9 : 1.000000}, {17 => 12 : 1.000000}, {17 => 15 : 1.000000}, {17 => 18 : 0.602767}, {18 => 9 : 0.695500}, {18 => 12 : 0.695500}, {18 => 15 : 0.695500}, {18 => 17 : 0.695500}, {9, 12 => 15 : 1.000000}, {9, 12 => 17 : 1.000000}, {9, 12 => 18 : 0.602767}, {9, 15 => 12 : 1.000000}, {9, 15 => 17 : 1.000000}, {9, 15 => 18 : 0.602767}, {9, 17 => 12 : 1.000000}, {9, 17 => 15 : 1.000000}, {9, 17 => 18 : 0.602767}, {9, 18 => 12 : 1.000000}, {9, 18 => 15 : 1.000000}, {9, 18 => 17 : 1.000000}, {12, 15 => 9 : 1.000000}, {12, 15 => 17 : 1.000000}, {12, 15 => 18 : 0.602767}, {12, 17 => 9 : 1.000000}, {12, 17 => 15 : 1.000000}, {12, 17 => 18 : 0.602767}, {12, 18 => 9 : 1.000000}, {12, 18 => 15 : 1.000000}, {12, 18 => 17 : 1.000000}, {15, 17 => 9 : 1.000000}, {15, 17 => 12 : 1.000000}, {15, 17 => 18 : 0.602767}, {15, 18 => 9 : 1.000000}, {15, 18 => 12 : 1.000000}, {15, 18 => 17 : 1.000000}, {17, 18 => 9 : 1.000000}, {17, 18 => 12 : 1.000000}, {17, 18 => 15 : 1.000000}, {9, 12, 15 => 17 : 1.000000}, {9, 12, 15 => 18 : 0.602767}, {9, 12, 17 => 15 : 1.000000}, {9, 12, 17 => 18 : 0.602767}, {9, 12, 18 => 15 : 1.000000}, {9, 12, 18 => 17 : 1.000000}, {9, 15, 17 => 12 : 1.000000}, {9, 15, 17 => 18 : 0.602767}, {9, 15, 18 => 12 : 1.000000}, {9, 15, 18 => 17 : 1.000000}, {9, 17, 18 => 12 : 1.000000}, {9, 17, 18 => 15 : 1.000000}, {12, 15, 17 => 9 : 1.000000}, {12, 15, 17 => 18 : 0.602767}, {12, 15, 18 => 9 : 1.000000}, {12, 15, 18 => 17 : 1.000000}, {12, 17, 18 => 9 : 1.000000}, {12, 17, 18 => 15 : 1.000000}, {15, 17, 18 => 9 : 1.000000}, {15, 17, 18 => 12 : 1.000000}, {9, 12, 15, 17 => 18 : 0.602767}, {9, 12, 15, 18 => 17 : 1.000000}, {9, 12, 17, 18 => 15 : 1.000000}, {9, 15, 17, 18 => 12 : 1.000000}, {12, 15, 17, 18 => 9 : 1.000000}]
La colonne statkeys nous informe que la statistique geants_couleur_gabarit_ere_berserk_cyclope concerne les colonnes 9 (COULEUR), 12 (GABARIT), 15 (BERSERK), 17 (ERE), 18 (CYCLOPE).
La colonne standistinct nous informe par exemple via {(b 9 12), 3.000000} quil existe 3 combinaisons COULEUR, GABARIT significatives.
Enfin la colonne stadependencies nous informe par exemple via {9 => 12 : 1.000000} que connaître la valeur de la colonne COULEUR pour une ligne permet de déduire la valeur de la colonne GABARIT.
Il y a pour linstant un ou deux Nota Bene, deux ou trois quiproquo comme dirait le Génie dAladdin. Dabord les statistiques étendues nont aucun effet dans mon exemple :
set random_page_cost=4;
SET
explain select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=1024.45..316631.48 rows=236 width=69)
Group Key: g.idg
-> Gather Merge (cost=1024.45..316626.17 rows=472 width=69)
Workers Planned: 2
-> Partial GroupAggregate (cost=24.42..315571.67 rows=236 width=69)
Group Key: g.idg
-> Nested Loop (cost=24.42..315088.39 rows=96184 width=41)
-> Parallel Index Scan using geants_pkey on geants g (cost=0.29..3765.46 rows=98 width=37)
Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text))
-> Bitmap Heap Scan on lancers l (cost=24.13..3166.98 rows=978 width=8)
Recheck Cond: (idg = g.idg)
-> Bitmap Index Scan on lancers_i1 (cost=0.00..23.89 rows=978 width=0)
Index Cond: (idg = g.idg)
(13 lignes)
Toujours une estimation de 236 lignes, il existe pour linstant des restrictions dans lutilisation des statistiques étendues par le planner.
Il existe un autre problème potentiel. Comment le planner peut-il savoir que GABARIT=PETIT donne ERE=TAUREAU et pas ERE=LICORNE sur la base des informations de statistiques étendues ? Si les distributions de valeur étaient réparties de manière très différentes il pourrait à la rigueur le déduire des statistiques par colonne mais ici (en en général) cest impossible. Vérification avec un exemple simple tiré de la documentation PostgreSQL :
CREATE TABLE t (a INT, b INT);
CREATE TABLE
Temps : 349,746 ms
INSERT INTO t SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i);
INSERT 0 10000
Temps : 74,101 ms
CREATE STATISTICS stts WITH (dependencies) ON (a, b) FROM t;
CREATE STATISTICS
analyze t;
ANALYZE
select * from pg_statistic_ext;
starelid | 57514
staname | stts
stanamespace | 2200
staowner | 10
stakeys | 1 2
staenabled | {f}
standistinct |
stadependencies | [{1 => 2 : 1.000000}, {2 => 1 : 1.000000}]
explain analyze select * from t where a = 1 and b = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual time=0.010..0.799 rows=100 loops=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 9900
Planning time: 0.093 ms
Execution time: 0.819 ms
(5 lignes)
explain analyze select * from t where a = 1 and b = 2;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual time=0.995..0.995 rows=0 loops=1)
Filter: ((a = 1) AND (b = 2))
Rows Removed by Filter: 10000
Planning time: 0.059 ms
Execution time: 1.011 ms
Pour a=1 et b=1, grâce aux statistiques étendues qui indiquent quil existe une dépendance entre a et b, le planner sait que la requête ne va pas retourner 1 ligne mais 100. Attention cependant aux limites que jai évoquées précédemment. Pour a = 1 et b = 2 il croit également que la requête va ramener 100 lignes alors quelle va évidemment en ramener...0. Cependant ce nest pas si grave lorsque le but est déviter de surestimer la sélectivité, comme dans mon exemple du clan des géants.
Conclusion...provisoire
Statistiques multi-colonnes, statistiques étendues : les bases sont posées pour une avancée majeure au niveau des capacités du planner de PostgreSQL. PostgreSQL 10 est encore en développement mais il sera très intéressant de suivre lévolution de la fonctionnalité jusquà sa sortie et au cours des futures versions.
Mise à jour : 23/04/2017