Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà. # Ottimizzazione delle prestazioni delle query in Aurora PostgreSQL L’ottimizzazione delle prestazioni delle query è un passaggio fondamentale perché consente ai database di funzionare in modo più rapido ed efficiente utilizzando meno risorse, il che si traduce in una migliore esperienza utente e in una riduzione dei costi operativi. Amazon Aurora PostgreSQL offre diverse funzionalità che aiutano a ottimizzare le prestazioni delle query per i carichi di lavoro PostgreSQL. **Topics** + [Prestazioni delle query migliorate per Aurora PostgreSQL con Letture ottimizzate per Aurora](AuroraPostgreSQL.optimized.reads.md) + [Ottimizzazione delle sottoquery correlate in Aurora PostgreSQL](apg-correlated-subquery.md) + [Miglioramento delle prestazioni delle query utilizzando join adattivo](user-apg-adaptive-join.md) + [Utilizzo della cache del piano condiviso](apg-shared-plan-cache.md) # Prestazioni delle query migliorate per Aurora PostgreSQL con Letture ottimizzate per Aurora Con Letture ottimizzate per Aurora è possibile velocizzare l'elaborazione delle query per Aurora PostgreSQL. Un'istanza database Aurora PostgreSQL che utilizza Letture ottimizzate per Aurora offre una latenza delle query fino a 8 volte migliorata e risparmi sui costi fino al 30% per applicazioni con set di dati di grandi dimensioni, che superano la capacità di memoria di un'istanza database. **Topics** + [Panoramica di Letture ottimizzate per Aurora in PostgreSQL](#AuroraPostgreSQL.optimized.reads.overview) + [Utilizzo di Letture ottimizzate per Aurora](#AuroraPostgreSQL.optimized.reads.using) + [Casi d'uso per letture ottimizzate per Aurora](#AuroraPostgreSQL.optimized.reads.usecases) + [Monitoraggio delle istanze database che utilizzano Letture ottimizzate per Aurora](#AuroraPostgreSQL.optimized.reads.monitoring) + [Best practice per Letture ottimizzate per Aurora](#AuroraPostgreSQL.optimized.reads.bestpractices) ## Panoramica di Letture ottimizzate per Aurora in PostgreSQL Aurora Optimized Reads è disponibile per impostazione predefinita quando si crea un cluster DB che utilizza istanze R6gd, R8gd e R6id basate su Graviton e R6id basate su Intel con storage express () in memoria non volatile. NVMe È disponibile a partire dalle versioni di PostgreSQL seguenti: + 14.12 e versioni successive, 15.7 e versioni successive, 16.3 e versioni successive, 17.4 e versioni successive per istanze R8gd + 14.9 e versioni successive, 15.4 e versioni successive, 16.1 e tutte le versioni successive per istanze R6gd e R6id Letture ottimizzate per Aurora supporta due funzionalità: cache a più livelli e oggetti temporanei. **Cache a più livelli abilitata per Letture ottimizzate**: con una cache a più livelli puoi ottenere una capacità di caching dell'istanza database fino a 5 volte superiore la memoria dell'istanza. Ciò assicura che la cache contenga automaticamente i dati più recenti e coerenti dal punto di vista transazionale, sollevando le applicazioni dall'onere di gestire la valuta dei dati delle soluzioni di caching basate su set di risultati esterni. Offre una latenza fino a 8 volte migliore per le query che in precedenza recuperavano dati dall'archiviazione di Aurora. In Aurora, il valore per `shared_buffers` nel gruppo di parametri predefinito è in genere impostato su circa il 75% della memoria disponibile. Tuttavia, per i tipi di istanza r8gd, r6gd e r6id, Aurora riduce `shared_buffers` lo spazio del 4,5% per ospitare i metadati per la cache delle letture ottimizzate. **Oggetti temporanei ottimizzati per la lettura**: utilizzando oggetti temporanei, è possibile ottenere un'elaborazione delle query più rapida posizionando i file temporanei generati da PostgreSQL nella memoria locale. NVMe Ciò riduce il traffico verso Elastic Block Storage (EBS) attraverso la rete. Offre una latenza e un throughput fino a 2 volte migliori per le query avanzate che ordinano, uniscono o integrano grandi volumi di dati che non rientrano nella capacità di memoria disponibile su un’istanza database. In un cluster Aurora I/O ottimizzato, Optimized Reads utilizza sia la cache a più livelli che gli oggetti temporanei sullo storage. NVMe Con la funzionalità di cache a più livelli abilitata per Letture ottimizzate, Aurora alloca il doppio della memoria dell'istanza per gli oggetti temporanei, circa il 10% dell'archiviazione per le operazioni interne e l'archiviazione rimanente come cache a più livelli. In un cluster Aurora standard, Letture ottimizzate utilizza solo oggetti temporanei. I cluster ottimizzati per l’I/O Aurora consentono di ridimensionare lo spazio allocato per gli oggetti temporanei abilitati a Letture ottimizzate utilizzando il parametro dinamico `aurora_temp_space_size` a livello di istanza. La funzionalità di ridimensionamento è disponibile a partire dalle versioni di PostgreSQL seguenti: + 16.8 e tutte le versioni successive + 15.12 e versioni successive alla 15 + 14.17 e versioni successive alla 14 Con questo parametro, è possibile ridimensionare la capacità da 2 a 6 volte la memoria dell’istanza senza richiedere il riavvio del motore di database. Quando si espande lo spazio per gli oggetti temporanei, la modifica diventa immediatamente effettiva, indipendentemente dai carichi di lavoro simultanei. Tuttavia, quando si riduce lo spazio, la modifica viene completata solo se lo spazio inutilizzato per gli oggetti temporanei è sufficiente per soddisfare la nuova richiesta di dimensione. Dopo aver ridimensionato gli oggetti temporanei abilitati a Letture ottimizzate, la cache a più livelli si regola automaticamente per utilizzare lo spazio disponibile. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.optimized.reads.html) **Nota** Il passaggio tra cluster ottimizzati per IO e standard su una classe di istanze DB basata comporta un NVMe riavvio immediato del motore di database. In Aurora PostgreSQL, utilizzare il parametro `temp_tablespaces` per configurare lo spazio della tabella in cui vengono archiviati gli oggetti temporanei. Per verificare se gli oggetti temporanei sono configurati, utilizzare il seguente comando: ``` postgres=> show temp_tablespaces; temp_tablespaces --------------------- aurora_temp_tablespace (1 row) ``` `aurora_temp_tablespace`È un tablespace configurato da Aurora che punta all'archiviazione locale. NVMe Non puoi modificare questo parametro o tornare all'archiviazione Amazon EBS. Per verificare se la cache per le letture ottimizzate è attiva, usa il seguente comando: ``` postgres=> show shared_preload_libraries; shared_preload_libraries -------------------------------------------------------- rdsutils,pg_stat_statements,aurora_optimized_reads_cache ``` ## Utilizzo di Letture ottimizzate per Aurora Quando si esegue il provisioning di un'istanza DB Aurora PostgreSQL con una delle istanze DB NVMe basate, l'istanza DB utilizza automaticamente Aurora Optimized Reads. Per attivare Letture ottimizzate per Aurora, procedi in uno dei seguenti modi: + Crea un cluster Aurora PostgreSQL DB utilizzando una delle classi di istanze DB basate. NVMe Per ulteriori informazioni, consulta [Creazione di un cluster database Amazon Aurora](Aurora.CreateInstance.md). + Modifica un cluster Aurora PostgreSQL DB esistente per utilizzare una delle classi di istanze DB basate. NVMe Per ulteriori informazioni, consulta [Modifica di un cluster database Amazon Aurora](Aurora.Modifying.md). Aurora Optimized Reads è disponibile Regioni AWS ovunque siano supportate una o più classi di istanze DB con archiviazione NVMe SSD locale. Per ulteriori informazioni, consulta [Classi di istanze DB Amazon Aurora](Concepts.DBInstanceClass.md). Per tornare a un'istanza Aurora di lettura non ottimizzata, modifica la classe di istanza DB dell'istanza Aurora con la classe di istanza simile senza spazio di archiviazione NVMe effimero per i carichi di lavoro del database. Ad esempio, se la classe di istanza database corrente è db.r6gd.4xlarge, scegli db.r6g.4xlarge per tornare indietro. Per ulteriori informazioni, consulta la pagina relativa alla [modifica di un'istanza database Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.DBInstance.Modifying.html). ## Casi d'uso per letture ottimizzate per Aurora **Cache a più livelli abilitata per Letture ottimizzate** Di seguito sono riportati alcuni casi d'uso in cui è possibile trarre vantaggio dalla funzionalità Letture ottimizzate con cache a più livelli: + Applicazioni su scala Internet come elaborazione dei pagamenti, fatturazione, e-commerce con prestazioni rigorose. SLAs + Dashboard di reporting in tempo reale che eseguono centinaia di interrogazioni mirate a fini di raccolta. metrics/data + Applicazioni di intelligenza artificiale generativa con l'estensione pgvector per la ricerca di vicini esatti o più prossimi tra milioni di incorporamenti vettoriali. **Oggetti temporanei abilitati per Letture ottimizzate** Di seguito sono riportati alcuni casi d'uso in cui è possibile trarre vantaggio dalla funzionalità Letture ottimizzate con oggetti temporanei: + Query analitiche che includono Common Table Expressions (CTEs), tabelle derivate e operazioni di raggruppamento. + Repliche di lettura che gestiscono le query non ottimizzate per un'applicazione. + Query di reporting on demand o dinamiche con operazioni complesse come GROUP BY e ORDER BY che non sempre possono utilizzare indici appropriati. + Operazioni `CREATE INDEX` o `REINDEX` per l’ordinamento. + Altri carichi di lavoro che utilizzano tabelle temporanee interne. ## Monitoraggio delle istanze database che utilizzano Letture ottimizzate per Aurora Per monitorare le query che utilizzano la cache a più livelli abilitata per Letture ottimizzate utilizza il comando EXPLAIN, come mostrato nell'esempio seguente: ``` 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 ``` **Nota** I campi `aurora_orcache_hit` e `aurora_storage_read` della sezione `Buffers` del piano di spiegazione vengono visualizzati solo quando la funzionalità Letture ottimizzate è attivata e i valori sono maggiori di zero. Il campo Lettura indica il totale dei campi `aurora_orcache_hit` e `aurora_storage_read`. È possibile monitorare le istanze DB che utilizzano Aurora Optimized Reads utilizzando le seguenti metriche: CloudWatch + `AuroraOptimizedReadsCacheHitRatio` + `FreeEphemeralStorage` + `ReadIOPSEphemeralStorage` + `ReadLatencyEphemeralStorage` + `ReadThroughputEphemeralStorage` + `WriteIOPSEphemeralStorage` + `WriteLatencyEphemeralStorage` + `WriteThroughputEphemeralStorage` Queste metriche forniscono dati sullo spazio di archiviazione dell'archivio dell'istanza, sulle operazioni IOPS e sulla velocità di trasmissione effettiva disponibili. Per ulteriori informazioni su questi parametri, consulta [Parametri a livello di istanza per Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances). È inoltre possibile utilizzare l'`pg_proctab`estensione per monitorare l'archiviazione. NVMe ``` 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) ``` ## Best practice per Letture ottimizzate per Aurora Usa le seguenti best practice per Letture ottimizzate per Aurora: + Monitora lo spazio di archiviazione disponibile sull'instance store con la CloudWatch metrica`FreeEphemeralStorage`. Se l’archivio dell’istanza sta raggiungendo il limite a causa del carico di lavoro sull’istanza database, ottimizzare la concorrenza e le query che utilizzano grandi volumi di oggetti temporanei oppure modificarlo affinché utilizzi una classe di istanza database più grande. + Monitora la CloudWatch metrica per la frequenza di accesso alla cache di Optimized Reads. Operazioni come VACUUM permettono di modificare blocchi numerosi molto rapidamente. Ciò può causare un calo temporaneo della percentuale di riscontri. Utilizza l'estensione `pg_prewarm` per caricare dati nella cache del buffer che consente ad Aurora di scrivere in modo proattivo alcuni di questi blocchi nella cache delle Letture ottimizzate. + È possibile abilitare Gestione della cache del cluster (CCM) per preparare la cache del buffer e la cache a più livelli su un lettore di livello 0, che verrà utilizzato come destinazione di failover. Quando la funzionalità CCM è abilitata, la cache del buffer viene scansionata periodicamente per scrivere pagine idonee all'espulsione nella cache a più livelli. Per ulteriori informazioni sulla CCM, consulta [Ripristino rapido dopo il failover con Cluster Cache Management per Aurora PostgreSQL](AuroraPostgreSQL.cluster-cache-mgmt.md). # Ottimizzazione delle sottoquery correlate in Aurora PostgreSQL Una sottoquery correlata fa riferimento alle colonne della tabella dalla query esterna. Viene valutata una volta per ogni riga restituita dalla query esterna. Nell’esempio seguente, la sottoquery fa riferimento a una colonna della tabella ot. Questa tabella non è inclusa nella clausola FROM della sottoquery, ma vi viene fatto riferimento nella clausola FROM della query esterna. Se la tabella ot ha 1 milione di righe, la sottoquery deve essere valutata 1 milione di volte. ``` SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); ``` **Nota** La trasformazione delle sottoquery e la cache delle sottoquery sono disponibili in Aurora PostgreSQL a partire dalla versione 16.8, mentre Babelfish per Aurora PostgreSQL supporta queste funzionalità dalla versione 4.2.0. A partire dalle versioni 4.6.0 e 5.2.0 di Babelfish per Aurora PostgreSQL, queste funzionalità sono controllate dai seguenti parametri: babelfishpg\$1tsql.apg\$1enable\$1correlated\$1scalar\$1transform babelfishpg\$1tsql.apg\$1enable\$1subquery\$1cache Per impostazione predefinita, entrambi i parametri sono attivati. ## Miglioramento delle prestazioni delle query di Aurora PostgreSQL con la trasformazione delle sottoquery Aurora PostgreSQL può accelerare le sottoquery correlate trasformandole in outer join equivalenti. Questa ottimizzazione si applica ai seguenti due tipi di sottoquery correlate: + Sottoquery che restituiscono un singolo valore aggregato e vengono visualizzate nell’elenco SELECT. ``` SELECT ot.a, ot.b, (SELECT AVG(it.b) FROM it WHERE it.a = ot.a) FROM ot; ``` + Sottoquery che restituiscono un singolo valore aggregato e vengono visualizzate in una clausola WHERE. ``` SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); ``` ### Abilitazione della trasformazione nella sottoquery Per abilitare la trasformazione di sottoquery correlate in outer join equivalenti, impostare il parametro `apg_enable_correlated_scalar_transform` su `ON`. Il valore predefinito di questo parametro è `OFF`. È possibile modificare il gruppo di parametri del cluster o dell’istanza per impostare i parametri. Per ulteriori informazioni, consulta [Gruppi di parametri per Amazon Aurora](USER_WorkingWithParamGroups.md). In alternativa, è possibile configurare l’impostazione solo per la sessione corrente con il seguente comando: ``` SET apg_enable_correlated_scalar_transform TO ON; ``` ### Verifica della trasformazione Utilizzare il comando EXPLAIN per verificare se la sottoquery correlata è stata trasformata in un outer join nel piano di query. Quando la trasformazione è abilitata, la parte di sottoquery correlata applicabile verrà trasformata in outer join. Esempio: ``` 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 stessa query non viene trasformata quando il parametro GUC viene impostato su `OFF`. Il piano non avrà un outer join ma un sottopiano. ``` 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) ``` ### Limitazioni + La sottoquery deve trovarsi nell’elenco SELECT o in una delle condizioni della clausola where. In caso contrario, non verrà trasformata. + La sottoquery deve restituire una funzione di aggregazione. Le funzioni di aggregazione definite dall’utente non sono supportate per la trasformazione. + Una sottoquery la cui espressione restituita non è una semplice funzione di aggregazione non verrà trasformata. + La condizione correlata nelle clausole WHERE della sottoquery deve essere un semplice riferimento di colonna. In caso contrario, non verrà trasformata. + La condizione correlata nella sottoquery in cui le clausole devono essere un semplice predicato di uguaglianza. + La sottoquery non può contenere né una clausola HAVING né una clausola GROUP BY. + La clausola where nella sottoquery può contenere uno o più predicati combinati con AND. **Nota** L’impatto della trasformazione sulle prestazioni varia a seconda dello schema, dei dati e del carico di lavoro. L’esecuzione di sottoquery correlate con la trasformazione può migliorare significativamente le prestazioni all’aumentare del numero di righe prodotte dalla query esterna. Si consiglia vivamente di testare questa funzionalità in un ambiente non di produzione con lo schema, i dati e il carico di lavoro effettivi prima di abilitarla in un ambiente di produzione. ## Utilizzo della cache delle sottoquery per migliorare le prestazioni delle query di Aurora PostgreSQL Aurora PostgreSQL supporta la cache delle sottoquery per archiviare i risultati delle sottoquery correlate. Questa funzionalità salta le esecuzioni ripetute delle sottoquery correlate quando i risultati delle sottoquery sono già presenti nella cache. ### Informazioni sulla cache delle sottoquery Il nodo Memoize di PostgreSQL è la parte fondamentale della cache delle sottoquery. Il nodo Memoize mantiene una tabella hash nella cache locale per mappare i valori dei parametri di input alle righe dei risultati delle query. Il limite di memoria per la tabella hash è il prodotto di work\$1mem e hash\$1mem\$1multiplier. Per ulteriori informazioni, consulta [Consumo di risorse](https://www.postgresql.org/docs/16/runtime-config-resource.html). Durante l’esecuzione delle query, la cache delle sottoquery utilizza la percentuale di riscontri nella cache (CHR, Cache Hit Rate) per stimare se la cache sta migliorando le prestazioni delle query e per decidere in fase di esecuzione della query se continuare a utilizzare la cache. CHR è il rapporto tra il numero di riscontri nella cache e il numero totale di richieste. Ad esempio, se una sottoquery correlata deve essere eseguita 100 volte e 70 di tali risultati di esecuzione possono essere recuperati dalla cache, il CHR è 0,7. Per ogni numero di mancati riscontri nella cache di apg\$1subquery\$1cache\$1check\$1interval, il vantaggio della cache delle sottoquery viene valutato controllando se il CHR è maggiore di apg\$1subquery\$1cache\$1hit\$1rate\$1threshold. In caso contrario, la cache verrà eliminata dalla memoria e l’esecuzione della query tornerà alla riesecuzione della sottoquery originale, non memorizzata nella cache. ### Parametri che controllano il comportamento della cache delle sottoquery La tabella seguente elenca i parametri che controllano il comportamento della cache delle sottoquery. | Parametro | Description | Predefinita | Consentito | | --- | --- | --- | --- | | apg\$1enable\$1subquery\$1cache | Abilita l’uso della cache per sottoquery scalari correlate. | OFF | ON, OFF | | apg\$1subquery\$1cache\$1check\$1interval | Imposta la frequenza, in numero di mancati riscontri nella cache, per valutare la percentuale di riscontri nella cache delle sottoquery. | 500 | 0–2147483647 | | apg\$1subquery\$1cache\$1hit\$1rate\$1threshold | Imposta la soglia per la percentuale di riscontri nella cache delle sottoquery. | 0.3 | 0.0 - 1.0 | **Nota** Valori maggiori di `apg_subquery_cache_check_interval` possono migliorare l’accuratezza della stima dei vantaggi della cache basata su CHR, ma aumenteranno il sovraccarico della cache, poiché CHR non verrà valutato finché la tabella della cache non avrà righe `apg_subquery_cache_check_interval`. Valori più elevati di `apg_subquery_cache_hit_rate_threshold` tendono ad abbandonare la cache delle sottoquery e a tornare alla riesecuzione originale delle sottoquery non memorizzate nella cache. È possibile modificare il gruppo di parametri del cluster o dell’istanza per impostare i parametri. Per ulteriori informazioni, consulta [Gruppi di parametri per Amazon Aurora](USER_WorkingWithParamGroups.md). In alternativa, è possibile configurare l’impostazione solo per la sessione corrente con il seguente comando: ``` SET apg_enable_subquery_cache TO ON; ``` ### Attivazione della cache delle sottoquery in Aurora PostgreSQL Quando la cache delle sottoquery è abilitata, Aurora PostgreSQL applica la cache per salvare i risultati delle sottoquery. Il piano di query avrà quindi un nodo Memoize sotto. SubPlan Ad esempio, la sequenza di comandi seguente mostra il piano di esecuzione delle query stimato di una sottoquery correlata semplice senza cache delle sottoquery. ``` 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) ``` Dopo l'attivazione`apg_enable_subquery_cache`, il piano di query conterrà un nodo Memoize sotto il SubPlan nodo, a indicare che la sottoquery prevede di utilizzare la 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) ``` Il piano di esecuzione delle query effettivo contiene maggiori dettagli sulla cache delle sottoquery, inclusi i riscontri nella cache e i mancati riscontri nella cache. L’output seguente mostra il piano di esecuzione delle query effettivo della query di esempio precedente dopo l’inserimento di alcuni valori nelle tabelle. ``` 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 ``` Il numero totale di riscontri nella cache è 4 e il numero totale di mancati riscontri nella cache è 6. Se il numero totale di riscontri e mancati riscontri è inferiore al numero di loop nel nodo Memoize, significa che la valutazione CHR non ha avuto esito positivo e che la cache è stata ripulita e abbandonata a un certo punto. L’esecuzione della sottoquery è quindi tornata alla riesecuzione originale non memorizzata nella cache. ### Limitazioni La cache delle sottoquery non supporta determinati modelli di sottoquery correlate. Questi tipi di query verranno eseguiti senza cache, anche se la cache delle sottoquery è attivata: + IN/EXISTS/ANY/ALLsottoquery correlate + Sottoquery correlate contenenti funzioni non deterministiche. + Sottoquery correlate che fanno riferimento a colonne esterne della tabella con tipi di dati che non supportano operazioni di hashing o uguaglianza. # Miglioramento delle prestazioni delle query utilizzando join adattivo ## Panoramica di Join adattivo è una funzionalità di anteprima di Aurora PostgreSQL 17.4 che aiuta a migliorare le prestazioni delle query. Questa funzionalità è disabilitata per impostazione predefinita, ma è possibile abilitarla utilizzando i parametri GUC (Global User Configuration). Poiché si tratta di una funzionalità di anteprima, i valori dei parametri predefiniti potrebbero cambiare. Se abilitato, il join adattivo aiuta a ottimizzare le prestazioni delle query passando dinamicamente da un loop join nidificato a un hash join in runtime. Questo passaggio si verifica quando lo strumento di ottimizzazione di PostgreSQL ha scelto erroneamente un loop join nidificato a causa di stime di cardinalità imprecise. ## Configurazione del join adattivo È possibile controllare il join adattivo utilizzando questi tre parametri GUC: **Parametri di configurazione di join adattivo** | Parametro GUC | Description | Opzioni predefinite e di configurazione | | --- | --- | --- | | apg\$1adaptive\$1join\$1crossover\$1multiplier | Questo moltiplicatore funziona con il punto di incrocio delle righe per determinare quando passare da un loop nidificato a un hash join. Il punto di incrocio delle righe è il punto in cui lo strumento di ottimizzazione SQL stima che le operazioni di loop nidificato e hash join abbiano lo stesso costo. Un valore di moltiplicatore più elevato riduce la probabilità che il join adattivo passi a un hash join. | Controlla se Join adattivo è abilitato [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) | | apg\$1adaptive\$1join\$1cost\$1threshold | Questo parametro imposta una soglia minima di costo delle query. Join adattivo si disattiva automaticamente per le query al di sotto di questa soglia. In questo modo si evita un sovraccarico prestazionale nelle query semplici in cui il costo della pianificazione di un join adattivo potrebbe superare i vantaggi del passaggio dal nested loop all’hash join. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) | | apg\$1enable\$1parameterized\$1adaptive\$1join | Se abilitato, questo parametro estende la funzionalità join adattivo ai loop join nidificati parametrizzati. Per impostazione predefinita, il join adattivo funziona solo con loop join nidificati non parametrizzati, poiché è più probabile che questi traggano vantaggio dal passaggio all’hash join. I loop join nidificati parametrizzati in genere offrono prestazioni migliori, rendendo meno importante il passaggio all’hash join. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) Deve essere prima abilitato `apg_adaptive_join_crossover_multiplier` | # Utilizzo della cache del piano condiviso ## Panoramica di Aurora PostgreSQL utilizza un process-per-user modello in cui ogni connessione client crea un processo di backend dedicato. Ogni processo di backend mantiene la propria cache del piano locale per le istruzioni preparate. Poiché queste cache non possono essere condivise tra i processi, le applicazioni che utilizzano molte istruzioni preparate potrebbero creare cache duplicate tra diversi processi di backend, con conseguente aumento dell'utilizzo della memoria. Aurora PostgreSQL versioni 17.6 e successive e 16.10 e successive introducono la funzionalità di cache del piano condiviso. Quando si abilita questa funzionalità, i processi di backend possono condividere piani generici, il che riduce l'utilizzo della memoria e migliora le prestazioni eliminando la generazione di piani duplicati. La cache del piano condiviso utilizza i seguenti componenti come chiave di cache: + Stringa di query (inclusi i commenti) + Parametri GUC relativi a Planner (inclusi) `search_path` + ID utente + ID del database I riavvii dell'istanza reimpostano la cache condivisa. ## Parameters La tabella seguente descrive i parametri che controllano la funzionalità di cache del piano condiviso: | Parametro | Description | Predefinita | Consentito | | --- | --- | --- | --- | | apg\$1shared\$1plan\$1cache.enable | Attiva o disattiva la cache del piano condiviso | 0 (DISATTIVATO) | 0, 1 | | apg\$1shared\$1plan\$1cache.max | Il numero massimo di voci della cache | 200-1000 (dipende dalla dimensione dell'istanza) | 100-50000 | | apg\$1shared\$1plan\$1cache.min\$1size\$1per\$1entry | La dimensione minima del piano da archiviare nella cache condivisa. I piani più piccoli utilizzano la cache locale per ottimizzare le prestazioni OLTP. | 16 KB | 0—32768 (KB) | | apg\$1shared\$1plan\$1cache.max\$1size\$1per\$1entry | La dimensione massima del piano per la cache condivisa. I piani più grandi memorizzano solo le informazioni sui costi. | 256 KB — 4 MB (dipende dalla dimensione dell'istanza) | 0-32768 (KB) | | apg\$1shared\$1plan\$1cache.idle\$1generic\$1plan\$1release\$1timeout | Il periodo dopo il quale le sessioni inattive rilasciano i piani generici locali. Valori più bassi consentono di risparmiare memoria; valori più alti potrebbero migliorare le prestazioni. | 10 secondi | 0—2147483647 (ms) | **Nota** È possibile modificare tutti i parametri senza riavviare. ## Visualizzazioni e funzioni di monitoraggio + `apg_shared_plan_cache()`— Mostra informazioni dettagliate sull'immissione nella cache (accessi, validità, timestamp) + `apg_shared_plan_cache_stat()`— Visualizza le statistiche a livello di istanza (sfratti, annullamenti) + `apg_shared_plan_cache_reset()`— `apg_shared_plan_cache()` Rimuove tutte le voci in e `apg_shared_plan_cache_stat()` + `apg_shared_plan_cache_remove(cache_key)`— Rimuove una voce dal `apg_shared_plan_cache()` punto in cui la voce corrisponde a `cache_key` ## Limitazioni + Funziona solo con istruzioni preparate e non memorizza nella cache PL/pgSQL le istruzioni + Non memorizza nella cache una query che contiene tabelle temporanee o tabelle di catalogo + Non memorizza nella cache una query che dipende da RLS (Row-Level Security) + Ogni replica mantiene la propria cache (nessuna condivisione tra repliche)