Statistiques étendues
(sujet préalablement traité avec les versions 10 devel, 10 beta, 11 devel et mis à jour avec la version 12 beta)
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 :
-- note : si vous voulez reproduire les exemples de cette page, attention d'utiliser une version 10.2 ou supérieure
select version();
version
---------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.2 (Debian 10.2-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 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 | | 40063
NOIR | MOYEN | f | LICORNE | f | 29968
GRIS | PETIT | t | TAUREAU | t | 29964
BLEU | MOYEN | f | LICORNE | f | 2
NOIR | MOYEN | f | TAUREAU | t | 2
NOIR | PETIT | t | TAUREAU | t | 1
(6 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
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=1498760.84..1499570.65 rows=239 width=69)
Group Key: g.idg
-> Gather Merge (cost=1498760.84..1499565.27 rows=478 width=69)
Workers Planned: 2
-> Partial GroupAggregate (cost=1497760.81..1498510.07 rows=239 width=69)
Group Key: g.idg
-> Sort (cost=1497760.81..1498009.77 rows=99583 width=41)
Sort Key: g.idg
-> Hash Join (cost=3181.99..1486427.62 rows=99583 width=41)
Hash Cond: (l.idg = g.idg)
-> Parallel Seq Scan on lancers l (cost=0.00..957207.67 rows=41666667 width=8)
-> Hash (cost=3179.00..3179.00 rows=239 width=37)
-> Seq Scan on geants g (cost=0.00..3179.00 rows=239 width=37)
Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text))
(14 lignes)
\timing
Chronométrage activé.
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;
Durée : 162036,395 ms (02:42,036)
...
Durée : 168042,732 ms (02:48,043)
...
Durée : 169756,361 ms (02:49,756)
Lexécution de la requête prend ici environ 2 minutes 50 secondes. 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 239 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é. 29964 géants sont en effet concernés.
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 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.66..329590.56 rows=239 width=69)
Group Key: g.idg
-> Gather Merge (cost=1024.66..329585.19 rows=478 width=69)
Workers Planned: 2
-> Partial GroupAggregate (cost=24.64..328529.99 rows=239 width=69)
Group Key: g.idg
-> Nested Loop (cost=24.64..328029.68 rows=99583 width=41)
-> Parallel Index Scan using geants_pkey on geants g (cost=0.29..3765.46 rows=100 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.35..3232.58 rows=1006 width=8)
Recheck Cond: (idg = g.idg)
-> Bitmap Index Scan on lancers_i1 (cost=0.00..24.10 rows=1006 width=0)
Index Cond: (idg = g.idg)
(13 lignes)
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;
Durée : 3107076,821 ms (51:47,077)
Lexemple met à mal une idée communément répandue : "au pire, ajouter un index pénalise un peu les écritures mais jamais les lectures". Ce nest PAS toujours vrai.
Ici, la durée explose et atteint presque 52 minutes sur mon environnement aux I/O peu performantes !
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.
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
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=1487997.00..1488003.57 rows=239 width=69)
Group Key: g.idg
-> Sort (cost=1487997.00..1487998.20 rows=478 width=69)
Sort Key: g.idg
-> Gather (cost=1487925.54..1487975.73 rows=478 width=69)
Workers Planned: 2
-> Partial HashAggregate (cost=1486925.54..1486927.93 rows=239 width=69)
Group Key: g.idg
-> Hash Join (cost=3181.99..1486427.62 rows=99583 width=41)
Hash Cond: (l.idg = g.idg)
-> Parallel Seq Scan on lancers l (cost=0.00..957207.67 rows=41666667 width=8)
-> Hash (cost=3179.00..3179.00 rows=239 width=37)
-> Seq Scan on geants g (cost=0.00..3179.00 rows=239 width=37)
Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text))
(14 lignes)
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;
Durée : 59002,740 ms (00:59,003)
...
Durée : 51927,907 ms (00:51,928)
...
Durée : 56353,209 ms (00:56,353)
Le plan est un peu différent au niveau de lexécution du regroupement (HashAggregate au lieu de GroupAggregate) mais, surtout, nous retrouvons une jointure par hachage (HASH JOIN). Le temps dexécution passe à moins dune minute. Le hic est que nous avons obtenu un peu artificiellement ce plan favorable. Il serait intéressant de lobtenir en fournissant linformation dont nous disposons et que le planner ignore, 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, attention, sont effectivement calculées par la commande ANALYZE :
create statistics geants_couleur_gabarit_ere_berserk_cyclope(dependencies, ndistinct) on couleur, gabarit, ere, berserk, cyclope from geants;
CREATE STATISTICS
Temps : 368,188 ms
analyze geants;
ANALYZE
Durée : 3188,836 ms (00:03,189)
\x
Affichage étendu activé.
select * from pg_statistic_ext;
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
stxrelid | 1452548
stxname | geants_couleur_gabarit_ere_berserk_cyclope
stxnamespace | 2200
stxowner | 10
stxkeys | 9 12 15 17 18
stxkind | {d,f}
stxndistinct | {"9, 12": 4, "9, 15": 4, "9, 17": 4, "9, 18": 4, "12, 15": 3, "12, 17": 3, "12, 18": 3, "15, 17": 3, "15, 18": 3, "17, 18": 3, "9, 12, 15": 4, "9, 12, 17": 4, "9, 12, 18": 4, "9, 15, 17": 4, "9, 15, 18": 4, "9, 17, 18": 4, "12, 15, 17": 3, "12, 15, 18": 3, "12, 17, 18": 3, "15, 17, 18": 3, "9, 12, 15, 17": 4, "9, 12, 15, 18": 4, "9, 12, 17, 18": 4, "9, 15, 17, 18": 4, "12, 15, 17, 18": 3, "9, 12, 15, 17, 18": 4}
stxdependencies | {"9 => 12": 0.597333, "9 => 15": 0.597333, "9 => 17": 0.597333, "9 => 18": 0.597333, "12 => 9": 0.701633, "12 => 15": 1.000000, "12 => 17": 1.000000, "12 => 18": 1.000000, "15 => 9": 0.701633, "15 => 12": 1.000000, "15 => 17": 1.000000, "15 => 18": 1.000000, "17 => 9": 0.701633, "17 => 12": 1.000000, "17 => 15": 1.000000, "17 => 18": 1.000000, "18 => 9": 0.701633, "18 => 12": 1.000000, "18 => 15": 1.000000, "18 => 17": 1.000000, "9, 12 => 15": 1.000000, "9, 12 => 17": 1.000000, "9, 12 => 18": 1.000000, "9, 15 => 12": 1.000000, "9, 15 => 17": 1.000000, "9, 15 => 18": 1.000000, "9, 17 => 12": 1.000000, "9, 17 => 15": 1.000000, "9, 17 => 18": 1.000000, "9, 18 => 12": 1.000000, "9, 18 => 15": 1.000000, "9, 18 => 17": 1.000000, "12, 15 => 9": 0.701633, "12, 15 => 17": 1.000000, "12, 15 => 18": 1.000000, "12, 17 => 9": 0.701633, "12, 17 => 15": 1.000000, "12, 17 => 18": 1.000000, "12, 18 => 9": 0.701633, "12, 18 => 15": 1.000000, "12, 18 => 17": 1.000000, "15, 17 => 9": 0.701633, "15, 17 => 12": 1.000000, "15, 17 => 18": 1.000000, "15, 18 => 9": 0.701633, "15, 18 => 12": 1.000000, "15, 18 => 17": 1.000000, "17, 18 => 9": 0.701633, "17, 18 => 12": 1.000000, "17, 18 => 15": 1.000000, "9, 12, 15 => 17": 1.000000, "9, 12, 15 => 18": 1.000000, "9, 12, 17 => 15": 1.000000, "9, 12, 17 => 18": 1.000000, "9, 12, 18 => 15": 1.000000, "9, 12, 18 => 17": 1.000000, "9, 15, 17 => 12": 1.000000, "9, 15, 17 => 18": 1.000000, "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": 0.701633, "12, 15, 17 => 18": 1.000000, "12, 15, 18 => 9": 0.701633, "12, 15, 18 => 17": 1.000000, "12, 17, 18 => 9": 0.701633, "12, 17, 18 => 15": 1.000000, "15, 17, 18 => 9": 0.701633, "15, 17, 18 => 12": 1.000000, "9, 12, 15, 17 => 18": 1.000000, "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": 0.701633}
\x
Affichage étendu désactivé.
La colonne stxkeys 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 stxndistinct nous informe par exemple via "12, 15": 3 quil existe 3 combinaisons GABARIT, BERSERK significatives.
Enfin, la colonne stxdependencies nous informe par exemple via "12 => 15": 1.000000 que connaître la valeur de la colonne GABARIT pour une ligne permet de déduire la valeur de la colonne BERSERK.
Vous pouvez constater pour dautres combinaisons que les statistiques comprennent des approximations. La théorie cest bien mais est-ce que ça marche ? Et bien OUI.
Démonstration en reprenant lexemple précédent :
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=1547562.93..1548213.20 rows=23646 width=69)
Group Key: g.idg
-> Sort (cost=1547562.93..1547681.16 rows=47292 width=69)
Sort Key: g.idg
-> Gather (cost=1536982.71..1541948.37 rows=47292 width=69)
Workers Planned: 2
-> Partial HashAggregate (cost=1535982.71..1536219.17 rows=23646 width=69)
Group Key: g.idg
-> Hash Join (cost=3474.57..1486720.21 rows=9852500 width=41)
Hash Cond: (l.idg = g.idg)
-> Parallel Seq Scan on lancers l (cost=0.00..957207.67 rows=41666667 width=8)
-> Hash (cost=3179.00..3179.00 rows=23646 width=37)
-> Seq Scan on geants g (cost=0.00..3179.00 rows=23646 width=37)
Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text))
(14 lignes)
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;
Durée : 53648,020 ms (00:53,648)
...
Durée : 53267,478 ms (00:53,267)
...
Durée : 52897,687 ms (00:52,898)
Lestimation du nombre de lignes passe de 239 lignes à 23646 lignes. Cela reste assez éloigné de la valeur réelle de 29964 mais cest néanmoins suffisant pour retrouver le plan performant que nous avions obtenu en jouant sur random_page_cost.
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 (et 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
INSERT INTO t SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i);
INSERT 0 10000
CREATE STATISTICS stts(dependencies) ON a, b FROM t;
CREATE STATISTICS
analyze t;
ANALYZE
select * from pg_statistic_ext;
stxrelid | stxname | stxnamespace | stxowner | stxkeys | stxkind | stxndistinct | stxdependencies
----------+---------+--------------+----------+---------+---------+--------------+------------------------------------------
1452561 | stts | 2200 | 10 | 1 2 | {f} | | {"1 => 2": 1.000000, "2 => 1": 1.000000}
(1 ligne)
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.026..2.042 rows=100 loops=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 9900
Planning time: 0.256 ms
Execution time: 2.223 ms
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=1.869..1.869 rows=0 loops=1)
Filter: ((a = 1) AND (b = 2))
Rows Removed by Filter: 10000
Planning time: 0.137 ms
Execution time: 1.908 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 : la fonctionnalité introduite en version 10 est pleinement utilisable. Elle donne un moyen supplémentaire de donner de linformation au planner afin dobtenir un plan dexécution optimal.
Mise à jour : 10/02/2018