select version();
version
--------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.9 (Debian 17.9-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 ligne)
create table geants(idg smallint generated always as identity primary key, nmg character varying(32), actif boolean);
CREATE TABLE
insert into geants(nmg, actif) select upper(md5(random()::text)), case when random() < 0.7 then true else false end from generate_series(1,54);
INSERT 0 54
create table lancers(dtl timestamp, idg smallint, perf integer);
CREATE TABLE
with recursive serie(i) as (select 576000 UNION ALL select i - 1 from serie where i > 1)
insert into lancers(dtl, idg, perf)
select current_timestamp - (i || ' minutes')::interval,
trunc(random() * 54 + 1),
case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end
from serie ;
INSERT 0 576000
create index lancers_i1 on lancers(idg);
CREATE INDEX
create index lancers_i2 on lancers(perf, dtl);
CREATE INDEX
analyze geants;
ANALYZE
analyze lancers;
ANALYZE
explain select l.idg, l.dtl, l.perf
from lancers l
join
(select g.idg idg, max(l.perf) perf
from geants g join
lancers l
on (g.idg = l.idg)
where g.actif = 't'
group by g.idg) perf_max
on (l.idg = perf_max.idg and l.perf = perf_max.perf)
order by 3 desc, 2 desc
limit 8;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=9999.92..9999.94 rows=8 width=14)
-> Sort (cost=9999.92..9999.95 rows=9 width=14)
Sort Key: l.perf DESC, l.dtl DESC
-> Nested Loop (cost=8064.00..9999.78 rows=9 width=14)
-> Finalize GroupAggregate (cost=8063.58..8073.46 rows=39 width=6)
Group Key: g.idg
-> Gather Merge (cost=8063.58..8072.68 rows=78 width=6)
Workers Planned: 2
-> Sort (cost=7063.55..7063.65 rows=39 width=6)
Sort Key: g.idg
-> Partial HashAggregate (cost=7062.13..7062.52 rows=39 width=6)
Group Key: g.idg
-> Hash Join (cost=2.03..6195.47 rows=173333 width=6)
Hash Cond: (l_1.idg = g.idg)
-> Parallel Seq Scan on lancers l_1 (cost=0.00..5514.00 rows=240000 width=6)
-> Hash (cost=1.54..1.54 rows=39 width=2)
-> Seq Scan on geants g (cost=0.00..1.54 rows=39 width=2)
Filter: actif
-> Index Scan using lancers_i2 on lancers l (cost=0.42..49.38 rows=1 width=14)
Index Cond: (perf = (max(l_1.perf)))
Filter: (g.idg = idg)
select l.idg, l.dtl, l.perf
from lancers l
join
(select g.idg idg, max(l.perf) perf
from geants g join
lancers l
on (g.idg = l.idg)
where g.actif = 't'
group by g.idg) perf_max
on (l.idg = perf_max.idg and l.perf = perf_max.perf)
order by 3 desc, 2 desc
limit 8;
idg | dtl | perf
-----+-----------------------+-------
18 | 2025-07-21 15:29:41.5 | 96739
1 | 2025-05-23 15:07:41.5 | 94318
48 | 2025-10-09 20:49:41.5 | 93693
7 | 2026-03-09 03:38:41.5 | 92628
10 | 2025-06-14 01:10:41.5 | 91239
33 | 2025-05-30 15:27:41.5 | 88503
21 | 2025-12-04 03:39:41.5 | 88341
54 | 2025-07-22 15:24:41.5 | 86705
(8 lignes)
Temps : 33,859 ms
Temps : 32,412 ms
Temps : 34,014 ms
Pas mal mais cette requête semble lente. Margiono se demande si une indexation différente pourrait donner de meilleures performances. Il sait en effet qu’Oumpfor n’est pas spécialement patient. Mais avant d’ajouter ou modifier des index, ce qui est toujours coûteux, il décide de demander à un totem, " Artificial Intello" (AI pour les intimes), son avis. Le totem répond sans barguigner :
explain WITH meilleurs_lancers AS (
SELECT
l.idg,
l.perf,
l.dtl,
ROW_NUMBER() OVER (
PARTITION BY l.idg
ORDER BY l.perf DESC, l.dtl DESC
) AS rn
FROM lancers l
INNER JOIN geants g ON l.idg = g.idg
WHERE g.actif = TRUE
)
SELECT
g.idg,
g.nmg,
ml.perf,
ml.dtl
FROM geants g
INNER JOIN meilleurs_lancers ml ON g.idg = ml.idg
WHERE ml.rn = 1
ORDER BY ml.perf DESC, ml.dtl DESC
LIMIT 8
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Limit (cost=70340.04..70340.06 rows=8 width=47)
-> Sort (cost=70340.04..70345.24 rows=2080 width=47)
Sort Key: ml.perf DESC, ml.dtl DESC
-> Hash Join (cost=1067.23..70298.44 rows=2080 width=47)
Hash Cond: (ml.idg = g.idg)
-> Subquery Scan on ml (cost=1065.02..70290.34 rows=2080 width=14)
Filter: (ml.rn = 1)
-> WindowAgg (cost=1065.02..65090.34 rows=416000 width=22)
Run Condition: (row_number() OVER (?) <= 1)
-> Incremental Sort (cost=1064.87..56770.34 rows=416000 width=14)
Sort Key: l.idg, l.perf DESC, l.dtl DESC
Presorted Key: l.idg
-> Nested Loop (cost=111.95..24713.68 rows=416000 width=14)
-> Index Scan using geants_pkey on geants g_1 (cost=0.14..12.95 rows=39 width=2)
Filter: actif
-> Bitmap Heap Scan on lancers l (cost=111.81..526.68 rows=10667 width=14)
Recheck Cond: (idg = g_1.idg)
-> Bitmap Index Scan on lancers_i1 (cost=0.00..109.15 rows=10667 width=0)
Index Cond: (idg = g_1.idg)
-> Hash (cost=1.54..1.54 rows=54 width=35)
-> Seq Scan on geants g (cost=0.00..1.54 rows=54 width=35)
WITH meilleurs_lancers AS (
SELECT
l.idg,
l.perf,
l.dtl,
ROW_NUMBER() OVER (
PARTITION BY l.idg
ORDER BY l.perf DESC, l.dtl DESC
) AS rn
FROM lancers l
INNER JOIN geants g ON l.idg = g.idg
WHERE g.actif = TRUE
)
SELECT
g.idg,
g.nmg,
ml.perf,
ml.dtl
FROM geants g
INNER JOIN meilleurs_lancers ml ON g.idg = ml.idg
WHERE ml.rn = 1
ORDER BY ml.perf DESC, ml.dtl DESC
LIMIT 8;
idg | nmg | perf | dtl
-----+----------------------------------+-------+-----------------------
18 | B8CCC0671B37033F0658C228DE0FE00A | 96739 | 2025-07-21 15:29:41.5
1 | 8C238B96C24AA9AEF47CFFD9C50163DA | 94318 | 2025-05-23 15:07:41.5
48 | F4046B4A21A99BE8A26DAF5FF29F49CF | 93693 | 2025-10-09 20:49:41.5
7 | 93B0EE0CBACC761C98015694433B1B21 | 92628 | 2026-03-09 03:38:41.5
10 | E873B2DF0C7BB42D21F791503907F07F | 91239 | 2025-06-14 01:10:41.5
33 | E0C096E18E57BE565DA3790AFB0B46E7 | 88503 | 2025-05-30 15:27:41.5
21 | A856E35CA0E64A4F0866897D4606970E | 88341 | 2025-12-04 03:39:41.5
54 | 60CACD61B996EB383134D68AE1E0F07A | 86705 | 2025-07-22 15:24:41.5
(8 lignes)
Temps : 185,511 ms
Temps : 180,544 ms
Temps : 187,718 ms
Pas terrible, c'est beaucoup plus lent, l'intelligence artificielle a visiblement ses limites... Margiono a une information essentielle qu'il aimerait utiliser : chaque géant a réalisé au moins une fois une performance exceptionnelle, de plus de 500 mètres, pour être admis dans le clan. Par ailleurs, ce genre de performance est rare. Il faut absolument utiliser l'index lancers_i2 en tirant parti de ces deux informations. Margiono décide de regarder du côté des jointures latérales, possibilité introduite par le standard SQL:1999 et implémentée depuis PostgreSQL 9.3 :
explain
select g.idg, g.nmg, l.dtl, l.perf
from
(select idg, nmg from geants where actif = true) g
join lateral
(select perf, dtl from lancers where g.idg = idg order by perf desc, dtl desc fetch first 1 rows only) l
on (true)
order by perf desc, dtl desc
fetch first 8 rows only;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit (cost=134.54..134.56 rows=8 width=47)
-> Sort (cost=134.54..134.63 rows=39 width=47)
Sort Key: lancers.perf DESC, lancers.dtl DESC
-> Nested Loop (cost=0.42..133.76 rows=39 width=47)
-> Seq Scan on geants (cost=0.00..1.54 rows=39 width=35)
Filter: actif
-> Limit (cost=0.42..3.37 rows=1 width=12)
-> Index Scan Backward using lancers_i2 on lancers (cost=0.42..31416.42 rows=10667 width=12)
Filter: (geants.idg = idg)
select g.idg, g.nmg, l.dtl, l.perf
from
(select idg, nmg from geants where actif = true) g
join lateral
(select perf, dtl from lancers where g.idg = idg order by perf desc, dtl desc fetch first 1 rows only) l
on (true)
order by perf desc, dtl desc
fetch first 8 rows only;
idg | nmg | dtl | perf
-----+----------------------------------+-----------------------+-------
18 | B8CCC0671B37033F0658C228DE0FE00A | 2025-07-21 15:29:41.5 | 96739
1 | 8C238B96C24AA9AEF47CFFD9C50163DA | 2025-05-23 15:07:41.5 | 94318
48 | F4046B4A21A99BE8A26DAF5FF29F49CF | 2025-10-09 20:49:41.5 | 93693
7 | 93B0EE0CBACC761C98015694433B1B21 | 2026-03-09 03:38:41.5 | 92628
10 | E873B2DF0C7BB42D21F791503907F07F | 2025-06-14 01:10:41.5 | 91239
33 | E0C096E18E57BE565DA3790AFB0B46E7 | 2025-05-30 15:27:41.5 | 88503
21 | A856E35CA0E64A4F0866897D4606970E | 2025-12-04 03:39:41.5 | 88341
54 | 60CACD61B996EB383134D68AE1E0F07A | 2025-07-22 15:24:41.5 | 86705
(8 lignes)
Temps : 1,836 ms
Temps : 1,533 ms
Temps : 0,887 ms
Bonne pioche de la part de Margiono. PostgreSQL estimait que le coût de la requête utilisant LATERAL serait largement inférieur à ceux des requêtes précédentes et il avait raison. À l’exécution, les temps de réponse obtenus se sont en effet révélés être largement meilleurs.
Qu’apporte ce type de jointure par rapport à une jointure habituelle ? Le mot clé LATERAL autorise ici à faire référence à une colonne de GEANTS dans la sous-requête effectuée sur la table LANCERS. Il s’agit donc de fait d’une boucle de type FOR EACH. Pour chaque géant actif, nous cherchons la meilleure performance et la date à laquelle elle a été réalisée via l’index lancers_i2 : c’est bien le fait de limiter le jeu de résultats de la sous-requête à la "top perf" de chaque géant qui rend ici la jointure latérale si efficace.
La satisfaction est toutefois de courte durée. Margiono apprend que des "pistonnés" vont pouvoir entrer dans le clan. Deux jumeaux, "faiblesbras" et "brascasses", sont les premiers concernés ! Ils ne lancent quasi jamais et leurs performances, minables, sont ajoutées dans ses registres. Est-ce que cela change la donne ?
insert into geants(nmg, actif) values('faiblesbras', true),('brascasses', true);
INSERT 0 2
insert into lancers values(current_date-365, 55, 150), (current_date-366, 56, 200);
INSERT 0 2
select l.idg, l.dtl, l.perf
from lancers l
join
(select g.idg idg, max(l.perf) perf
from geants g join
lancers l
on (g.idg = l.idg)
where g.actif = 't'
group by g.idg) perf_max
on (l.idg = perf_max.idg and l.perf = perf_max.perf)
order by 3 desc, 2 desc
limit 8;
idg | dtl | perf
-----+-----------------------+-------
18 | 2025-07-21 15:29:41.5 | 96739
1 | 2025-05-23 15:07:41.5 | 94318
48 | 2025-10-09 20:49:41.5 | 93693
7 | 2026-03-09 03:38:41.5 | 92628
10 | 2025-06-14 01:10:41.5 | 91239
33 | 2025-05-30 15:27:41.5 | 88503
21 | 2025-12-04 03:39:41.5 | 88341
54 | 2025-07-22 15:24:41.5 | 86705
(8 lignes)
Temps : 33,409 ms
Temps : 32,792 ms
Temps : 33,282 ms
WITH meilleurs_lancers AS (
SELECT
l.idg,
l.perf,
l.dtl,
ROW_NUMBER() OVER (
PARTITION BY l.idg
ORDER BY l.perf DESC, l.dtl DESC
) AS rn
FROM lancers l
INNER JOIN geants g ON l.idg = g.idg
WHERE g.actif = TRUE
)
SELECT
g.idg,
g.nmg,
ml.perf,
ml.dtl
FROM geants g
INNER JOIN meilleurs_lancers ml ON g.idg = ml.idg
WHERE ml.rn = 1
ORDER BY ml.perf DESC, ml.dtl DESC
LIMIT 8;
idg | nmg | perf | dtl
-----+----------------------------------+-------+-----------------------
18 | B8CCC0671B37033F0658C228DE0FE00A | 96739 | 2025-07-21 15:29:41.5
1 | 8C238B96C24AA9AEF47CFFD9C50163DA | 94318 | 2025-05-23 15:07:41.5
48 | F4046B4A21A99BE8A26DAF5FF29F49CF | 93693 | 2025-10-09 20:49:41.5
7 | 93B0EE0CBACC761C98015694433B1B21 | 92628 | 2026-03-09 03:38:41.5
10 | E873B2DF0C7BB42D21F791503907F07F | 91239 | 2025-06-14 01:10:41.5
33 | E0C096E18E57BE565DA3790AFB0B46E7 | 88503 | 2025-05-30 15:27:41.5
21 | A856E35CA0E64A4F0866897D4606970E | 88341 | 2025-12-04 03:39:41.5
54 | 60CACD61B996EB383134D68AE1E0F07A | 86705 | 2025-07-22 15:24:41.5
(8 lignes)
Temps : 199,493 ms
Temps : 175,865 ms
Temps : 188,694 ms
select g.idg, g.nmg, l.dtl, l.perf
from
(select idg, nmg from geants where actif = true) g
join lateral
(select perf, dtl from lancers where g.idg = idg order by perf desc, dtl desc fetch first 1 rows only) l
on (true)
order by perf desc, dtl desc
fetch first 8 rows only;
idg | nmg | dtl | perf
-----+----------------------------------+-----------------------+-------
18 | B8CCC0671B37033F0658C228DE0FE00A | 2025-07-21 15:29:41.5 | 96739
1 | 8C238B96C24AA9AEF47CFFD9C50163DA | 2025-05-23 15:07:41.5 | 94318
48 | F4046B4A21A99BE8A26DAF5FF29F49CF | 2025-10-09 20:49:41.5 | 93693
7 | 93B0EE0CBACC761C98015694433B1B21 | 2026-03-09 03:38:41.5 | 92628
10 | E873B2DF0C7BB42D21F791503907F07F | 2025-06-14 01:10:41.5 | 91239
33 | E0C096E18E57BE565DA3790AFB0B46E7 | 2025-05-30 15:27:41.5 | 88503
21 | A856E35CA0E64A4F0866897D4606970E | 2025-12-04 03:39:41.5 | 88341
54 | 60CACD61B996EB383134D68AE1E0F07A | 2025-07-22 15:24:41.5 | 86705
(8 lignes)
Temps : 341,696 ms
Temps : 333,817 ms
Temps : 322,097 ms
Catastrophe ! Les deux pistonnés et leurs performances minables ont "pourri" les performances de sa belle requête, devenue pire que celles des requétes de Vincent et du totem "AI". Cette fois, Margiono sait qu'il n'y coupera pas. Il va devoir changer l'indexation mais que faire ? Ajouter un index lancers_i3 sur lancers(idg, dtl, perf) ? Hmmm...pourquoi pas, cela permettra de trouvera rapidement ces intrus dans l'index en n'ayant pas à le balayer de façon interminable pour trouver leur "meilleur" lancer ! Mais lancers_i3 va alors ressembler beaucoup à lancers_i1 ET lancers_i2 réunis qui prendront de la place pour rien ? Il décide, mauvaise pratique, de supprimer non seulement lancers_i1 qui est compris dans lancers_i3 en lisant les définitions (colonnes) de gauche à droite mais aussi lancers_i2 qui ne l'est pas et advienne que pourra :
create index lancers_i3 on lancers(idg, perf, dtl);
CREATE INDEX
drop index lancers_i1;
DROP INDEX
drop index lancers_i2;
DROP INDEX
explain select l.idg, l.dtl, l.perf
from lancers l
join
(select g.idg idg, max(l.perf) perf
from geants g join
lancers l
on (g.idg = l.idg)
where g.actif = 't'
group by g.idg) perf_max
on (l.idg = perf_max.idg and l.perf = perf_max.perf)
order by 3 desc, 2 desc
limit 8;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=8271.06..8271.08 rows=8 width=14)
-> Sort (cost=8271.06..8271.08 rows=9 width=14)
Sort Key: l.perf DESC, l.dtl DESC
-> Nested Loop (cost=8074.30..8270.91 rows=9 width=14)
-> Finalize GroupAggregate (cost=8073.87..8084.26 rows=41 width=6)
Group Key: g.idg
-> Gather Merge (cost=8073.87..8083.44 rows=82 width=6)
Workers Planned: 2
-> Sort (cost=7073.85..7073.95 rows=41 width=6)
Sort Key: g.idg
-> Partial HashAggregate (cost=7072.34..7072.75 rows=41 width=6)
Group Key: g.idg
-> Hash Join (cost=2.07..6193.77 rows=175715 width=6)
Hash Cond: (l_1.idg = g.idg)
-> Parallel Seq Scan on lancers l_1 (cost=0.00..5514.01 rows=240001 width=6)
-> Hash (cost=1.56..1.56 rows=41 width=2)
-> Seq Scan on geants g (cost=0.00..1.56 rows=41 width=2)
Filter: actif
-> Index Only Scan using lancers_i3 on lancers l (cost=0.42..4.54 rows=1 width=14)
Index Cond: ((idg = g.idg) AND (perf = (max(l_1.perf))))
select l.idg, l.dtl, l.perf
from lancers l
join
(select g.idg idg, max(l.perf) perf
from geants g join
lancers l
on (g.idg = l.idg)
where g.actif = 't'
group by g.idg) perf_max
on (l.idg = perf_max.idg and l.perf = perf_max.perf)
order by 3 desc, 2 desc
limit 8;
idg | dtl | perf
-----+-----------------------+-------
18 | 2025-07-21 15:29:41.5 | 96739
1 | 2025-05-23 15:07:41.5 | 94318
48 | 2025-10-09 20:49:41.5 | 93693
7 | 2026-03-09 03:38:41.5 | 92628
10 | 2025-06-14 01:10:41.5 | 91239
33 | 2025-05-30 15:27:41.5 | 88503
21 | 2025-12-04 03:39:41.5 | 88341
54 | 2025-07-22 15:24:41.5 | 86705
(8 lignes)
Temps : 33,387 ms
Temps : 33,862 ms
Temps : 35,026 ms
explain WITH meilleurs_lancers AS (
SELECT
l.idg,
l.perf,
l.dtl,
ROW_NUMBER() OVER (
PARTITION BY l.idg
ORDER BY l.perf DESC, l.dtl DESC
) AS rn
FROM lancers l
INNER JOIN geants g ON l.idg = g.idg
WHERE g.actif = TRUE
)
SELECT
g.idg,
g.nmg,
ml.perf,
ml.dtl
FROM geants g
INNER JOIN meilleurs_lancers ml ON g.idg = ml.idg
WHERE ml.rn = 1
ORDER BY ml.perf DESC, ml.dtl DESC
LIMIT 8;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=63324.25..63324.27 rows=8 width=46)
-> Sort (cost=63324.25..63329.52 rows=2109 width=46)
Sort Key: ml.perf DESC, ml.dtl DESC
-> Hash Join (cost=823.24..63282.07 rows=2109 width=46)
Hash Cond: (ml.idg = g.idg)
-> Subquery Scan on ml (cost=820.98..63273.85 rows=2109 width=14)
Filter: (ml.rn = 1)
-> WindowAgg (cost=820.98..58002.40 rows=421716 width=22)
Run Condition: (row_number() OVER (?) <= 1)
-> Incremental Sort (cost=820.84..49568.08 rows=421716 width=14)
Sort Key: l.idg, l.perf DESC, l.dtl DESC
Presorted Key: l.idg
-> Nested Loop (cost=0.56..17029.45 rows=421716 width=14)
-> Index Scan using geants_pkey on geants g_1 (cost=0.14..12.98 rows=41 width=2)
Filter: actif
-> Index Only Scan using lancers_i3 on lancers l (cost=0.42..308.37 rows=10667 width=14)
Index Cond: (idg = g_1.idg)
-> Hash (cost=1.56..1.56 rows=56 width=34)
-> Seq Scan on geants g (cost=0.00..1.56 rows=56 width=34)
WITH meilleurs_lancers AS (
SELECT
l.idg,
l.perf,
l.dtl,
ROW_NUMBER() OVER (
PARTITION BY l.idg
ORDER BY l.perf DESC, l.dtl DESC
) AS rn
FROM lancers l
INNER JOIN geants g ON l.idg = g.idg
WHERE g.actif = TRUE
)
SELECT
g.idg,
g.nmg,
ml.perf,
ml.dtl
FROM geants g
INNER JOIN meilleurs_lancers ml ON g.idg = ml.idg
WHERE ml.rn = 1
ORDER BY ml.perf DESC, ml.dtl DESC
LIMIT 8;
idg | nmg | perf | dtl
-----+----------------------------------+-------+-----------------------
18 | B8CCC0671B37033F0658C228DE0FE00A | 96739 | 2025-07-21 15:29:41.5
1 | 8C238B96C24AA9AEF47CFFD9C50163DA | 94318 | 2025-05-23 15:07:41.5
48 | F4046B4A21A99BE8A26DAF5FF29F49CF | 93693 | 2025-10-09 20:49:41.5
7 | 93B0EE0CBACC761C98015694433B1B21 | 92628 | 2026-03-09 03:38:41.5
10 | E873B2DF0C7BB42D21F791503907F07F | 91239 | 2025-06-14 01:10:41.5
33 | E0C096E18E57BE565DA3790AFB0B46E7 | 88503 | 2025-05-30 15:27:41.5
21 | A856E35CA0E64A4F0866897D4606970E | 88341 | 2025-12-04 03:39:41.5
54 | 60CACD61B996EB383134D68AE1E0F07A | 86705 | 2025-07-22 15:24:41.5
(8 lignes)
Temps : 82,368 ms
Temps : 75,118 ms
Temps : 83,166 ms
explain
select g.idg, g.nmg, l.dtl, l.perf
from
(select idg, nmg from geants where actif = true) g
join lateral
(select perf, dtl from lancers where g.idg = idg order by perf desc, dtl desc fetch first 1 rows only) l
on (true)
order by perf desc, dtl desc
fetch first 8 rows only;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Limit (cost=22.02..22.04 rows=8 width=46)
-> Sort (cost=22.02..22.12 rows=41 width=46)
Sort Key: lancers.perf DESC, lancers.dtl DESC
-> Nested Loop (cost=0.42..21.20 rows=41 width=46)
-> Seq Scan on geants (cost=0.00..1.56 rows=41 width=34)
Filter: actif
-> Limit (cost=0.42..0.46 rows=1 width=12)
-> Index Only Scan Backward using lancers_i3
select g.idg, g.nmg, l.dtl, l.perf
from
(select idg, nmg from geants where actif = true) g
join lateral
(select perf, dtl from lancers where g.idg = idg order by perf desc, dtl desc fetch first 1 rows only) l
on (true)
order by perf desc, dtl desc
fetch first 8 rows only;
idg | nmg | dtl | perf
-----+----------------------------------+-----------------------+-------
18 | B8CCC0671B37033F0658C228DE0FE00A | 2025-07-21 15:29:41.5 | 96739
1 | 8C238B96C24AA9AEF47CFFD9C50163DA | 2025-05-23 15:07:41.5 | 94318
48 | F4046B4A21A99BE8A26DAF5FF29F49CF | 2025-10-09 20:49:41.5 | 93693
7 | 93B0EE0CBACC761C98015694433B1B21 | 2026-03-09 03:38:41.5 | 92628
10 | E873B2DF0C7BB42D21F791503907F07F | 2025-06-14 01:10:41.5 | 91239
33 | E0C096E18E57BE565DA3790AFB0B46E7 | 2025-05-30 15:27:41.5 | 88503
21 | A856E35CA0E64A4F0866897D4606970E | 2025-12-04 03:39:41.5 | 88341
54 | 60CACD61B996EB383134D68AE1E0F07A | 2025-07-22 15:24:41.5 | 86705
(8 lignes)
Temps : 1,231 ms
Temps : 1,118 ms
Temps : 0,559 ms
Ouf ! La requête a retrouvé toute son efficacité mais, nouvelle catastrophe, une requête qui avait toujours tourné vite en utilisant l'index lancers_i2 est à présent "lente" et consomme beaucoup de ressources. Le scribe tente de transformer un peu la requête, en vain :
-- avant le changement...
explain analyze select max(perf) from lancers where dtl between current_date-110 and current_date-100 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=1.55..1.56 rows=1 width=4) (actual time=0.054..0.055 rows=1 loops=1)
InitPlan 1
-> Limit (cost=0.43..1.55 rows=1 width=4) (actual time=0.051..0.052 rows=1 loops=1)
-> Index Only Scan Backward using lancers_i2 on lancers (cost=0.43..16234.17 rows=14574 width=4) (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: ((perf IS NOT NULL) AND (dtl >= (CURRENT_DATE - 110)) AND (dtl <= (CURRENT_DATE - 100)))
Heap Fetches: 0
Planning Time: 0.218 ms
Execution Time: 0.082 ms
-- apres le changement
explain select max(perf) from lancers where dtl between current_date-110 and current_date-100;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Finalize Aggregate (cost=10129.40..10129.41 rows=1 width=4)
-> Gather (cost=10129.18..10129.39 rows=2 width=4)
Workers Planned: 2
-> Partial Aggregate (cost=9129.18..9129.19 rows=1 width=4)
-> Parallel Seq Scan on lancers (cost=0.00..9114.00 rows=6072 width=4)
Filter: ((dtl >= (CURRENT_DATE - 110)) AND (dtl <= (CURRENT_DATE - 100)))
select max(perf) from lancers where dtl between current_date-110 and current_date-100;
max
-------
52751
(1 ligne)
Temps : 16,038 ms
Temps : 16,315 ms
Temps : 17,712 ms
explain select max(perf) from lancers where dtl between current_date-110 and current_date-100 and perf > 50000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=10714.36..10714.38 rows=1 width=4)
-> Gather (cost=10714.15..10714.36 rows=2 width=4)
Workers Planned: 2
-> Partial Aggregate (cost=9714.15..9714.16 rows=1 width=4)
-> Parallel Seq Scan on lancers (cost=0.00..9714.00 rows=60 width=4)
Filter: ((perf > 50000) AND (dtl >= (CURRENT_DATE - 110)) AND (dtl <= (CURRENT_DATE - 100)))
select max(perf) from lancers where dtl between current_date-110 and current_date-100 and perf > 50000;
max
-------
52751
(1 ligne)
Temps : 13,698 ms
Temps : 14,846 ms
Temps : 13,684 ms
Avant de recréer lancers_i2, toujours un peu embêté de devoir conserver deux index aussi comparables, Margiono se demande si mettre à jour PostgreSQL ne pourrait pas aider :
pg_upgradecluster -v 18 17 main
select version();
version
--------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.3 (Debian 18.3-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
explain select max(perf) from lancers where dtl between current_date-110 and current_date-100;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Finalize Aggregate (cost=10183.83..10183.84 rows=1 width=4)
-> Gather (cost=10183.61..10183.83 rows=2 width=4)
Workers Planned: 2
-> Partial Aggregate (cost=9183.61..9183.62 rows=1 width=4)
-> Parallel Seq Scan on lancers (cost=0.00..9168.00 rows=6246 width=4)
Filter: ((dtl >= (CURRENT_DATE - 110)) AND (dtl <= (CURRENT_DATE - 100)))
select max(perf) from lancers where dtl between current_date-110 and current_date-100;
max
-------
52751
(1 ligne)
Temps : 15,989 ms
Temps : 15,989 ms
Temps : 15,897 ms
explain select max(perf) from lancers where dtl between current_date-110 and current_date-100 and perf > 50000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Aggregate (cost=328.73..328.74 rows=1 width=4)
-> Index Only Scan using lancers_i3 on lancers (cost=0.43..328.36 rows=148 width=4)
Index Cond: ((perf > 50000) AND (dtl >= (CURRENT_DATE - 110)) AND (dtl <= (CURRENT_DATE - 100)))
select max(perf) from lancers where dtl between current_date-110 and current_date-100 and perf > 50000;
max
-------
52751
(1 ligne)
Temps : 1,373 ms
Temps : 0,769 ms
Temps : 0,789 ms
explain analyze select max(perf) from lancers where dtl between current_date-110 and current_date-100 and perf > 50000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=316.70..316.71 rows=1 width=4) (actual time=0.310..0.311 rows=1.00 loops=1)
Buffers: shared hit=173
-> Index Only Scan using lancers_i3 on lancers (cost=0.43..316.33 rows=145 width=4) (actual time=0.285..0.304 rows=1.00 loops=1)
Index Cond: ((perf > 50000) AND (dtl >= (CURRENT_DATE - 110)) AND (dtl <= (CURRENT_DATE - 100)))
Heap Fetches: 0
Index Searches: 55
Buffers: shared hit=173
Planning:
Buffers: shared hit=11
Planning Time: 0.316 ms
Execution Time: 0.352 ms
(11 lignes)
Ouf ! La première requête est toujours lente mais la deuxième requête a retrouvé (quasi) toute son efficacité grâce aux "index skip scans" de PostgreSQL 18 et versions supérieures. Attention toutefois au mot "quasi", pour des performances optimales sur toutes les requêtes, Margiono devrait sans doute revoir sa copie...