Reviers, le 7 mars 2025
Une fonctionnalité m’avait beaucoup impressionné en version 9.0, bien qu’elle
ait été éclipsée par la grande nouveauté, la réplication en natif. Cette
fonctionnalité avait pour but de supprimer les jointures inutiles. En fait, seul
un cas de jointure inutile dans un LEFT JOIN était traité. Depuis, beaucoup de
patchs ont circulé pour détecter d’autres cas, mais aucun n’a passé la sélection
jusqu’à la semaine dernière. La nouveauté concerne les jointures d’une table sur
elle-même. C’est ce que je vais détailler dans cet article.
Prenons cette requête de jointure d’une table avec elle-même :
SELECT *
FROM t1
JOIN t1 t1bis ON t1.c1 = t1bis.c1;
Sans contrainte particulière sur c1, cette jointure ne peut être éludée. Cependant, si les
valeurs de la colonne c1 sont uniques, le moteur n’a pas besoin de réaliser la jointure
pour obtenir le résultat.
Nous allons prendre ce jeu de test :
CREATE TABLE t1 (c1 integer);
INSERT INTO t1 SELECT generate_series(1, 1_000_000);
ALTER TABLE t1 ADD PRIMARY KEY (c1);
VACUUM ANALYZE t1;
Voici le plan d’exécution de la première requête en version 17 :
Hash Join (cost=30832.00..59603.01 rows=1000000 width=8)
Hash Cond: (t1.c1 = t1bis.c1)
-> Seq Scan on t1 (cost=0.00..14425.00 rows=1000000 width=4)
-> Hash (cost=14425.00..14425.00 rows=1000000 width=4)
-> Seq Scan on t1 t1bis (cost=0.00..14425.00 rows=1000000 width=4)
La jointure est bien réalisée. Et voici maintenant le plan d’exécution en version 18 :
Seq Scan on t1 t1bis (cost=0.00..14425.00 rows=1000000 width=8)
Il n’y a plus trace de la jointure. Le coût est de ce fait inférieur et la durée d’exécution l’est aussi : 530 ms en version 17, contre 185 ms en version 18. De plus, la durée d’optimisation/planification de la requête est elle aussi inférieure avec cette optimisation : 0,20 ms contre 0,06 ms.
Dans le cas où la contrainte n’est plus une clé primaire, mais une simple contrainte d’unicité, le plan en sera un peu impacté :
Seq Scan on t1 t1bis (cost=0.00..14425.00 rows=1000000 width=8)
Filter: (c1 IS NOT NULL)
En effet, le filtre est nécessaire pour respecter la requête d’origine. Ce filtre supplémentaire a un petit impact sur la durée d’exécution de la requête, étant donné qu’elle passe à 220 ms.
Il est à noter que cette optimisation fonctionne aussi avec d’autres formes de cette requête, par exemple :
SELECT *
FROM t1
WHERE EXISTS
(
SELECT 1 FROM t1 t1bis WHERE t1.c1 = t1bis.c1
);
Enfin, il faut savoir que cette optimisation est débrayable en configurant le
paramètre enable_self_join_elimination à off en sachant qu’il est à on par
défaut.
Pour terminer, il est évident qu’il est préférable de bien écrire les requêtes mais quand l’application qui exécute la requête est propriétaire, il n’est pas possible de modifier les requêtes exécutées. Quand un développeur utilise un ORM, il n’a pas toujours la possibilité de changer la requête exécutée par l’ORM. Et enfin, ce genre de requête peut aussi provenir de plusieurs vues imbriquées qui finissent par en arriver à ce genre d’aberration. Cette optimisation qui semble si évidente n’est pas du tout triviale : le commit a vingt-trois contributeurs, dont les plus aguerris des développeurs de PostgreSQL, et la discussion avait démarré en 2018 !
