Rester efficace même si la modélisation est...étrange

Des choux et des carottes

      Margiono doit intervenir pour de nombreux clans de géants mais, invariablement, ils lancent des cailloux. Garfog, un chef, lui demande un jour de trouver dans les registres de son clan toutes les notes correspondant à un lancer bien précis mais aussi celles qui seraient rattachées à un géant, dans le système d'identification inter-clans, même si cela ne correspond à aucun lancer.
      Un peu perdu, Margiono tente d'abord d'effectuer une jointure entre les notes et les lancers, en filtrant sur le lancer ou l'identifiant inter-clans du géant :

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 notes( id_note bigint generated by default as identity primary key, id_geant_interclans uuid, id_lancer bigint, id_clan bigint, note text); CREATE TABLE with recursive serie(i) as (select 1 UNION ALL select i + 1 from serie where i < 100000) insert into notes(id_geant_interclans, id_lancer, id_clan, note) select case when random() <= 0.1 then gen_random_uuid() end, 5*i, case when random() <= 0.5 then 1 else trunc(random() * 10 + 1) end, md5(random()::text) from serie ; INSERT 0 100000 create index on notes(id_geant_interclans); CREATE INDEX create index on notes(id_lancer); CREATE INDEX create table lancers( id_lancer bigint generated by default as identity primary key, perf bigint, id_geant bigint, id_clan bigint, dt_lancer timestamp); CREATE TABLE with recursive serie(i) as (select 500000 UNION ALL select i - 1 from serie where i > 1) insert into lancers(perf, id_geant, id_clan, dt_lancer) select case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end , 0, (select id_clan from notes n where id_lancer = 500001-i), current_timestamp - (i || ' minutes')::interval from serie ; INSERT 0 500000 create index on lancers(id_geant); CREATE INDEX create index on lancers using brin(dt_lancer) with (autosummarize); CREATE INDEX explain select * from notes n left outer join lancers l on n.id_lancer = l.id_lancer where n.id_geant_interclans = '16a1c1cf-ce1d-4a2e-8199-af2f20634a08' or (l.id_lancer = 25 and n.id_clan = 1); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=3440.85..10330.66 rows=1 width=113) Workers Planned: 2 -> Parallel Hash Right Join (cost=2440.85..9330.56 rows=1 width=113) Hash Cond: (l.id_lancer = n.id_lancer) Filter: ((n.id_geant_interclans = '16a1c1cf-ce1d-4a2e-8199-af2f20634a08'::uuid) OR ((l.id_lancer = 25) AND (n.id_clan = 1))) -> Parallel Seq Scan on lancers l (cost=0.00..5871.33 rows=208333 width=40) -> Parallel Hash (cost=2034.35..2034.35 rows=32520 width=73) -> Parallel Seq Scan on notes n (cost=0.00..2034.35 rows=32520 width=73) Filter: ((id_geant_interclans = '16a1c1cf-ce1d-4a2e-8199-af2f20634a08'::uuid) OR (id_clan = 1)) (9 lignes) select * from notes n left outer join lancers l on n.id_lancer = l.id_lancer where n.id_geant_interclans = '16a1c1cf-ce1d-4a2e-8199-af2f20634a08' or (l.id_lancer = 25 and n.id_clan = 1); id_note | id_geant_interclans | id_lancer | id_clan | note | id_lancer | perf | id_geant | id_clan | dt_lancer ---------+--------------------------------------+-----------+---------+----------------------------------+-----------+-------+----------+---------+---------------------------- 5 | | 25 | 1 | 182781a07e80314d52a5b84a5305b8b1 | 25 | 15862 | 0 | 1 | 2025-05-02 16:28:56.088194 45 | 16a1c1cf-ce1d-4a2e-8199-af2f20634a08 | 225 | 4 | 3d43de0e0b381b486f22ff69aa2c6681 | 225 | 44830 | 0 | 4 | 2025-05-02 19:48:56.088194 (2 lignes) Temps : 58,714 ms Temps : 49,573 ms Temps : 49,091 ms

      Pas mal, il a le résultat mais cette requête semble lente. Margiono se reprend. Il se rend compte que la modélisation est étrange, que ces fameuses notes sont reliées aux lancers ou directement aux géants, qu'on lui a soumis DEUX problèmes et qu'il faut donc décomposer la requête, chercher les notes associées au géant puis les notes associées au lancer, même s'il compte bien tout faire en UNE requête :

explain with notes_geant_interclans as (select * from notes n left outer join lancers l on n.id_lancer = l.id_lancer where n.id_geant_interclans = '16a1c1cf-ce1d-4a2e-8199-af2f20634a08') , notes_lancers as (select * from notes n join lancers l on n.id_lancer = l.id_lancer where (l.id_lancer = 25 and n.id_clan = 1)) select * from notes_geant_interclans UNION select * from notes_lancers; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=33.53..33.59 rows=2 width=112) -> Sort (cost=33.53..33.54 rows=2 width=112) Sort Key: n.id_note, n.id_geant_interclans, n.id_lancer, n.id_clan, n.note, l.id_lancer, l.perf, l.id_geant, l.id_clan, l.dt_lancer -> Append (cost=0.71..33.52 rows=2 width=112) -> Nested Loop Left Join (cost=0.71..16.75 rows=1 width=113) -> Index Scan using notes_id_geant_interclans_idx on notes n (cost=0.29..8.31 rows=1 width=73) Index Cond: (id_geant_interclans = '16a1c1cf-ce1d-4a2e-8199-af2f20634a08'::uuid) -> Index Scan using lancers_pkey on lancers l (cost=0.42..8.44 rows=1 width=40) Index Cond: (id_lancer = n.id_lancer) -> Nested Loop (cost=0.71..16.76 rows=1 width=113) -> Index Scan using notes_id_lancer_idx on notes n_1 (cost=0.29..8.31 rows=1 width=73) Index Cond: (id_lancer = 25) Filter: (id_clan = 1) -> Index Scan using lancers_pkey on lancers l_1 (cost=0.42..8.44 rows=1 width=40) Index Cond: (id_lancer = 25) (15 lignes) with notes_geant_interclans as (select * from notes n left outer join lancers l on n.id_lancer = l.id_lancer where n.id_geant_interclans = '16a1c1cf-ce1d-4a2e-8199-af2f20634a08') , notes_lancers as (select * from notes n join lancers l on n.id_lancer = l.id_lancer where (l.id_lancer = 25 and n.id_clan = 1)) select * from notes_geant_interclans UNION select * from notes_lancers; id_note | id_geant_interclans | id_lancer | id_clan | note | id_lancer | perf | id_geant | id_clan | dt_lancer ---------+--------------------------------------+-----------+---------+----------------------------------+-----------+-------+----------+---------+---------------------------- 5 | | 25 | 1 | 182781a07e80314d52a5b84a5305b8b1 | 25 | 15862 | 0 | 1 | 2025-05-02 16:28:56.088194 45 | 16a1c1cf-ce1d-4a2e-8199-af2f20634a08 | 225 | 4 | 3d43de0e0b381b486f22ff69aa2c6681 | 225 | 44830 | 0 | 4 | 2025-05-02 19:48:56.088194 (2 lignes) Temps : 0,659 ms Temps : 0,677 ms Temps : 0,632 ms

      C'est mieux. Lorsque la modélisation est étrange (bancale...), il faut parfois aider PostgreSQL. Ici, il était possible de tout faire efficacement en une requête (en fait 2 en 1 grâce aux CTE, common table expressions ou with queries), mais difficilement avec une seule jointure.

Mise à jour : 14/04/2026