Indexer, Filtrer

select version(); version ----------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 18.3 (Debian 18.3-2~20260413.2043.g6393259bd49.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, perf, dtl); CREATE INDEX create index lancers_i2 on lancers(perf, dtl); CREATE INDEX 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 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' and perf > 50000 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=504.87..504.89 rows=8 width=14) -> Sort (cost=504.87..504.89 rows=9 width=14) Sort Key: l.perf DESC, l.dtl DESC -> Nested Loop (cost=326.55..504.73 rows=9 width=14) -> HashAggregate (cost=326.13..326.53 rows=40 width=6) Group Key: g.idg -> Nested Loop (cost=0.42..305.76 rows=4073 width=6) -> Seq Scan on geants g (cost=0.00..1.56 rows=40 width=2) Filter: actif -> Index Only Scan using lancers_i1 on lancers l_1 (cost=0.42..6.55 rows=106 width=6) Index Cond: ((idg = g.idg) AND (perf > 50000)) -> Index Only Scan using lancers_i1 on lancers l (cost=0.42..4.44 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' and perf > 50000 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 -----+----------------------------+------- 12 | 2025-10-14 02:20:43.919045 | 98194 15 | 2026-03-11 15:29:43.919045 | 97287 40 | 2025-03-18 10:16:43.919045 | 97205 23 | 2025-11-14 01:29:43.919045 | 94115 51 | 2025-05-11 03:19:43.919045 | 93883 11 | 2025-10-19 20:10:43.919045 | 90719 17 | 2026-02-15 02:41:43.919045 | 90540 8 | 2025-08-16 06:23:43.919045 | 88061 (8 lignes) Temps : 1,006 ms Temps : 0,921 ms Temps : 1,060 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 and perf > 50000 ) 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=635.27..635.29 rows=8 width=46) -> Sort (cost=635.27..635.32 rows=20 width=46) Sort Key: ml.perf DESC, ml.dtl DESC -> Hash Join (cost=11.26..634.87 rows=20 width=46) Hash Cond: (ml.idg = g.idg) -> Subquery Scan on ml (cost=9.00..632.56 rows=20 width=14) Filter: (ml.rn = 1) -> WindowAgg (cost=9.00..581.65 rows=4073 width=22) Window: w1 AS (PARTITION BY l.idg ORDER BY l.perf, l.dtl ROWS UNBOUNDED PRECEDING) Run Condition: (row_number() OVER w1 <= 1) -> Incremental Sort (cost=8.85..500.19 rows=4073 width=14) Sort Key: l.idg, l.perf DESC, l.dtl DESC Presorted Key: l.idg -> Nested Loop (cost=0.56..321.18 rows=4073 width=14) -> Index Scan using geants_pkey on geants g_1 (cost=0.14..12.98 rows=40 width=2) Filter: actif -> Index Only Scan using lancers_i1 on lancers l (cost=0.42..6.65 rows=106 width=14) Index Cond: ((idg = g_1.idg) AND (perf > 50000)) -> Hash (cost=1.56..1.56 rows=56 width=34) -> Seq Scan on geants g (cost=0.00..1.56 rows=56 width=34) (20 lignes) 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 and perf > 50000 ) 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 -----+----------------------------------+-------+---------------------------- 12 | C95028738761F4E7476F233DA60E3DE3 | 98194 | 2025-10-14 02:20:43.919045 15 | A769FDF69200876DDBCE0387A340E51F | 97287 | 2026-03-11 15:29:43.919045 40 | C31E1F97449EF206E2E5A434A86436FF | 97205 | 2025-03-18 10:16:43.919045 23 | FE9B83AC1251329AE8160E67C3D8EF41 | 94115 | 2025-11-14 01:29:43.919045 51 | 2BF5B80DF1007176203415049F1A1591 | 93883 | 2025-05-11 03:19:43.919045 11 | B7E49EA0F36F3249289037EB5B03BF47 | 90719 | 2025-10-19 20:10:43.919045 17 | 66746DE0383F3EFAFA2E287365DE9CB1 | 90540 | 2026-02-15 02:41:43.919045 8 | 478127A5452F1C10686A46BD82C90780 | 88061 | 2025-08-16 06:23:43.919045 (8 lignes) Temps : 0,886 ms Temps : 1,000 ms Temps : 0,728 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 and perf > 50000 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.47..22.49 rows=8 width=46) -> Sort (cost=22.47..22.57 rows=40 width=46) Sort Key: lancers.perf DESC, lancers.dtl DESC -> Nested Loop (cost=0.42..21.67 rows=40 width=46) -> Seq Scan on geants (cost=0.00..1.56 rows=40 width=34) Filter: actif -> Limit (cost=0.42..0.48 rows=1 width=12) -> Index Only Scan Backward using lancers_i1 on lancers (cost=0.42..6.55 rows=106 width=12) Index Cond: ((idg = geants.idg) AND (perf > 50000)) (9 lignes) 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 and perf > 50000 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 -----+----------------------------------+----------------------------+------- 12 | C95028738761F4E7476F233DA60E3DE3 | 2025-10-14 02:20:43.919045 | 98194 15 | A769FDF69200876DDBCE0387A340E51F | 2026-03-11 15:29:43.919045 | 97287 40 | C31E1F97449EF206E2E5A434A86436FF | 2025-03-18 10:16:43.919045 | 97205 23 | FE9B83AC1251329AE8160E67C3D8EF41 | 2025-11-14 01:29:43.919045 | 94115 51 | 2BF5B80DF1007176203415049F1A1591 | 2025-05-11 03:19:43.919045 | 93883 11 | B7E49EA0F36F3249289037EB5B03BF47 | 2025-10-19 20:10:43.919045 | 90719 17 | 66746DE0383F3EFAFA2E287365DE9CB1 | 2026-02-15 02:41:43.919045 | 90540 8 | 478127A5452F1C10686A46BD82C90780 | 2025-08-16 06:23:43.919045 | 88061 (8 lignes) Temps : 0,566 ms Temps : 0,505 ms Temps : 0,482 ms explain select idg, perf, dtl from lancers where dtl between current_date-110 and current_date-100 order by perf desc limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------- Limit (cost=0.43..2.30 rows=1 width=14) -> Index Scan Backward using lancers_i2 on lancers (cost=0.43..27241.41 rows=14593 width=14) Index Cond: ((dtl >= (CURRENT_DATE - 110)) AND (dtl <= (CURRENT_DATE - 100))) (3 lignes) select idg, perf, dtl from lancers where dtl between current_date-110 and current_date-100 order by perf desc limit 1; idg | perf | dtl -----+-------+---------------------------- 52 | 49999 | 2026-01-01 13:22:43.919045 (1 ligne) Temps : 0,318 ms Temps : 0,252 ms Temps : 0,269 ms explain 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) InitPlan 1 -> Limit (cost=0.43..1.55 rows=1 width=4) -> Index Only Scan Backward using lancers_i2 on lancers (cost=0.43..16234.39 rows=14593 width=4) Index Cond: ((perf IS NOT NULL) AND (dtl >= (CURRENT_DATE - 110)) AND (dtl <= (CURRENT_DATE - 100))) (5 lignes) select max(perf) from lancers where dtl between current_date-110 and current_date-100; max ------- 49999 (1 ligne) Temps : 0,391 ms Temps : 0,323 ms Temps : 0,267 ms