Jointures latérales, indexation (composite, partielle), partitionnement, contexte

Indexer au plus juste pour tous les besoins ?

      Dans cette page, Margiono s'est "affolé" en constatant que sa requête basée sur une jointure latérale était devenue lente au changement de contexte. Il est responsable des données, il a tranformé un index, il en a supprimé un autre... Mauvaise pratique.
      Ne pouvait-il pas modifier sa requête, comme il l'a d'ailleurs fait par la suite pour une autre requête, en constatant la régression ? Il voulait les champions après tout, pourquoi ne pas donner cette information à PostgreSQL en filtrant sur les "super" perfs via perf > 50000 dans la sous-requête corrélative ?
      Démonstration en conservant le DDL + DML :

select version(); version ----------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 18.3 (Debian 18.3-2~20260411.0843.gb081c5b0730.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 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 commit; COMMIT \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Persistence | Méthode d'accès | Taille | Description --------+----------------+----------+--------------+-------------+-----------------+------------+------------- clan | geants | table | postgres | permanent | heap | 8192 bytes | clan | geants_idg_seq | séquence | postgres | permanent | | 8192 bytes | clan | lancers | table | postgres | permanent | heap | 24 MB | (3 lignes) \di+ List of indexes Schéma | Nom | Type | Propriétaire | Table | Persistence | Méthode d'accès | Taille | Description --------+-------------+-------+--------------+---------+-------------+-----------------+---------+------------- clan | geants_pkey | index | postgres | geants | permanent | btree | 16 kB | clan | lancers_i1 | index | postgres | lancers | permanent | btree | 3944 kB | clan | lancers_i2 | index | postgres | lancers | permanent | btree | 17 MB | (3 lignes) explain analyze 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=114.09..114.11 rows=8 width=46) (actual time=569.378..569.381 rows=8.00 loops=1) Buffers: shared hit=1152181 -> Sort (cost=114.09..114.17 rows=33 width=46) (actual time=569.377..569.379 rows=8.00 loops=1) Sort Key: lancers.perf DESC, lancers.dtl DESC Sort Method: top-N heapsort Memory: 26kB Buffers: shared hit=1152181 -> Nested Loop (cost=0.42..113.43 rows=33 width=46) (actual time=0.032..569.357 rows=33.00 loops=1) Buffers: shared hit=1152181 -> Seq Scan on geants (cost=0.00..1.56 rows=33 width=34) (actual time=0.008..0.016 rows=33.00 loops=1) Filter: actif Rows Removed by Filter: 23 Buffers: shared hit=1 -> Limit (cost=0.42..3.37 rows=1 width=12) (actual time=17.252..17.252 rows=1.00 loops=33) Buffers: shared hit=1152180 -> Index Scan Backward using lancers_i2 on lancers (cost=0.42..31415.33 rows=10667 width=12) (actual time=17.252..17.252 rows=1.00 loops=33) Filter: (geants.idg = idg) Rows Removed by Filter: 34845 Index Searches: 33 Buffers: shared hit=1152180 Planning Time: 0.105 ms Execution Time: 569.404 ms (21 lignes) Temps : 569,757 ms explain analyze 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 perf > 50000 and 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=3785.30..3785.32 rows=8 width=46) (actual time=0.455..0.456 rows=8.00 loops=1) Buffers: shared hit=1546 -> Sort (cost=3785.30..3785.38 rows=33 width=46) (actual time=0.454..0.455 rows=8.00 loops=1) Sort Key: lancers.perf DESC, lancers.dtl DESC Sort Method: top-N heapsort Memory: 26kB Buffers: shared hit=1546 -> Nested Loop (cost=0.42..3784.64 rows=33 width=46) (actual time=0.029..0.440 rows=19.00 loops=1) Buffers: shared hit=1546 -> Seq Scan on geants (cost=0.00..1.56 rows=33 width=34) (actual time=0.007..0.012 rows=33.00 loops=1) Filter: actif Rows Removed by Filter: 23 Buffers: shared hit=1 -> Limit (cost=0.42..114.62 rows=1 width=12) (actual time=0.013..0.013 rows=0.58 loops=33) Buffers: shared hit=1545 -> Index Scan Backward using lancers_i2 on lancers (cost=0.42..12104.97 rows=106 width=12) (actual time=0.013..0.013 rows=0.58 loops=33) Index Cond: (perf > 50000) Filter: (geants.idg = idg) Rows Removed by Filter: 43 Index Searches: 33 Buffers: shared hit=1545 Planning: Buffers: shared hit=4 Planning Time: 0.189 ms Execution Time: 0.473 ms (24 lignes)

      Oui, Margiono pouvait simplement considérer les performances exceptionnelles et, s'il avait tapé "trop haut" pour le niveau de perf, il pouvait simplement abaisser un peu son exigence. Sa préoccupation concernant l'indexation inutile/redondante était par ailleurs louable mais, s'il avait vraiment voulu gagner de l'espace disque et de la performance DML, il aurait pu au départ créer lancers_i2 partiellement :

drop index lancers_i2; DROP INDEX create index lancers_i2 on lancers(perf, dtl) where perf > 50000; CREATE INDEX \di+ List of indexes Schéma | Nom | Type | Propriétaire | Table | Persistence | Méthode d'accès | Taille | Description --------+-------------+-------+--------------+---------+-------------+-----------------+---------+------------- clan | geants_pkey | index | postgres | geants | permanent | btree | 16 kB | clan | lancers_i1 | index | postgres | lancers | permanent | btree | 3944 kB | clan | lancers_i2 | index | postgres | lancers | permanent | btree | 16 kB | (3 lignes) explain analyze 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 perf > 50000 and 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=3769.49..3769.51 rows=8 width=46) (actual time=0.340..0.341 rows=8.00 loops=1) Buffers: shared hit=1480 -> Sort (cost=3769.49..3769.57 rows=33 width=46) (actual time=0.339..0.340 rows=8.00 loops=1) Sort Key: lancers.perf DESC, lancers.dtl DESC Sort Method: top-N heapsort Memory: 26kB Buffers: shared hit=1480 -> Nested Loop (cost=0.14..3768.83 rows=33 width=46) (actual time=0.030..0.332 rows=19.00 loops=1) Buffers: shared hit=1480 -> Seq Scan on geants (cost=0.00..1.56 rows=33 width=34) (actual time=0.009..0.012 rows=33.00 loops=1) Filter: actif Rows Removed by Filter: 23 Buffers: shared hit=1 -> Limit (cost=0.14..114.14 rows=1 width=12) (actual time=0.009..0.009 rows=0.58 loops=33) Buffers: shared hit=1479 -> Index Scan Backward using lancers_i2 on lancers (cost=0.14..11970.09 rows=105 width=12) (actual time=0.009..0.009 rows=0.58 loops=33) Filter: (geants.idg = idg) Rows Removed by Filter: 43 Index Searches: 33 Buffers: shared hit=1479 Planning Time: 0.124 ms Execution Time: 0.356 ms (21 lignes)

      Bingo, l'index minuscule fait le travail pour la requête modifiée, le beurre et l'argent du beurre. Bien sûr, fixer 50000 en dur dans la requête et, surtout, dans la définition de l'index peut être dangereux. Point qui n'a pas été considéré : est-ce qu'une perf exceptionnelle a encore un sens si elle est très ancienne pour déterminer le "top 8" ? Est-ce que ce seuil de performance permettra toujours de trouver huit géants, pas de possiblité de régression du niveau ? La valeur serait sans doute paramétrée, fournie au moment via la requête, la période à considérer (dynamique, e.g moins d'un an ?) devant sans doute l'être aussi.
      Quid de l'index ? Un peu plus complexe encore car il n'a pas le dynamisme d'une requête ! Si la requête est fournie à différents clans, il faudrait le créer initialement en fonction de la distribution des données, avec une petite marge pour gérer une éventuelle baisse de niveau. Mais est-ce que l'évolution des performances banalisera au contraire les performances à plus de 500m, rendant l'index inutilement "gros" ? Encore moins facile ! Il faudra surveiller l'évolution des perfs pour recréer le "minuscule" index ad-hoc périodiquement...ou, mieux, partitionner la table des lancers et créer l'index sur la partition suivante en fonction des évolutions observées et prévisibles, peut-être supprimer les anciens index quand la partition devient "froide".
      Difficile...sans entrer dans toutes ces considérations, Margiono n'avait donc pas forcément tort avec son index sur (idg, perf, dtl) car c'est plutôt l'index lancers_i1 sur (idg) qui est discutable. Si on lui demandait un jour de s'intéresser aussi aux meilleures performances des "tocards", un index sur (idg, perf, dtl) deviendrait utile par exemple. Le "bon" choix avec PostgreSQL 17 pour couvrir le top 8 des lanceurs, les meilleures performances par géant (même les tocards) et le top des lancers pour une période donnée serait alors l'index lancers_i1 sur (idg, perf, dtl) et lancers_i2 sur (perf, dtl) avec (perf > seuil_de_perf_excellente), l'index lancers_i2 devenant moins utile avec PostgreSQL 18 et les "skip scans"...si le nombre de membres du clan n'explose pas, ce qui remettrait tout en cause de toute façon !
      Conclusion renouvelée : la requête et l'indexation idéales dépendent toujours du besoin, des données, bref du contexte complet. Il peut être complexe d'indexer au plus juste tout en couvrant l'ensemble des besoins. Pour couvrir les besoins les plus avancés, il peut être nécessaire de tout combiner : requêtes optimales, y-compris pour les valeurs des paramètres, partitionnement, indexation composite/partielle/B-Tree/BRIN ad-hoc. Vous serez souvent probablement amenés à "surindexer", par précaution.

Mise à jour : 12/04/2026