Jointures inutiles

Phoenix Genma Ken

      L’optimisation consistant à ne PAS faire quelque chose est la plus efficace. Ici, nous allons nous intéresser aux jointures.
      Guillaume Lelarge écrit dans son livre "PostgreSQL, architecture et notions avancées" que le planner (optimiseur) est capable depuis la version 9.0 d’éviter certaines jointures inutiles.
      Démonstration avec PostgreSQL 10 beta :

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-16) 6.3.0 20170425, 64-bit (1 ligne) create table geants( idg integer generated by default as identity primary key, dtn timestamp, dtb timestamp, devise varchar(128), berserk boolean, taille smallint ); CREATE TABLE with recursive serie(i, r) as (select 550000, random() UNION ALL select i - 1, random() from serie where i > 100001) insert into geants(dtn, dtb, devise, berserk, taille) select case when r < 0.8 then current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(r * 100 + 1) || ' days')::interval end, case when r >= 0.8 then current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(r * 100 + 1) || ' days')::interval else current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(r * 100 + 6) || ' days')::interval end, upper(md5(random()::text)), case when random() < 0.001 then true else false end, 200 + (trunc(random() * 200 + 1)) from serie; INSERT 0 450000 create table lancers(dtl timestamp, idg integer, perf integer); CREATE TABLE WITH serie(i) AS (SELECT generate_series(10000000,1,-1)) insert into lancers(dtl, idg, perf) select current_timestamp - (i/10 || ' seconds')::interval, trunc(random() * 450000 + 1), case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end from serie; INSERT 0 10000000 explain select lancers.idg from lancers left outer join geants on (geants.idg = lancers.idg) where perf > 75000; QUERY PLAN ----------------------------------------------------------------------------- Gather (cost=1000.00..107236.20 rows=986 width=4) Workers Planned: 2 -> Parallel Seq Scan on lancers (cost=0.00..106137.60 rows=411 width=4) Filter: (perf > 75000) (4 lignes)


      Nous réalisons une jointure externe gauche entre lancers et geants en utilisant la colonne idg. La colonne idg constitue la clé primaire de geants donc il existe 0 ou 1 idg dans la table geants pour chaque idg dans la table lancers. Si j’avais ajouté une clé étrangère entre geants et lancers ce serait 1 mais peu importe. Ce qui est important ici, c’est que ça ne puisse pas être 2 ou plus. En effet, que ce soit 0 ou 1, chaque ligne de lancers respectant la clause de filtrage nous donne une ligne de résultats. La clause de jointure ne modifie donc pas le nombre de lignes qui seraient retournées en interrogeant seulement la table lancers.
      Nous ne demandons que des informations de la table lancers au niveau de la clause de projection. Ici, il s’agit de la colonne lancers.idg.
      Le planner fait la même conclusion que nous : la jointure avec geants ne modifie pas les lignes retournées par la requête. Autant donc ne pas réaliser cette jointure.
      Alors c’est magique et nous n’avons plus besoin de réfléchir ? Hmmm nous allons tout de même creuser.

explain select distinct geants.idg, geants.dtn from geants left outer join lancers on (geants.idg = lancers.idg) where berserk; QUERY PLAN --------------------------------------------------------------------------------------------------- Unique (cost=288871.47..288943.98 rows=435 width=12) -> Sort (cost=288871.47..288895.64 rows=9667 width=12) Sort Key: geants.idg, geants.dtn -> Hash Right Join (cost=7929.94..288231.57 rows=9667 width=12) Hash Cond: (lancers.idg = geants.idg) -> Seq Scan on lancers (cost=0.00..154053.60 rows=9999860 width=4) -> Hash (cost=7924.50..7924.50 rows=435 width=12) -> Gather (cost=1000.00..7924.50 rows=435 width=12) Workers Planned: 2 -> Parallel Seq Scan on geants (cost=0.00..6881.00 rows=181 width=12) Filter: berserk (11 lignes) explain (select geants.idg, geants.dtn from geants where berserk); QUERY PLAN --------------------------------------------------------------------------- Gather (cost=1000.00..7924.50 rows=435 width=12) Workers Planned: 2 -> Parallel Seq Scan on geants (cost=0.00..6881.00 rows=181 width=12) Filter: berserk (4 lignes) select count(*) from (select distinct geants.idg, geants.dtn from geants left outer join lancers on (geants.idg = lancers.idg) where berserk) b; count ------- 428 (1 ligne) select count(*) from (select geants.idg, geants.dtn from geants where berserk) b; count ------- 428 (1 ligne) (select geants.idg, geants.dtn from geants where berserk) except (select distinct geants.idg, geants.dtn from geants left outer join lancers on (geants.idg = lancers.idg) where berserk); idg | dtn -----+----- (0 ligne) (select distinct geants.idg, geants.dtn from geants left outer join lancers on (geants.idg = lancers.idg) where berserk) except (select geants.idg, geants.dtn from geants where berserk); idg | dtn -----+----- (0 ligne)


      Que fait la 1ère requête ? Nous cherchons l’identifiant des géants et leur date de naissance, qu’ils aient lancé ou non puisqu’il s’agit d’une jointure externe gauche entre geants et lancers.
      Nous éliminons les doublons donc, même si un géant berserk a lancé plusieurs fois, nous n’obtiendrons qu’une seule fois son idg dans les résultats.
      Nous ne demandons que des informations de la table geants au niveau de la clause de projection, les colonnes geants.idg et geants.dtn.
      La jointure avec lancers est superflue mais elle est n’est pas indolore car PostgreSQL la réalise effectivement au contraire de ce que nous avions observé précédemment.
      En fait, ce que nous cherchions ici à obtenir, c’était la liste des berserks. La 2ème requête donne cette liste aussi bien que la 1ère mais elle est plus simple et moins coûteuse puisqu’elle ne comporte pas la jointure avec lancers.

Conclusion

      PostgreSQL peut, dans certains cas, ne pas réaliser une jointure que son planner juge inutile. Cependant cela ne dispense pas de réfléchir sur les requêtes. Le plus sûr moyen de ne pas exécuter quelque chose d’inutile reste encore de ne pas le demander lors de l’écriture du code SQL, quel que soit le SGBD. Les informations fournies dans cet article sont égalalement valables avec Oracle Database 11.2.0.4 par exemple.

Mise à jour : 26/06/2017