La migration de bases de données, notamment d'Oracle vers PostgreSQL, peut parfois révéler des défis inattendus en matière de performances. Un scénario courant est la gestion des sous-requêtes, en particulier celles utilisant les opérateurs IN et NOT IN. Dans les versions antérieures de PostgreSQL, l'optimiseur avait des limitations, ne parvenant à transformer efficacement en jointures que les sous-requêtes IN les plus simples. Les cas plus complexes, ou l'utilisation de NOT IN, exigeaient souvent une réécriture manuelle en EXISTS ou NOT EXISTS pour maintenir des performances acceptables.
Avec l'introduction de PostgreSQL 17, une évolution significative a été apportée à l'optimiseur de requêtes, permettant la transformation automatique de sous-requêtes IN corrélées en jointures. Cette amélioration vise à simplifier les migrations et à réduire la charge de travail des développeurs en matière d'optimisation manuelle du SQL. Cet article explore cette nouvelle capacité à travers des exemples concrets, en comparant les plans d'exécution entre PostgreSQL 17 et les versions précédentes.
Pour nos démonstrations, nous utiliserons un schéma de base de données inspiré du système de gestion des réservations aériennes.
- Le Cas de l'Opérateur
NOT IN
Nous commençons par examiner si l'opérateur NOT IN bénéficie également de ces nouvelles optimisations. Reprenons une requête similaire à celle qui posait problème dans les versions antérieures lors des migrations.
EXPLAIN SELECT COUNT(*) FROM reservations r WHERE r.compte_id NOT IN (SELECT c.compte_id FROM comptes_client c);
Voici le plan d'exécution obteenu sur PostgreSQL 17 :
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=43138248424.04..43138248424.05 rows=1 width=8)
-> Gather (cost=43138248423.82..43138248424.03 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=43138247423.82..43138247423.83 rows=1 width=8)
-> Parallel Seq Scan on reservations r (cost=0.00..43138244322.60 rows=1240490 width=0)
Filter: (NOT (ANY (compte_id = (SubPlan 1).col1)))
SubPlan 1
-> Materialize (cost=0.00..31970.50 rows=1121833 width=4)
-> Seq Scan on comptes_client c (cost=0.00..21978.33 rows=1121833 width=4)
(9 rows)
Le plan d'exécution révèle toujours la présence du terme SubPlan 1. Cela indique que, pour les scénarios utilisant NOT IN, l'optimiseur de PostgreSQL 17 ne réalise pas encore de transformation automatique en anti-jointure. Nous pouvons espérer que les futures versions de PostgreSQL étendront cette capacité à NOT IN.
- Améliorations pour les Sous-Requêtes
IN
Les précédentes versions de PostgreSQL présentaient des limites dans l'optimisation des sous-requêtes IN, notamment :
- Les sous-liens étaient souvent traités comme de simples sous-requêtes non optimisées.
- Difficulté à gérer les références de variables à différents niveaux (
varlevelsup=1). - Manque de support pour la promotion des sous-requêtes en
LATERAL JOIN.
PostgreSQL 17 adresse ces points. Pour observer ces transformations, outre l'analyse des plans d'exécution, le paramètre de débogage debug_print_rewritten peut être activé pour visualiser la représentatoin interne de la requête après son passage par les règles de réécriture.
2.1. Sous-requête IN corrélée imbriquée
Considérons une requête complexe avec plusieurs niveaux d'imbrication et une corrélation, difficile à optimiser dans les versions antérieures.
EXPLAIN (COSTS OFF, ANALYZE)
SELECT c.id_commande, c.id_client, c.montant_total
FROM commandes c
WHERE c.id_commande IN (
SELECT lc.id_commande
FROM lignes_commande lc
WHERE lc.id_produit IN (
SELECT p.id_produit
FROM produits p
WHERE p.categorie = 'Électronique' AND p.prix > c.montant_total
)
);
Plan d'exécution sur PostgreSQL 17 :
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (actual time=0.158..0.159 rows=0 loops=1)
-> Seq Scan on commandes c (actual time=0.012..0.013 rows=10 loops=1)
-> Subquery Scan on "ANY_subquery" (actual time=0.014..0.014 rows=0 loops=10)
Filter: ((c.id_commande)::text = ("ANY_subquery".id_commande)::text)
Rows Removed by Filter: 1
-> Nested Loop (actual time=0.011..0.014 rows=1 loops=10)
-> Index Scan using idx_produits_categorie on produits p (actual time=0.005..0.006 rows=1 loops=10)
Index Cond: ((categorie)::text = 'Électronique'::text)
Filter: (prix > c.montant_total)
Rows Removed by Filter: 2
-> Bitmap Heap Scan on lignes_commande lc (actual time=0.005..0.005 rows=1 loops=11)
Recheck Cond: ((id_produit)::text = (p.id_produit)::text)
Heap Blocks: exact=9
-> Bitmap Index Scan on idx_lignes_commande_id_produit (actual time=0.003..0.003 rows=1 loops=11)
Index Cond: ((id_produit)::text = (p.id_produit)::text)
Planning Time: 1.820 ms
Execution Time: 0.242 ms
(17 rows)
Sur PostgreSQL 17, le plan d'exécution ne mentionne plus SubPlan. À la place, nous observons un Nested Loop Semi Join et un Subquery Scan on "ANY_subquery". Cela prouve que l'optimiseur a réussi à réécrire la sous-requête corrélée en une forme plus efficace, souvent équivalente à une jointure.
Plan d'exécusion sur PostgreSQL 14 (à titre de comparaison) :
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on commandes c (actual time=0.076..0.076 rows=0 loops=1)
Filter: (SubPlan 1)
Rows Removed by Filter: 10
SubPlan 1
-> Nested Loop (actual time=0.005..0.007 rows=1 loops=10)
-> Index Scan using idx_produits_categorie on produits p (actual time=0.003..0.003 rows=1 loops=10)
Index Cond: ((categorie)::text = 'Électronique'::text)
Filter: (prix > c.montant_total)
Rows Removed by Filter: 2
-> Bitmap Heap Scan on lignes_commande lc (actual time=0.002..0.002 rows=1 loops=11)
Recheck Cond: ((id_produit)::text = (p.id_produit)::text)
Heap Blocks: exact=9
-> Bitmap Index Scan on idx_lignes_commande_id_produit (actual time=0.002..0.002 rows=1 loops=11)
Index Cond: ((id_produit)::text = (p.id_produit)::text)
Planning Time: 0.471 ms
Execution Time: 0.144 ms
(16 rows)
Le plan de PostgreSQL 14 montre clairement l'exécution de la sous-requête comme un SubPlan, ce qui est généralement moins performant pour les sous-requêtes corrélées, car elle est réévaluée pour chaque ligne de la requête externe.
2.2. Sous-requête IN imbriquée avec INNER JOIN
Testons un autre scénario avec une imbrication plus profonde et une clause INNER JOIN.
EXPLAIN (COSTS OFF, ANALYZE)
SELECT cl.nom_client, p.nom_produit, p.prix
FROM clients cl
INNER JOIN produits p ON p.id_produit IN (
SELECT lc.id_produit
FROM lignes_commande lc
WHERE lc.id_commande IN (
SELECT c.id_commande
FROM commandes c
WHERE c.id_client = cl.id_client
AND c.statut_commande = 'completed'
)
)
WHERE cl.type_client = 'P';
Plan d'exécution sur PostgreSQL 17 :
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (actual time=0.127..0.139 rows=3 loops=1)
-> Bitmap Heap Scan on clients cl (actual time=0.046..0.046 rows=3 loops=1)
Recheck Cond: (type_client = 'P'::bpchar)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_clients_type (actual time=0.027..0.027 rows=3 loops=1)
Index Cond: (type_client = 'P'::bpchar)
-> Nested Loop (actual time=0.029..0.030 rows=1 loops=3)
-> HashAggregate (actual time=0.021..0.021 rows=1 loops=3)
Group Key: ("ANY_subquery".id_produit)::text
Batches: 1 Memory Usage: 24kB
-> Subquery Scan on "ANY_subquery" (actual time=0.017..0.019 rows=1 loops=3)
-> Nested Loop (actual time=0.016..0.018 rows=1 loops=3)
-> Bitmap Heap Scan on commandes c (actual time=0.010..0.010 rows=1 loops=3)
Recheck Cond: ((id_client)::text = (cl.id_client)::text)
Filter: ((statut_commande)::text = 'completed'::text)
Heap Blocks: exact=2
-> Bitmap Index Scan on idx_commandes_client_date (actual time=0.006..0.006 rows=1 loops=3)
Index Cond: ((id_client)::text = (cl.id_client)::text)
-> Bitmap Heap Scan on lignes_commande lc (actual time=0.006..0.006 rows=1 loops=3)
Recheck Cond: ((id_commande)::text = (c.id_commande)::text)
Heap Blocks: exact=3
-> Bitmap Index Scan on idx_lignes_commande_commande_produit (actual time=0.003..0.003 rows=1 loops=3)
Index Cond: ((id_commande)::text = (c.id_commande)::text)
-> Index Scan using produits_pkey on produits p (actual time=0.008..0.008 rows=1 loops=3)
Index Cond: ((id_produit)::text = ("ANY_subquery".id_produit)::text)
Planning Time: 0.594 ms
Execution Time: 0.293 ms
(27 rows)
Encore une fois, PostgreSQL 17 montre une réécriture sophistiquée, transformant les sous-requêtes imbriquées en jointures (Nested Loop, HashAggregate, Subquery Scan on "ANY_subquery"), ce qui est généralement plus performant qu'une exécution de sous-plan pour chaque ligne.
Plan d'exécution sur PostgreSQL 14 (à titre de comparaison) :
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Nested Loop (actual time=0.104..0.208 rows=3 loops=1)
Join Filter: (SubPlan 1)
Rows Removed by Join Filter: 27
-> Seq Scan on produits p (actual time=0.020..0.022 rows=10 loops=1)
-> Materialize (actual time=0.003..0.003 rows=3 loops=10)
-> Bitmap Heap Scan on clients cl (actual time=0.021..0.022 rows=3 loops=1)
Recheck Cond: (type_client = 'P'::bpchar)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_clients_type (actual time=0.012..0.012 rows=3 loops=1)
Index Cond: (type_client = 'P'::bpchar)
SubPlan 1
-> Nested Loop (actual time=0.004..0.005 rows=1 loops=30)
-> Index Scan using idx_commandes_client_date on commandes c (actual time=0.001..0.002 rows=1 loops=30)
Index Cond: ((id_client)::text = (cl.id_client)::text)
Filter: ((statut_commande)::text = 'completed'::text)
-> Bitmap Heap Scan on lignes_commande lc (actual time=0.002..0.002 rows=1 loops=29)
Recheck Cond: ((id_commande)::text = (c.id_commande)::text)
Heap Blocks: exact=29
-> Bitmap Index Scan on idx_lignes_commande_commande_produit (actual time=0.001..0.001 rows=1 loops=29)
Index Cond: ((id_commande)::text = (c.id_commande)::text)
Planning Time: 1.657 ms
Execution Time: 0.307 ms
(22 rows)
Le plan de PostgreSQL 14 révèle la dépendance aux SubPlan, ce qui rend l'exécution de la requête potentiellement moins efficace, surtout avec des jeux de données importants.
- Conclusion
Les démonstrations ci-dessus illustrent clairement l'impact positif des nouvelles fonctionnalités de PostgreSQL 17 sur l'optimisation des sous-requêtes IN. La capacité de l'optimiseur à transformer automatiquement des sous-requêtes corrélées et imbriquées en jointures réduit considérablement la complexité pour les développeurs et améliore la compatibilité avec des requêtes provenant d'autres systèmes de gestion de bases de données, comme Oracle. Bien que l'opérateur NOT IN ne bénéficie pas encore de cette optimisation automatique, cette avancée marque une étape importante vers un optimiseur de requêtes toujours plus intelligent et performant dans PostgreSQL.