Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra. # Optimisation des performances des requêtes dans Aurora PostgreSQL L’optimisation des performances des requêtes est cruciale, car elle permet aux bases de données de fonctionner plus rapidement et plus efficacement tout en utilisant moins de ressources, ce qui se traduit par une meilleure expérience utilisateur et des coûts d’exploitation réduits. Amazon Aurora PostgreSQL fournit plusieurs fonctionnalités permettant d’optimiser les performances des requêtes pour les charges de travail PostgreSQL. **Topics** + [Amélioration des performances des requêtes pour Aurora PostgreSQL avec Aurora Optimized Reads](AuroraPostgreSQL.optimized.reads.md) + [Optimisation des sous-requêtes corrélées dans Aurora PostgreSQL](apg-correlated-subquery.md) + [Amélioration des performances des requêtes à l’aide d’une jointure adaptative](user-apg-adaptive-join.md) + [Utilisation du cache de plan partagé](apg-shared-plan-cache.md) # Amélioration des performances des requêtes pour Aurora PostgreSQL avec Aurora Optimized Reads Vous pouvez accélérer le traitement des requêtes pour Aurora PostgreSQL avec Aurora Optimized Reads. Une instance de base de données Aurora PostgreSQL qui utilise Aurora Optimized Reads permet d’améliorer jusqu’à 8 fois le temps de latence des requêtes et de réaliser des économies pouvant atteindre 30 % pour les applications comportant des jeux de données volumineux, qui dépassent la capacité de mémoire d’une instance de base de données. **Topics** + [Présentation d’Aurora Optimized Reads dans PostgreSQL](#AuroraPostgreSQL.optimized.reads.overview) + [Utilisation d’Aurora Optimized Reads](#AuroraPostgreSQL.optimized.reads.using) + [Cas d’utilisation d’Aurora Optimized Reads](#AuroraPostgreSQL.optimized.reads.usecases) + [Surveillance des instances de base de données qui utilisent Aurora Optimized Reads](#AuroraPostgreSQL.optimized.reads.monitoring) + [Bonnes pratiques pour Aurora Optimized Reads](#AuroraPostgreSQL.optimized.reads.bestpractices) ## Présentation d’Aurora Optimized Reads dans PostgreSQL Aurora Optimized Reads est disponible par défaut lorsque vous créez un cluster de base de données qui utilise des instances R6gd, R8gd et Intel R6id basées sur Graviton avec un stockage non volatile memory express (). NVMe Il est disponible à partir des versions PostgreSQL suivantes : + Versions 14.12 et supérieures, versions 15.7 et supérieures, versions 16.3 et supérieures, versions 17.4 et supérieures pour les instances R8gd + Versions 14.9 et supérieures, versions 15.4 et supérieures, 16.1 et toutes les versions supérieures pour les instances R6gd et R6id Aurora Optimized Reads prend en charge deux fonctionnalités : le cache à plusieurs niveaux et les objets temporaires. **Cache à plusieurs niveaux Optimized Reads :** grâce au cache à plusieurs niveaux, vous pouvez étendre la capacité de mise en cache de votre instance de base de données jusqu’à 5 fois la mémoire de l’instance. Cela permet de maintenir le cache à jour automatiquement de manière à ce qu’il contienne les données les plus récentes et les plus homogènes sur le plan transactionnel, et de libérer ainsi les applications de la charge de gestion de la mise à jour des données des solutions de mise en cache externes basées sur des ensembles de résultats. Il réduit jusqu’à 8 fois le temps de latence des requêtes qui récupéraient auparavant des données du stockage Aurora. Dans Aurora, la valeur pour `shared_buffers` dans le groupe de paramètres par défaut est généralement définie sur environ 75 % de la mémoire disponible. Toutefois, pour les types d'instances r8gd, r6gd et r6id, Aurora réduit l'`shared_buffers`espace de 4,5 % pour héberger les métadonnées du cache de lectures optimisées. **Objets temporaires optimisés compatibles Reads** : à l'aide d'objets temporaires, vous pouvez accélérer le traitement des requêtes en plaçant les fichiers temporaires générés par PostgreSQL sur le stockage local. NVMe Cela réduit le trafic à destination d’Elastic Block Storage (EBS) sur le réseau. La latence et le débit sont jusqu’à deux fois supérieurs pour les requêtes avancées qui trient, joignent ou fusionnent les gros volumes de données qui ne rentrent pas dans la capacité de mémoire disponible sur une instance de base de données. Sur un cluster optimisé pour les E/S Aurora, Optimized Reads utilise à la fois le cache hiérarchisé et les objets temporaires stockés. NVMe Grâce au cache à plusieurs niveaux Optimized Reads, Aurora alloue deux fois la mémoire de l’instance aux objets temporaires, environ 10 % de l’espace de stockage aux opérations internes et le reste du stockage sous forme de cache à plusieurs niveaux. Sur un cluster Aurora standard, Optimized Reads utilise uniquement des objets temporaires. Les clusters optimisés pour les E/S Aurora vous permettent de redimensionner l’espace alloué aux objets temporaires compatibles avec Optimized Reads à l’aide du paramètre dynamique `aurora_temp_space_size` au niveau de l’instance. Cette fonctionnalité de redimensionnement est disponible à partir des versions PostgreSQL suivantes : + Version 16.8 et toutes les versions ultérieures + 15.12 et versions 15 ultérieures + 14.17 et versions 14 ultérieures Avec ce paramètre, vous pouvez redimensionner la capacité pour qu’elle atteigne de 2 à 6 fois la mémoire de l’instance sans avoir à redémarrer le moteur de base de données. Lorsque vous élargissez l’espace des objets temporaires, cette modification prend effet immédiatement, quelles que soient les charges de travail simultanées. Toutefois, lorsque vous réduisez l’espace, cette modification ne prend effet qu’une fois qu’il y a suffisamment d’espace inutilisé dans les objets temporaires pour répondre à la nouvelle demande de taille. Une fois que vous avez redimensionné des objets temporaires compatibles avec Optimized Reads, le cache à plusieurs niveaux s’ajuste automatiquement pour utiliser l’espace disponible. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.optimized.reads.html) **Note** Un basculement entre des clusters optimisés pour l'IO et des clusters standard sur une classe d'instance de base de données NVMe basée entraîne le redémarrage immédiat du moteur de base de données. Dans Aurora PostgreSQL, utilisez le paramètre `temp_tablespaces` pour configurer l’espace de la table sur lequel les objets temporaires seront stockés. Pour vérifier si les objets temporaires sont configurés, utilisez la commande suivante : ``` postgres=> show temp_tablespaces; temp_tablespaces --------------------- aurora_temp_tablespace (1 row) ``` `aurora_temp_tablespace`Il s'agit d'un tablespace configuré par Aurora qui pointe vers le stockage NVMe local. Vous ne pouvez pas modifier ce paramètre ou revenir au stockage Amazon EBS. Pour vérifier si le cache Optimized Reads est activé, utilisez la commande suivante : ``` postgres=> show shared_preload_libraries; shared_preload_libraries -------------------------------------------------------- rdsutils,pg_stat_statements,aurora_optimized_reads_cache ``` ## Utilisation d’Aurora Optimized Reads Lorsque vous provisionnez une instance de base de données Aurora PostgreSQL avec l'une des instances de base de données basées, NVMe l'instance de base de données utilise automatiquement les lectures optimisées Aurora. Pour activer Aurora Optimized Reads, effectuez l’une des actions suivantes : + Créez un cluster de base de données Aurora PostgreSQL à l'aide de l'une des classes d'instances de base de données basées sur NVMe la base de données. Pour de plus amples informations, veuillez consulter [Création d’un cluster de bases de données Amazon Aurora](Aurora.CreateInstance.md). + Modifiez un cluster de base de données Aurora PostgreSQL existant pour utiliser l'une des classes d'instances de base de données basées sur NVMe la base de données. Pour de plus amples informations, veuillez consulter [Modification d’un cluster de bases de données Amazon Aurora](Aurora.Modifying.md). Aurora Optimized Reads est disponible partout Régions AWS où une ou plusieurs classes d'instances de base de données avec stockage NVMe SSD local sont prises en charge. Pour de plus amples informations, veuillez consulter [Classes d'instances de base de données Amazon Aurora](Concepts.DBInstanceClass.md). Pour revenir à une instance Aurora en lecture non optimisée, modifiez la classe d'instance de base de données de votre instance Aurora en une classe d'instance similaire sans stockage NVMe éphémère pour les charges de travail de votre base de données. Par exemple, si la classe d’instance de base de données actuelle est db.r6gd.4xlarge, choisissez db.r6g.4xlarge pour revenir en arrière. Pour plus d’informations, consultez [Modification d’une instance de base de données Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.DBInstance.Modifying.html). ## Cas d’utilisation d’Aurora Optimized Reads **Cache à plusieurs niveaux Optimized Reads** Voici quelques cas d’utilisation du cache à plusieurs niveaux Optimized Reads : + Applications à l'échelle d'Internet telles que le traitement des paiements, la facturation, le commerce électronique avec des performances strictes SLAs. + Des tableaux de bord de reporting en temps réel qui exécutent des centaines de requêtes ponctuelles à des fins de metrics/data collecte. + Applications d’IA générative avec l’extension pgvector pour rechercher des voisins exacts ou les voisins les plus proches parmi des millions de vecteurs intégrés. **Objets temporaires Optimized Reads** Voici quelques cas d’utilisation des objets temporaires Optimized Reads : + Requêtes analytiques qui incluent des expressions de table communes (CTEs), des tables dérivées et des opérations de regroupement. + Réplicas en lecture qui gèrent les requêtes non optimisées pour une application. + Requêtes de création de rapports dynamiques ou à la demande avec des opérations complexes telles que GROUP BY et ORDER BY qui ne peuvent pas toujours utiliser les index appropriés. + Opérations `CREATE INDEX` ou `REINDEX` pour le tri. + Autres charges de travail utilisant des tables temporaires internes. ## Surveillance des instances de base de données qui utilisent Aurora Optimized Reads Vous pouvez surveiller vos requêtes qui utilisent le cache à plusieurs niveaux Optimized Reads à l’aide de la commande EXPLAIN comme illustré dans l’exemple suivant : ``` Postgres=> EXPLAIN (ANALYZE, BUFFERS) SELECT c FROM sbtest15 WHERE id=100000000 QUERY PLAN -------------------------------------------------------------------------------------- Index Scan using sbtest15_pkey on sbtest15 (cost=0.57..8.59 rows=1 width=121) (actual time=0.287..0.288 rows=1 loops=1) Index Cond: (id = 100000000) Buffers: shared hit=3 read=2 aurora_orcache_hit=2 I/O Timings: shared/local read=0.264 Planning: Buffers: shared hit=33 read=6 aurora_orcache_hit=6 I/O Timings: shared/local read=0.607 Planning Time: 0.929 ms Execution Time: 0.303 ms (9 rows) Time: 2.028 ms ``` **Note** Les champs `aurora_orcache_hit` et `aurora_storage_read` de la section `Buffers` du plan d’explication ne sont affichés que lorsqu’Optimized Reads est activé et que le résultat est supérieur à zéro. Le champ de lecture est le total des champs `aurora_orcache_hit` et `aurora_storage_read`. Vous pouvez surveiller les instances de base de données qui utilisent les lectures optimisées Aurora à l'aide CloudWatch des métriques suivantes : + `AuroraOptimizedReadsCacheHitRatio` + `FreeEphemeralStorage` + `ReadIOPSEphemeralStorage` + `ReadLatencyEphemeralStorage` + `ReadThroughputEphemeralStorage` + `WriteIOPSEphemeralStorage` + `WriteLatencyEphemeralStorage` + `WriteThroughputEphemeralStorage` Ces métriques fournissent des données sur le stockage disponible dans le stockage d’instances, les IOPS et le débit. Pour plus d’informations sur ces métriques, consultez [Métriques de niveau instance pour Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances). Vous pouvez également utiliser l'`pg_proctab`extension pour surveiller le NVMe stockage. ``` postgres=>select * from pg_diskusage(); major | minor | devname | reads_completed | reads_merged | sectors_read | readtime | writes_completed | writes_merged | sectors_written | writetime | current_io | iotime | totaliotime ------+-------+---------------------+-----------------+--------------+--------------+----------+------------------+---------------+-----------------+-----------+------------+---------+------------- | | rdstemp | 23264 | 0 | 191450 | 11670 | 1750892 | 0 | 24540576 | 819350 | 0 | 3847580 | 831020 | | rdsephemeralstorage | 23271 | 0 | 193098 | 2620 | 114961 | 0 | 13845120 | 130770 | 0 | 215010 | 133410 (2 rows) ``` ## Bonnes pratiques pour Aurora Optimized Reads Utilisez les bonnes pratiques suivantes pour Aurora Optimized Reads : + Surveillez l'espace de stockage disponible sur le magasin d'instances à l'aide de la CloudWatch métrique`FreeEphemeralStorage`. Si le stockage d’instances atteint sa limite en raison de la charge de travail de l’instance de base de données, ajustez la simultanéité et les requêtes qui utilisent des objets temporaires de manière intensive, ou modifiez le stockage d’instances pour qu’il utilise une classe d’instance de base de données plus grande. + Surveillez la CloudWatch métrique du taux de réussite du cache Optimized Reads. Des opérations telles que VACUUM modifient très rapidement un grand nombre de blocs. Cela peut entraîner une baisse temporaire du taux d’accès. L’extension `pg_prewarm` peut être utilisée pour charger des données dans le cache de tampon, ce qui permet à Aurora d’écrire de manière proactive certains de ces blocs dans le cache Optimized Reads. + Vous pouvez activer la gestion du cache de cluster pour réchauffer le cache d tampon et le cache à plusieurs niveaux sur un lecteur de niveau 0, qui sera utilisé comme cible de basculement. Lorsque la gestion du cache de cluster est activée, le cache de tampon est scanné périodiquement pour écrire les pages susceptibles d’être expulsées dans le cache à plusieurs niveaux. Pour plus d’informations sur la gestion du cache de cluster, consultez [Récupération rapide après basculement avec la gestion des caches de clusters pour Aurora PostgreSQL](AuroraPostgreSQL.cluster-cache-mgmt.md). # Optimisation des sous-requêtes corrélées dans Aurora PostgreSQL Une sous-requête corrélée référence les colonnes de la table à partir de la requête externe. Elle est évaluée une fois pour chaque ligne renvoyée par la requête externe. Dans l’exemple suivant, la sous-requête référence une colonne de la table ot. Cette table n’est pas incluse dans la clause FROM de la sous-requête, mais elle est référencée dans la clause FROM de la requête externe. Si la table ot contient 1 million de lignes, la sous-requête doit être évaluée 1 million de fois. ``` SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); ``` **Note** La transformation des sous-requêtes et le cache de sous-requêtes sont disponibles dans Aurora PostgreSQL à partir de la version 16.8, tandis que Babelfish pour Aurora PostgreSQL prend en charge ces fonctionnalités à partir de la version 4.2.0. À partir des versions 4.6.0 et 5.2.0 de Babelfish pour Aurora PostgreSQL, les paramètres suivants contrôlent ces fonctionnalités : babelfishpg\$1tsql.apg\$1enable\$1correlated\$1scalar\$1transform babelfishpg\$1tsql.apg\$1enable\$1subquery\$1cache Par défaut, ces deux paramètres sont activés. ## Amélioration des performances des requêtes Aurora PostgreSQL à l’aide de la transformation des sous-requêtes Aurora PostgreSQL peut accélérer les sous-requêtes corrélées en les transformant en jointures externes équivalentes. Cette optimisation s’applique aux deux types de sous-requêtes corrélées suivants : + Sous-requêtes renvoyant une valeur agrégée unique et apparaissant dans la liste SELECT. ``` SELECT ot.a, ot.b, (SELECT AVG(it.b) FROM it WHERE it.a = ot.a) FROM ot; ``` + Sous-requêtes renvoyant une valeur agrégée unique et apparaissant dans une clause WHERE. ``` SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); ``` ### Activation de la transformation dans la sous-requête Pour activer la transformation des sous-requêtes corrélées en jointures externes équivalentes, définissez le paramètre `apg_enable_correlated_scalar_transform` sur `ON`. La valeur par défaut de ce paramètre est `OFF`. Vous pouvez modifier les paramètres du cluster ou de l’instance pour définir les paramètres. Pour en savoir plus, consultez [Groupes de paramètres pour Amazon Aurora](USER_WorkingWithParamGroups.md). Vous pouvez également configurer le paramètre uniquement pour la session en cours à l’aide de la commande suivante : ``` SET apg_enable_correlated_scalar_transform TO ON; ``` ### Vérification de la transformation Utilisez la commande EXPLAIN pour vérifier si la sous-requête corrélée a été transformée en jointure externe dans le plan de requête. Lorsque la transformation est activée, la partie de sous-requête corrélée applicable est transformée en jointure externe. Par exemple : ``` postgres=> CREATE TABLE ot (a INT, b INT); CREATE TABLE postgres=> CREATE TABLE it (a INT, b INT); CREATE TABLE postgres=> SET apg_enable_correlated_scalar_transform TO ON; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); QUERY PLAN -------------------------------------------------------------- Hash Join Hash Cond: (ot.a = apg_scalar_subquery.scalar_output) Join Filter: ((ot.b)::numeric < apg_scalar_subquery.avg) -> Seq Scan on ot -> Hash -> Subquery Scan on apg_scalar_subquery -> HashAggregate Group Key: it.a -> Seq Scan on it ``` La même requête n’est pas transformée lorsque le paramètre GUC est activé (`OFF`). Le plan n’aura pas de jointure externe mais un sous-plan à la place. ``` postgres=> SET apg_enable_correlated_scalar_transform TO OFF; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); QUERY PLAN ---------------------------------------- Seq Scan on ot Filter: ((b)::numeric < (SubPlan 1)) SubPlan 1 -> Aggregate -> Seq Scan on it Filter: (a = ot.a) ``` ### Limitations + La sous-requête doit figurer dans la liste SELECT ou dans l’une des conditions de la clause WHERE. Dans le cas contraire, elle ne sera pas transformée. + La sous-requête doit renvoyer une fonction d’agrégation. Les fonctions d’agrégation définies par l’utilisateur ne sont pas prises en charge pour la transformation. + Une sous-requête dont l’expression de retour n’est pas une fonction d’agrégation simple ne sera pas transformée. + La condition corrélée dans les clauses WHERE de la sous-requête doit être une référence de colonne simple. Dans le cas contraire, elle ne sera pas transformée. + La condition corrélée dans les clauses WHERE de la sous-requête doit être un simple prédicat d’égalité. + La sous-requête ne peut pas contenir de clause HAVING ou GROUP BY. + La clause WHERE de la sous-requête peut contenir un ou plusieurs prédicats combinés à AND. **Note** L’impact de la transformation sur les performances varie en fonction de votre schéma, de vos données et de votre charge de travail. L’exécution de sous-requêtes corrélées avec la transformation peut améliorer considérablement les performances à mesure que le nombre de lignes générées par la requête externe augmente. Nous vous recommandons vivement de tester cette fonctionnalité dans un environnement hors production avec votre schéma, vos données et votre charge de travail réels avant de l’activer dans un environnement de production. ## Utilisation du cache de sous-requêtes pour améliorer les performances des requêtes Aurora PostgreSQL Aurora PostgreSQL prend en charge un cache de sous-requêtes pour stocker les résultats des sous-requêtes corrélées. Cette fonctionnalité ignore les exécutions répétées de sous-requêtes corrélées lorsque leurs résultats se trouvent déjà dans le cache. ### Comprendre le cache de sous-requêtes Le nœud Memoize de PostgreSQL est l’élément clé du cache de sous-requêtes. Le nœud Memoize gère une table de hachage dans le cache local pour mapper les valeurs des paramètres d’entrée aux lignes de résultats des requêtes. La limite de mémoire de la table de hachage est le produit de work\$1mem et hash\$1mem\$1multiplier. Pour en savoir plus, consultez [Consommation des ressources](https://www.postgresql.org/docs/16/runtime-config-resource.html). Lors de l’exécution des requêtes, le cache de sous-requêtes utilise le taux d’accès au cache pour estimer si le cache améliore les performances des requêtes et pour décider, au moment de l’exécution d’une requête, s’il convient de continuer à utiliser le cache. Le taux d’accès au cache est le rapport entre le nombre d’accès au cache et le nombre total de requêtes. Par exemple, si une sous-requête corrélée doit être exécutée 100 fois et que 70 de ces résultats d’exécution peuvent être extraits du cache, le taux d’accès au cache est de 0,7. Pour chaque perte de mémoire cache par apg\$1subquery\$1cache\$1interval, l’avantage du cache de sous-requêtes est évalué en vérifiant si le taux d’accès au cache est supérieur à apg\$1subquery\$1cache\$1hit\$1rate\$1threshold. Si ce n’est pas le cas, le cache sera supprimé de la mémoire, et l’exécution de la requête reviendra à la réexécution initiale de la sous-requête non mise en cache. ### Paramètres qui contrôlent le comportement du cache de sous-requêtes Le tableau suivant répertorie les paramètres qui contrôlent le comportement du cache de sous-requêtes. | Paramètre | Description | Par défaut | Autorisé | | --- | --- | --- | --- | | apg\$1enable\$1subquery\$1cache | Permet d’utiliser le cache pour les sous-requêtes scalaires corrélées. | OFF | ON, OFF | | apg\$1subquery\$1cache\$1check\$1interval | Définit la fréquence, en nombre de pertes de mémoire cache, pour évaluer le taux d’accès au cache des sous-requêtes. | 500 | 0–2147483647 | | apg\$1subquery\$1cache\$1hit\$1rate\$1threshold | Définit le seuil du taux d’accès au cache de sous-requêtes. | 0.3 | 0.0-1.0 | **Note** Des valeurs plus élevées pour `apg_subquery_cache_check_interval` peuvent améliorer la précision de l’estimation des avantages du cache basée sur le taux d’accès au cache, mais augmentent la surcharge du cache, car le taux d’accès au cache ne sera pas évalué tant que la table de cache ne contiendra pas le nombre de lignes défini pour `apg_subquery_cache_check_interval`. Des valeurs plus élevées pour `apg_subquery_cache_hit_rate_threshold` indiquent un biais en faveur de l’abandon du cache de sous-requêtes et du retour à la réexécution des sous-requêtes d’origine, non mises en cache. Vous pouvez modifier les paramètres du cluster ou de l’instance pour définir les paramètres. Pour en savoir plus, consultez [Groupes de paramètres pour Amazon Aurora](USER_WorkingWithParamGroups.md). Vous pouvez également configurer le paramètre uniquement pour la session en cours à l’aide de la commande suivante : ``` SET apg_enable_subquery_cache TO ON; ``` ### Activation du cache de sous-requêtes dans Aurora PostgreSQL Lorsque le cache de sous-requêtes est activé, Aurora PostgreSQL l’applique pour enregistrer les résultats des sous-requêtes. Le plan de requête comportera alors un nœud Memoize en dessous. SubPlan Par exemple, la séquence de commandes suivante montre le plan d’exécution estimé d’une sous-requête simple corrélée sans cache de sous-requêtes. ``` postgres=> SET apg_enable_subquery_cache TO OFF; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a); QUERY PLAN ------------------------------------ Seq Scan on ot Filter: (b < (SubPlan 1)) SubPlan 1 -> Seq Scan on it Filter: (a = ot.a) ``` Une fois activé`apg_enable_subquery_cache`, le plan de requête contiendra un nœud Memoize sous le SubPlan nœud, indiquant que la sous-requête prévoit d'utiliser le cache. ``` postgres=> SET apg_enable_subquery_cache TO ON; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a); QUERY PLAN ------------------------------------ Seq Scan on ot Filter: (b < (SubPlan 1)) SubPlan 1 -> Memoize Cache Key: ot.a Cache Mode: binary -> Seq Scan on it Filter: (a = ot.a) ``` Le plan d’exécution des requêtes proprement dit contient plus de détails sur le cache de sous-requêtes, y compris les accès au cache et les pertes de mémoire cache. La sortie suivante montre le plan d’exécution réel de l’exemple de requête ci-dessus après l’insertion de certaines valeurs dans les tables. ``` postgres=> EXPLAIN (COSTS FALSE, TIMING FALSE, ANALYZE TRUE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a); QUERY PLAN ----------------------------------------------------------------------------- Seq Scan on ot (actual rows=2 loops=1) Filter: (b < (SubPlan 1)) Rows Removed by Filter: 8 SubPlan 1 -> Memoize (actual rows=0 loops=10) Cache Key: ot.a Cache Mode: binary Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on it (actual rows=0 loops=6) Filter: (a = ot.a) Rows Removed by Filter: 4 ``` Le nombre total d’accès au cache est de 4, et le nombre total de pertes de mémoire cache est de 6. Si le nombre total d’accès au cache et de pertes de cache mémoire est inférieur au nombre de boucles dans le nœud Memoize, cela signifie que l’évaluation du taux d’accès au cache n’a pas été réussie et que le cache a été nettoyé et abandonné à un moment donné. L’exécution de la sous-requête est ensuite renvoyée à la réexécution initiale non mise en cache. ### Limitations Le cache de sous-requêtes ne prend pas en charge certains modèles de sous-requêtes corrélées. Ces types de requêtes sont exécutés sans cache, même si le cache de sous-requêtes est activé : + IN/EXISTS/ANY/ALLsous-requêtes corrélées + Sous-requêtes corrélées contenant des fonctions non déterministes. + Sous-requêtes corrélées qui font référence à des colonnes de table externes avec des types de données qui ne prennent pas en charge les opérations de hachage ou d’égalité. # Amélioration des performances des requêtes à l’aide d’une jointure adaptative ## Présentation de La jointure adaptative est une fonctionnalité de présentation d’Aurora PostgreSQL 17.4 qui permet d’améliorer les performances des requêtes. Cette fonctionnalité est désactivée par défaut, mais vous pouvez l’activer à l’aide des paramètres de configuration utilisateur globale (GUC). Comme il s’agit d’une fonctionnalité de présentation, les valeurs des paramètres par défaut peuvent changer. Lorsqu’elle est activée, la jointure adaptative permet d’optimiser les performances des requêtes en passant dynamiquement d’une jointure de boucle imbriquée à une jointure de hachage lors de l’exécution. Ce changement se produit lorsque l’optimiseur PostgreSQL a mal choisi une jointure de boucle imbriquée en raison d’estimations de cardinalité inexactes. ## Configuration de jointure adaptative Vous pouvez contrôler la jointure adaptative à l’aide de trois paramètres GUC suivants : **Paramètres de configuration de jointure adaptative** | Paramètre GUC | Description | Options par défaut et de configuration | | --- | --- | --- | | apg\$1adaptive\$1join\$1crossover\$1multiplicateur | Ce multiplicateur fonctionne avec le point de croisement de ligne pour déterminer quand passer d’une boucle imbriquée à une jointure de hachage. Le point de croisement de ligne est l’endroit où l’optimiseur SQL estime que les opérations de boucle imbriquée et de jointure de hachage ont le même coût. Une valeur de multiplicateur plus élevée réduit la probabilité que la jointure adaptative passe à une jointure de hachage. | Contrôle si la jointure adaptative est activée [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) | | apg\$1adaptive\$1join\$1cost\$1threshold | Ce paramètre définit un seuil de coût minimal pour les requêtes. La jointure adaptative se désactive automatiquement pour les requêtes inférieures à ce seuil. Cela permet d’éviter les surcharges de performances dans les requêtes simples où le coût de planification d’une jointure adaptative pourrait dépasser les avantages du passage d’une boucle imbriquée à une jointure de hachage. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) | | apg\$1enable\$1parameterized\$1adaptive\$1join | Ce paramètre, lorsqu’il est activé, étend la fonctionnalité de jointure adaptative aux jointures par boucles imbriquées paramétrées. Par défaut, la jointure adaptative fonctionne uniquement avec les jointures par boucle imbriquées non paramétrées, car elles sont plus susceptibles de bénéficier du passage à la jointure de hachage. Les jointures par boucle imbriquée paramétrées sont généralement plus performantes, ce qui rend le passage à la jointure de hachage moins critique. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) Il est nécessaire que le `apg_adaptive_join_crossover_multiplier` soit activé au préalable | # Utilisation du cache de plan partagé ## Présentation de Aurora PostgreSQL utilise process-per-user un modèle dans lequel chaque connexion client crée un processus principal dédié. Chaque processus principal gère son propre cache de plan local pour les instructions préparées. Comme ces caches ne peuvent pas être partagés entre les processus, les applications qui utilisent de nombreuses instructions préparées peuvent créer des caches dupliqués entre différents processus principaux, ce qui entraîne une augmentation de l'utilisation de la mémoire. Les versions 17.6 et ultérieures d'Aurora PostgreSQL et 16.10 et versions ultérieures introduisent la fonctionnalité de cache de plan partagé. Lorsque vous activez cette fonctionnalité, les processus principaux peuvent partager des plans génériques, ce qui réduit l'utilisation de la mémoire et améliore les performances en éliminant la génération de plans dupliqués. Le cache du plan partagé utilise les composants suivants comme clé de cache : + Chaîne de requête (y compris les commentaires) + Paramètres GUC liés au planificateur (y compris) `search_path` + ID de l’utilisateur + ID de base de données Les redémarrages de l'instance réinitialisent le cache partagé. ## Parameters Le tableau suivant décrit les paramètres qui contrôlent la fonctionnalité de cache du plan partagé : | Paramètre | Description | Par défaut | Autorisé | | --- | --- | --- | --- | | apg\$1shared\$1plan\$1cache.enable | Active ou désactive le cache du forfait partagé | 0 (DÉSACTIVÉ) | 0, 1 | | apg\$1shared\$1plan\$1cache.max | Le nombre maximum d'entrées de cache | 200 à 1 000 (selon la taille de l'instance) | 100—50 000 | | apg\$1shared\$1plan\$1cache.min\$1size\$1per\$1entry | La taille minimale du plan à stocker dans le cache partagé. Les petits forfaits utilisent le cache local pour optimiser les performances OLTP. | 16 Ko | 0—32768 (KO) | | apg\$1shared\$1plan\$1cache.max\$1size\$1per\$1entry | Taille maximale du plan pour le cache partagé. Les plans plus importants ne stockent que les informations sur les coûts. | 256 Ko à 4 Mo (selon la taille de l'instance) | 0—32768 (KO) | | apg\$1shared\$1plan\$1cache.idle\$1generic\$1plan\$1release\$1timeout | Délai après lequel les sessions inactives publient des plans génériques locaux. Des valeurs faibles permettent d'économiser de la mémoire ; des valeurs élevées peuvent améliorer les performances. | 10 secondes | 0 — 2147483647 (ms) | **Note** Vous pouvez modifier tous les paramètres sans redémarrer. ## Surveillance des vues et des fonctions + `apg_shared_plan_cache()`— Affiche des informations détaillées sur les entrées dans le cache (accès, validité, horodatage) + `apg_shared_plan_cache_stat()`— Affiche les statistiques au niveau de l'instance (expulsions, invalidations) + `apg_shared_plan_cache_reset()`— Supprime toutes les entrées dans `apg_shared_plan_cache()` et `apg_shared_plan_cache_stat()` + `apg_shared_plan_cache_remove(cache_key)`— Supprime une entrée `apg_shared_plan_cache()` dont elle correspond à `cache_key` ## Limitations + Fonctionne uniquement avec les instructions préparées et ne met pas les PL/pgSQL instructions en cache + Ne met pas en cache une requête contenant des tables temporaires ou des tables de catalogue + Ne met pas en cache une requête qui dépend du protocole RLS (Row-Level Security) + Chaque réplique conserve son propre cache (pas de partage entre répliques)