Mettre à jour PostgreSQL pour améliorer les performances
Chaque version majeure apporte des avancées...majeures
Tout changement doit faire l’objet de tests de non régression. Mais, avec PostgreSQL, le risque est souvent payant au niveau des performances.
Nous allons ici nous intéresser aux tris avec des requêtes top-n et 2 versions majeures de PostgreSQL (12 et 13)
Cadre commun (DDL):
create table geants(
idg serial 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,10000000,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 10000000
create table lancers(dtl timestamp, idg integer, perf integer);
CREATE TABLE
WITH serie(i) AS (SELECT generate_series(25000000,1,-1))
insert into lancers(dtl, idg, perf)
select
current_timestamp - (i || ' minutes')::interval,
trunc(random() * 10000000 + 1),
case when random() <= 0.001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end
from serie;
INSERT 0 25000000
create index on lancers(perf, dtl);
CREATE INDEX
analyze geants;
ANALYZE
analyze lancers;
ANALYZE
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+----------------+----------+--------------+------------+-------------
public | geants | table | postgres | 1116 MB |
public | geants_idg_seq | séquence | postgres | 8192 bytes |
public | lancers | table | postgres | 1056 MB |
(3 lignes)
Les tables considérées sont des classiques du site, GEANTS et LANCERS, nous allons devoir trouver :
- les 5 meilleurs lanceurs (ordre de performance descendant)
- les 5 meilleurs lanceurs avec les ex aequo au dernier rang (ordre de performance descendant)
- les 5 meilleurs lanceurs (ordre de performance descendant, ordre de date de lancer ascendant)
- les 5 meilleurs lanceurs (ordre de performance descendant, ordre de taille descendant)
Tout d'abord, test avec PostreSQL 12 :
select version();
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
-- 5 meilleurs lanceurs
explain select idg, perf from lancers order by perf desc fetch first 5 rows only;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..0.90 rows=5 width=8)
-> Index Scan Backward using lancers_perf_dtl_idx on lancers (cost=0.56..1694816.24 rows=25000000 width=8)
select idg, perf from lancers order by perf desc fetch first 5 rows only;
idg | perf
---------+--------
1744023 | 100000
99808 | 100000
2586216 | 99997
9950675 | 99993
4168756 | 99993
(5 lignes)
Temps : 0,649 ms
-- 5 meilleurs lanceurs avec ex aequo dernier rang
explain select idg, perf
from
(select
idg,
perf,
rank() over (order by perf desc)
from lancers) as lancerst
where rank <= 5
order by perf desc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Subquery Scan on lancerst (cost=0.56..2382316.24 rows=8333333 width=8)
Filter: (lancerst.rank <= 5)
-> WindowAgg (cost=0.56..2069816.24 rows=25000000 width=16)
-> Index Scan Backward using lancers_perf_dtl_idx on lancers (cost=0.56..1694816.24 rows=25000000 width=8)
JIT:
Functions: 7
Options: Inlining true, Optimization true, Expressions true, Deforming true
select idg, perf
from
(select
idg,
perf,
rank() over (order by perf desc)
from lancers) as lancerst
where rank <= 5
order by perf desc;
idg | perf
---------+--------
1744023 | 100000
99808 | 100000
2586216 | 99997
9950675 | 99993
4168756 | 99993
7534078 | 99993
(6 lignes)
Durée : 53911,969 ms (00:53,912)
-- 5 meilleurs lanceurs (perf descendante, date ascendante)
explain select idg, taille, perf from lancers join geants using (idg) order by perf desc, dtl asc fetch first 5 rows only;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Limit (cost=910108.11..910108.70 rows=5 width=18)
-> Gather Merge (cost=910108.11..3340833.45 rows=20833334 width=18)
Workers Planned: 2
-> Sort (cost=909108.09..935149.76 rows=10416667 width=18)
Sort Key: lancers.perf DESC, lancers.dtl
-> Parallel Hash Join (cost=252885.57..736091.00 rows=10416667 width=18)
Hash Cond: (lancers.idg = geants.idg)
-> Parallel Seq Scan on lancers (cost=0.00..337858.67 rows=10416667 width=16)
-> Parallel Hash (cost=184524.92..184524.92 rows=4166692 width=6)
-> Parallel Seq Scan on geants (cost=0.00..184524.92 rows=4166692 width=6)
JIT:
Functions: 11
Options: Inlining true, Optimization true, Expressions true, Deforming true
select idg, taille, perf from lancers join geants using (idg) order by perf desc, dtl asc fetch first 5 rows only;
idg | taille | perf
---------+--------+--------
99808 | 382 | 100000
1744023 | 338 | 100000
2586216 | 367 | 99997
7534078 | 275 | 99993
4168756 | 362 | 99993
(5 lignes)
Durée : 3812,313 ms (00:03,812)
drop index lancers_perf_dtl_idx ;
DROP INDEX
create index on lancers(perf desc, dtl asc);
CREATE INDEX
explain select idg, taille, perf from lancers join geants using (idg) order by perf desc, dtl asc fetch first 5 rows only;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=1.00..3.74 rows=5 width=18)
-> Nested Loop (cost=1.00..13688432.15 rows=25000000 width=18)
-> Index Scan using lancers_perf_dtl_idx on lancers (cost=0.56..1694816.24 rows=25000000 width=16)
-> Index Scan using geants_pkey on geants (cost=0.43..0.48 rows=1 width=6)
Index Cond: (idg = lancers.idg)
select idg, taille, perf from lancers join geants using (idg) order by perf desc, dtl asc fetch first 5 rows only;
idg | taille | perf
---------+--------+--------
99808 | 382 | 100000
1744023 | 338 | 100000
2586216 | 367 | 99997
7534078 | 275 | 99993
4168756 | 362 | 99993
(5 lignes)
Temps : 0,864 ms
-- 5 meilleurs lanceurs (perf descendante, taille descendante)
explain select idg, taille, perf from lancers join geants using (idg) order by perf desc, taille desc fetch first 5 rows only;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Limit (cost=889764.11..889764.70 rows=5 width=10)
-> Gather Merge (cost=889764.11..3320489.45 rows=20833334 width=10)
Workers Planned: 2
-> Sort (cost=888764.09..914805.76 rows=10416667 width=10)
Sort Key: lancers.perf DESC, geants.taille DESC
-> Parallel Hash Join (cost=252885.57..715747.00 rows=10416667 width=10)
Hash Cond: (lancers.idg = geants.idg)
-> Parallel Seq Scan on lancers (cost=0.00..337858.67 rows=10416667 width=8)
-> Parallel Hash (cost=184524.92..184524.92 rows=4166692 width=6)
-> Parallel Seq Scan on geants (cost=0.00..184524.92 rows=4166692 width=6)
JIT:
Functions: 11
Options: Inlining true, Optimization true, Expressions true, Deforming true
select idg, taille, perf from lancers join geants using (idg) order by perf desc, taille desc fetch first 5 rows only;
idg | taille | perf
---------+--------+--------
99808 | 382 | 100000
1744023 | 338 | 100000
2586216 | 367 | 99997
4168756 | 362 | 99993
7534078 | 275 | 99993
(5 lignes)
Durée : 3501,299 ms (00:03,501)
Quels enseignements ? PostgreSQL 12 se sort trés bien de la requête consistant à ramener les 5 meilleurs lanceurs grâce à l'index sur lancers(perf, dtl) : moins de 1ms.
En revanche, l'écriture utilisée pour ramener les ex aequo du dernier rang est fonctionnellement correcte mais catastrophique au niveau des performances : la requête utilisant le fenêtrage prend plus de 50s.
La requête triant les lanceurs par performance descendante mais date de lancers ascendante ne tire pas partie de l'index sur lancers(perf, dtl) et prend plus de 3 secondes tout en utilisant beaucoup de ressources (exécution en parallèle). Il faut remplacer l'index par un index spécialisé sur lancers(perf desc, dtl asc) pour obtenir un résultat autour de la milliseconde. Ajouter un nouvel index, remplacer un index existant...cela présente des inconvénients et cela peut même s'avérer impossible si la table est très volumineuse.
Enfin, c'est mauvais lorsqu'on demande un tri à la fois sur les données de geants et lancers.
Il serait bien sûr possible de trouver des astuces et de modifier l'écriture de ces requêtes pour obtenir des performances correctes, par exemple en ne considérant que les performances potentiellement intéressantes. Mais c'est de l'ordre du contournement plutôt que de la solution, il serait préférable de pouvoir s'en passer.
Après mise à jour du cluster, que donne directement PostgreSQL 13 sur toutes ces requêtes ?
pg_upgradecluster 12 -v 13 main
...
drop index lancers_perf_dtl_idx;
DROP INDEX
create index on lancers(perf, dtl);
CREATE INDEX
select version();
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.0 (Ubuntu 13.0-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
-- 5 meilleurs lanceurs
explain select idg, perf from lancers order by perf desc fetch first 5 rows only;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..0.82 rows=5 width=8)
-> Index Scan Backward using lancers_perf_dtl_idx on lancers (cost=0.56..1300593.47 rows=25000000 width=8)
select idg, perf from lancers order by perf desc fetch first 5 rows only;
idg | perf
---------+--------
1744023 | 100000
99808 | 100000
2586216 | 99997
9950675 | 99993
4168756 | 99993
(5 lignes)
Temps : 0,591 ms
-- 5 meilleurs lanceurs avec ex aequo dernier rang
explain select idg, perf
from
(select
idg,
perf,
rank() over (order by perf desc)
from lancers) as lancerst
where rank <= 5
order by perf desc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Subquery Scan on lancerst (cost=0.56..1988093.47 rows=8333333 width=8)
Filter: (lancerst.rank <= 5)
-> WindowAgg (cost=0.56..1675593.47 rows=25000000 width=16)
-> Index Scan Backward using lancers_perf_dtl_idx on lancers (cost=0.56..1300593.47 rows=25000000 width=8)
JIT:
Functions: 7
Options: Inlining true, Optimization true, Expressions true, Deforming true
select idg, perf
from
(select
idg,
perf,
rank() over (order by perf desc)
from lancers) as lancerst
where rank <= 5
order by perf desc;
idg | perf
---------+--------
1744023 | 100000
99808 | 100000
2586216 | 99997
9950675 | 99993
4168756 | 99993
7534078 | 99993
(6 lignes)
Durée : 53176,513 ms (00:53,177)
explain select idg, perf from lancers order by perf desc fetch first 5 rows with ties;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..0.82 rows=5 width=8)
-> Index Scan Backward using lancers_perf_dtl_idx on lancers (cost=0.56..1300593.47 rows=25000000 width=8)
select idg, perf from lancers order by perf desc fetch first 5 rows with ties;
idg | perf
---------+--------
1744023 | 100000
99808 | 100000
2586216 | 99997
9950675 | 99993
4168756 | 99993
7534078 | 99993
(6 lignes)
Temps : 0,716 ms
-- 5 meilleurs lanceurs (perf descendante, date ascendante)
explain select idg, taille, perf from lancers join geants using (idg) order by perf desc, dtl asc fetch first 5 rows only;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Limit (cost=284.36..287.21 rows=5 width=18)
-> Incremental Sort (cost=284.36..14261803.18 rows=25000000 width=18)
Sort Key: lancers.perf DESC, lancers.dtl
Presorted Key: lancers.perf
-> Nested Loop (cost=1.00..13294209.39 rows=25000000 width=18)
-> Index Scan Backward using lancers_perf_dtl_idx on lancers (cost=0.56..1300593.47 rows=25000000 width=16)
-> Index Scan using geants_pkey on geants (cost=0.43..0.48 rows=1 width=6)
Index Cond: (idg = lancers.idg)
select idg, taille, perf from lancers join geants using (idg) order by perf desc, dtl asc fetch first 5 rows only;
idg | taille | perf
---------+--------+--------
99808 | 382 | 100000
1744023 | 338 | 100000
2586216 | 367 | 99997
7534078 | 275 | 99993
4168756 | 362 | 99993
(5 lignes)
Temps : 1,013 ms
drop index lancers_perf_dtl_idx ;
DROP INDEX
create index on lancers(perf desc, dtl asc);
CREATE INDEX
explain select idg, taille, perf from lancers join geants using (idg) order by perf desc, dtl asc fetch first 5 rows only;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=1.00..3.66 rows=5 width=18)
-> Nested Loop (cost=1.00..13294209.39 rows=25000000 width=18)
-> Index Scan using lancers_perf_dtl_idx on lancers (cost=0.56..1300593.47 rows=25000000 width=16)
-> Index Scan using geants_pkey on geants (cost=0.43..0.48 rows=1 width=6)
Index Cond: (idg = lancers.idg)
select idg, taille, perf from lancers join geants using (idg) order by perf desc, dtl asc fetch first 5 rows only;
idg | taille | perf
---------+--------+--------
99808 | 382 | 100000
1744023 | 338 | 100000
2586216 | 367 | 99997
7534078 | 275 | 99993
4168756 | 362 | 99993
(5 lignes)
Temps : 0,969 ms
-- 5 meilleurs lanceurs (perf descendante, taille descendante)
explain select idg, taille, perf from lancers join geants using (idg) order by perf desc, taille desc fetch first 5 rows only;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=284.36..287.21 rows=5 width=10)
-> Incremental Sort (cost=284.36..14261803.18 rows=25000000 width=10)
Sort Key: lancers.perf DESC, geants.taille DESC
Presorted Key: lancers.perf
-> Nested Loop (cost=1.00..13294209.39 rows=25000000 width=10)
-> Index Scan using lancers_perf_dtl_idx on lancers (cost=0.56..1300593.47 rows=25000000 width=8)
-> Index Scan using geants_pkey on geants (cost=0.43..0.48 rows=1 width=6)
Index Cond: (idg = lancers.idg)
select idg, taille, perf from lancers join geants using (idg) order by perf desc, taille desc fetch first 5 rows only;
idg | taille | perf
---------+--------+--------
99808 | 382 | 100000
1744023 | 338 | 100000
2586216 | 367 | 99997
4168756 | 362 | 99993
7534078 | 275 | 99993
(5 lignes)
Temps : 0,965 ms
Quels enseignements ? PostgreSQL 13 se sort toujours aussi bien de la requête consistant à ramener les 5 meilleurs lanceurs grâce à l'index sur lancers(perf, dtl) : moins de 1ms.
L'écriture analytique utilisée pour ramener les ex aequo du dernier rang est toujours fonctionnellement correcte et toujours catastrophique au niveau des performances (plus de 50s). Cependant, cette écriture n'est plus nécessaire puisque PostgreSQL 13 gère la syntaxe WITH TIES, n'hésitez pas à en tirer parti (moins de 1ms)
La requête triant les lanceurs par performance descendante mais date de lancers ascendante est à présent performante sans avoir besoin de transformer l'index grâce aux tris incrémentaux de PostgreSQL 13 (Incremental Sort, autour de 1ms dans l'exemple).
De même, c'est tout bon lorsqu'on demande un tri à la fois sur les données de geants et lancers, toujours grâce au tri incrémental (moins de 1ms).
Dans ces exemples, PostgreSQL 13 permet toujours de renvoyer le résultat en 1 milliseconde ou moins avec une écriture simple et directe là où PostgreSQL 12 peinait parfois avec des temps de réponse de plus de 3 secondes et même de plus de 50s pour obtenir les ex aequo.
Conclusion
Obtenir des résultats triés est une fonctionnalité essentielle d'un SGBD et PostgreSQL 13 apporte des améliorations majeures de performance par rapport aux version précédentes sur cet aspect. Ne passez pas à côté de PostgreSQL 13, cette version ne comporte pas de nouvelle fonctionnalité spectaculaire mais comporte bien d'autres optimisations et améliorations, notamment sur le partitionnement et l'indexation.
Mise à jour : 27/09/2020