Panoramica della gestione del piano di query per Aurora PostgreSQL - Amazon Aurora
Istruzioni SQL supportateLimitazioniTerminologiaVersioni disponibiliOttimizzazione della gestione del piano di queryUpgrade della gestione del piano di queryDisattivazione della gestione del piano di query per Aurora PostgreSQL

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à.

Panoramica della gestione del piano di query per Aurora PostgreSQL

La gestione del piano di query per Aurora PostgreSQL è progettata per garantire la stabilità del piano a prescindere dalle modifiche apportate al database che potrebbero causare la regressione del piano di query. La regressione del piano di query si verifica quando l'ottimizzatore sceglie un piano non ottimale per una determinata istruzione SQL dopo modifiche al sistema o al database. Le modifiche a statistiche, vincoli, impostazioni dell'ambiente, associazioni dei parametri di query e aggiornamenti al motore di database PostgreSQL possono tutte causare la regressione del piano.

La gestione del piano di query per Aurora PostgreSQLconsente di controllare come e quando cambiano i piani di esecuzione delle query. I vantaggi della gestione del piano di query per Aurora PostgreSQL includono i seguenti.

  • Migliorare la stabilità del piano forzando l'ottimizzatore a selezionare tra un numero ridotto di piani corretti.

  • Ottimizzare i piani centralmente e distribuire i migliori a livello globale.

  • Identificare gli indici non utilizzati e valutare l'impatto della creazione o della rimozione di un indice.

  • Rilevare automaticamente un nuovo piano a costo minimo individuato dall'ottimizzatore.

  • Provare nuove funzionalità dell'ottimizzatore con meno rischi, perché è possibile scegliere di approvare solo le modifiche dei piani che migliorano le performance.

Gli strumenti forniti dalla gestione del piano di query possono essere usati in modo proattivo per specificare il piano migliore per determinate query. In alternativa, è possibile utilizzare la gestione del piano di query per reagire a circostanze mutevoli ed evitare regressioni del piano. Per ulteriori informazioni, consulta Best practice per la gestione del piano di query Aurora PostgreSQL.

Istruzioni SQL supportate

La gestione del piano di query supporta i seguenti tipi di istruzioni SQL.

  • Qualsiasi istruzione SELECT, INSERT, UPDATE o DELETE, a prescindere dalla complessità.

  • Istruzioni preparate. Per ulteriori informazioni, consultare PREPARE nella documentazione di PostgreSQL.

  • Istruzioni dinamiche, comprese quelle eseguite in modalità immediata. Per ulteriori informazioni, consultare Dynamic SQL ed EXECUTE IMMEDIATE nella documentazione di PostgreSQL.

  • Comandi e istruzioni SQL incorporati. Per ulteriori informazioni, consultare Embedded SQL Commands nella documentazione di PostgreSQL.

  • Istruzioni all'interno di funzioni denominate. Per ulteriori informazioni, consultare CREATE FUNCTION nella documentazione di PostgreSQL.

  • Istruzioni contenenti tabelle temporanee.

  • Istruzioni all'interno di procedure e blocchi DO.

La gestione del piano di query può essere utilizzata con EXPLAIN in modalità manuale per acquisire un piano senza eseguirlo effettivamente. Per ulteriori informazioni, consulta Analisi del piano scelto dall'ottimizzatore. Per ulteriori informazioni sulle modalità di gestione del piano di query (manuale, automatica), consultare Acquisizione dei piani di esecuzione Aurora PostgreSQL.

La gestione del piano di query PostgreSQL di Aurora supporta tutte le funzionalità del linguaggio PostgreSQL, tra cui tabelle partizionate, ereditarietà, sicurezza a livello di riga ed espressioni di tabella comuni ricorsive (). CTEs Per ulteriori informazioni su queste caratteristiche del linguaggio PostgreSQL, consultare Table Partitioning, Row Security Policies e WITH Queries (Common Table Expressions) e altri argomenti nella documentazione di PostgreSQL.

Per informazioni sulle diverse versioni della funzionalità di gestione del piano di query di Aurora PostgreSQL, consulta Versioni dell'estensione Aurora PostgreSQL apg_plan_mgmt nelle Note di rilascio per Aurora PostgreSQL.

Limitazioni della gestione del piano di query

La versione corrente della gestione del piano di query di Aurora PostgreSQL include le seguenti limitazioni.

  • I piani non vengono acquisiti per le istruzioni che fanno riferimento alle relazioni di sistema: le istruzioni che fanno riferimento alle relazioni di sistema, ad esempio pg_class, non vengono acquisite. Si tratta di un'impostazione predefinita per evitare che venga acquisito un numero elevato di piani generati dal sistema utilizzati internamente. Questo vale anche per le tabelle di sistema all'interno delle viste.

  • Potrebbe essere necessaria una classe di istanza DB più grande per il cluster DB Aurora PostgreSQL: a seconda del carico di lavoro, la gestione del piano di query potrebbe richiedere una classe di istanza DB con più di 2 v. CPUs Il numero di max_worker_processes è limitato dalla dimensione della classe dell'istanza DB. Il numero di max_worker_processes forniti da una classe di istanza database con 2 vCPU (ad esempio db.t3.medium) può non essere sufficiente per un determinato carico di lavoro. Ti consigliamo di scegliere una classe di istanza DB con più di 2 v CPUs per il tuo cluster Aurora PostgreSQL DB se utilizzi la gestione del piano di query.

    Se la classe di istanza database non è in grado di supportare il carico di lavoro, la gestione del piano di query genera un messaggio di errore come quello riportato di seguito. 

    WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.

    In questo caso, è necessario aumentare il cluster database Aurora PostgreSQL fino a una dimensione della classe di istanza database con più memoria. Per ulteriori informazioni, consulta Motori DB supportati per classi di istanza database.

  • I piani già archiviati nelle sessioni non vengono modificati: la gestione dei piani di query fornisce un modo per influenzare i piani di query senza modificare il codice dell'applicazione. Tuttavia, quando un piano generico è già archiviato in una sessione esistente e se desideri modificarne il piano di query, devi prima impostare plan_cache_mode su force_custom_plan nel gruppo di parametri del cluster database.

  • queryid in apg_plan_mgmt.dba_plans e pg_stat_statements può essere diverso quando:

    • Gli oggetti vengono eliminati e ricreati dopo l'archiviazione in apg_plan_mgmt.dba_plans.

    • La tabella apg_plan_mgmt.plans viene importata da un altro cluster.

Per informazioni sulle diverse versioni della funzionalità di gestione del piano di query di Aurora PostgreSQL, consulta Versioni dell'estensione Aurora PostgreSQL apg_plan_mgmt nelle Note di rilascio per Aurora PostgreSQL.

Terminologia della gestione del piano di query

In questo argomento vengono utilizzati i seguenti termini.

istruzione gestita

Un'istruzione SQL acquisita dall'ottimizzatore durante la gestione del piano di query. Un'istruzione gestita ha uno o più piani di esecuzione della query archiviati nella vista apg_plan_mgmt.dba_plans.

baseline del piano

L'insieme di piani approvati per un'istruzione gestita specificata. Ovvero, tutti i piani per l'istruzione gestita che contengono "Approvato" per la relativa colonna status nella vista dba_plan.

cronologia del piano

L'insieme di tutti i piani acquisiti per un'istruzione gestita specificata. La cronologia del piano contiene tutti i piani acquisiti per l'istruzione, a prescindere dallo stato.

regressione del piano di query

Il caso in cui l'ottimizzatore sceglie un piano non ottimale rispetto a prima di una determinata modifica all'ambiente del database, ad esempio una nuova versione di PostgreSQL o modifiche alle statistiche.

Versioni di gestione del piano di query per Aurora PostgreSQL

La gestione del piano di query è supportata da tutte le versioni di Aurora PostgreSQL attualmente disponibili. Per ulteriori informazioni, consultare l'elenco di aggiornamenti di Amazon Aurora PostgreSQL nelle Note di rilascio di Aurora PostgreSQL.

La funzionalità di gestione del piano di query viene aggiunta al cluster database Aurora PostgreSQL quando si installa l'estensione apg_plan_mgmt. Versioni diverse di Aurora PostgreSQL supportano versioni differenti dell'estensione apg_plan_mgmt. Ti consigliamo di aggiornare l'estensione gestione del piano di query alla versione più recente per la versione di Aurora PostgreSQL.

Nota

Per le note di rilascio per ogni versione dell'estensione apg_plan_mgmt, consultare Aurora PostgreSQL apg_plan_mgmt extension versions nelle Note di rilascio per Aurora PostgreSQL.

È possibile identificare la versione in esecuzione sul cluster connettendosi a un'istanza mediante psql e utilizzando il metacomando \dx per elencare le estensioni come mostrato di seguito.

labdb=> \dx
                       List of installed extensions
     Name      | Version |    Schema     |                            Description
---------------+---------+---------------+-------------------------------------------------------------------
 apg_plan_mgmt | 1.0     | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 plpgsql       | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

L'output mostra che questo cluster utilizza la versione 1.0 dell'estensione. Solo alcune versioni di apg_plan_mgmt sono disponibili per una determinata versione di Aurora PostgreSQL. In alcuni casi, potrebbe essere necessario aggiornare il cluster database Aurora PostgreSQL a una nuova versione secondaria o applicare una patch in modo da poter eseguire l'aggiornamento alla versione più recente della gestione del piano di query. La apg_plan_mgmt versione 1.0 mostrata nell'output proviene da un cluster database Aurora PostgreSQL versione 10.17 che non dispone di una versione più recente di apg_plan_mgmt disponibile. In questo caso, il cluster database Aurora PostgreSQL deve essere aggiornato a una versione più recente di PostgreSQL.

Per ulteriori informazioni sull'aggiornamento di un cluster database Aurora PostgreSQL a una nuova versione di PostgreSQL, consultare Aggiornamenti del motore di database per Amazon Aurora Postgre SQL.

Per informazioni su come aggiornare l'estensione apg_plan_mgmt, consultare Aggiornamento della gestione del piano di query per Aurora PostgreSQL.

Attivazione della gestione del piano di query per Aurora PostgreSQL

La configurazione della gestione del piano di query per il cluster database Aurora PostgreSQL implica l'installazione di un'estensione e la modifica di diverse impostazioni dei parametri cluster database. Per installare l'estensione apg_plan_mgmt e attivare la funzionalità per il cluster database Aurora PostgreSQL sono necessarie autorizzazioni rds_superuser.

L'installazione dell'estensione crea un nuovo ruolo, apg_plan_mgmt, che consente agli utenti del database di visualizzare, gestire e mantenere i piani di query. In qualità di amministratore con privilegi rds_superuser, assicurati di concedere il ruolo apg_plan_mgmt agli utenti del database in base alle esigenze.

Solo gli utenti con il ruolo rds_superuser possono completare la procedura riportata di seguito. Il rds_superuser è necessario per creare l'estensione apg_plan_mgmt e il relativo ruolo apg_plan_mgmt. Il ruolo apg_plan_mgmt deve essere concesso agli utenti per gestire l'estensione apg_plan_mgmt.

Come attivare la gestione del piano di query per il cluster database Aurora PostgreSQL

I passaggi seguenti attivano la gestione del piano di query per tutte le istruzioni SQL che vengono inviate al cluster database Aurora PostgreSQL. Questa è nota come modalità automatica. Per ulteriori informazioni sulla differenza tra le modalità, consultare Acquisizione dei piani di esecuzione Aurora PostgreSQL.

  1. Aprire la console Amazon RDS all'indirizzo https://console.aws.amazon.com/rds/.

  2. Scegli un gruppo di parametri cluster di database personalizzato per il cluster database Aurora PostgreSQL. Per attivare la gestione del piano di query e impostare il suo comportamento, è necessario modificare determinati parametri. Per ulteriori informazioni, consulta Creazione di un gruppo di parametri DB in Amazon Aurora.

  3. Apri il gruppo di parametri cluster di database personalizzato e imposta il parametro rds.enable_plan_management su 1, come mostrato nell'immagine seguente.

    Immagine del gruppo di parametri del cluster DB.

    Per ulteriori informazioni, consulta Modifica dei parametri in un gruppo di parametri del cluster DB in Amazon Aurora.

  4. Crea un gruppo di parametri database personalizzato che puoi utilizzare per impostare i parametri del piano di query a livello di istanza. Per ulteriori informazioni, consulta Creazione di un gruppo di parametri del cluster DB in Amazon Aurora.

  5. Modifica l'istanza di scrittura del cluster database Aurora PostgreSQL per utilizzare il gruppo di parametri database personalizzato. Per ulteriori informazioni, consulta Modifica di un'istanza database in un cluster database.

  6. Modifica il cluster database Aurora PostgreSQL per utilizzare il gruppo di parametri database personalizzato. Per ulteriori informazioni, consulta Modifica del cluster di database tramite la console, la CLI e l'API.

  7. Riavvia l'istanza database per abilitare le impostazioni del gruppo di parametri personalizzati.

  8. Effettua la connessione all'endpoint dell'istanza database del cluster database Aurora PostgreSQL utilizzando psql o pgAdmin. L'esempio seguente utilizza l'account postgres predefinito per il ruolo rds_superuser.

    psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db

  9. Crea l'estensione apg_plan_mgmt per l'istanza database, come mostrato di seguito.

    labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
    Suggerimento

    Installa l'estensione apg_plan_mgmt nel database modello per l'applicazione. Il database modello predefinito è denominato template1. Per ulteriori informazioni, consultare Template Databases nella documentazione di PostgreSQL.

  10. Modifica il parametro apg_plan_mgmt.capture_plan_baselines in automatic. Questa impostazione consente all'ottimizzatore di generare i piani per ogni istruzione SQL pianificata o eseguita due o più volte.

    Nota

    La gestione del piano di Query dispone anche di una modalità manuale che è possibile utilizzare per istruzioni SQL specifiche. Per ulteriori informazioni, consulta Acquisizione dei piani di esecuzione Aurora PostgreSQL.

  11. Modifica il valore del parametro apg_plan_mgmt.use_plan_baselines in "on". Questo parametro consente all'ottimizzatore di scegliere un piano per l'istruzione dalla sua baseline del piano. Per ulteriori informazioni, consulta Utilizzo dei piani gestiti per Aurora PostgreSQL.

    Nota

    È possibile modificare il valore di uno di questi parametri dinamici per la sessione senza dover riavviare l'istanza.

Una volta completata la configurazione della gestione del piano di query, assicurati di concedere il ruolo apg_plan_mgmt a tutti gli utenti del database che devono visualizzare, gestire o mantenere piani di query.

Aggiornamento della gestione del piano di query per Aurora PostgreSQL

Ti consigliamo di aggiornare l'estensione gestione del piano di query alla versione più recente per la versione di Aurora PostgreSQL.

  1. Esegui la connessione all'istanza di scrittura del cluster database Aurora PostgreSQL come un utente che dispone di privilegi rds_superuser. Se hai mantenuto il nome predefinito durante la configurazione dell'istanza, esegui la connessione come postgres. In questo esempio viene illustrato come utilizzare psql, ma puoi anche utilizzare pgAdmin, se preferisci.

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

  2. Esegui la seguente query per aggiornare l'estensione.

    ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';

  3. Utilizza la funzione apg_plan_mgmt.validate_plans per aggiornare gli hash di tutti i piani. L'ottimizzatore convalida tutti i piani Approvato, Non approvato e Rifiutato per garantire che siano ancora piani validi per la nuova versione dell'estensione. 

    SELECT apg_plan_mgmt.validate_plans('update_plan_hash');

    Per ulteriori informazioni sull'utilizzo di questa funzione, consultare Convalida dei piani.

  4. Utilizza la funzione apg_plan_mgmt.reload per aggiornare eventuali piani nella memoria condivisa con i piani convalidati dalla vista dba_plans. 

    SELECT apg_plan_mgmt.reload();

Per ulteriori informazioni su tutte le funzioni disponibili per la gestione del piano di query, consultare Informazioni di riferimento sulle funzioni per la gestione del piano di query Aurora PostgreSQL.

Disattivazione della gestione del piano di query per Aurora PostgreSQL

Puoi disabilitare la gestione del piano di query in qualsiasi momento disattivando apg_plan_mgmt.use_plan_baselines e apg_plan_mgmt.capture_plan_baselines: 

labdb=> SET apg_plan_mgmt.use_plan_baselines = off;

labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;