Panoramica sulla gestione del piano di interrogazione di Aurora SQL Postgre - Amazon Aurora
SQLDichiarazioni supportateLimitazioniTerminologiaVersioni disponibiliOttimizzazione della gestione del piano di queryUpgrade della gestione del piano di queryDisattivazione della gestione del piano di interrogazione di Aurora SQL Postgre

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 sulla gestione del piano di interrogazione di Aurora SQL Postgre

La gestione del piano di SQL query di Aurora Postgre è progettata per garantire la stabilità del piano indipendentemente dalle modifiche 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 dopo modifiche al sistema o al database. SQL Le modifiche alle statistiche, ai vincoli, alle impostazioni dell'ambiente, alle associazioni dei parametri di query e gli aggiornamenti al motore di database Postgre possono tutti causare una regressione del piano. SQL

Con la gestione dei piani di SQL interrogazione di Aurora Postgre, puoi controllare come e quando cambiano i piani di esecuzione delle query. I vantaggi della gestione del piano di SQL query di Aurora Postgre includono quanto segue.

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

SQLDichiarazioni supportate

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

  • Qualsiasi DELETE dichiarazione SELECT INSERTUPDATE, indipendentemente dalla complessità.

  • Istruzioni preparate. Per ulteriori informazioni, consulta la PREPAREdocumentazione di Postgre. SQL

  • Istruzioni dinamiche, comprese quelle eseguite in modalità immediata. Per ulteriori informazioni, consulta Dynamic SQL e EXECUTEIMMEDIATEnella documentazione di SQL Postgre.

  • SQLComandi e istruzioni incorporati. Per ulteriori informazioni, consulta Embedded SQL Commands nella documentazione di Postgre. SQL

  • Istruzioni all'interno di funzioni denominate. Per ulteriori informazioni, consulta la documentazione CREATEFUNCTIONdi SQL Postgre.

  • 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 SQL query di Aurora Postgre supporta tutte le funzionalità del SQL linguaggio Postgre, tra cui tabelle partizionate, ereditarietà, sicurezza a livello di riga ed espressioni di tabella comuni ricorsive (). CTEs Per ulteriori informazioni su queste funzionalità del SQL linguaggio Postgre, consulta Table Partitioning, Row Security Policies and Queries (Common Table Expressions) e altri argomenti nella documentazione di Postgre. WITH SQL

Per informazioni sulle diverse versioni della funzionalità di gestione del piano di SQL query di Aurora Postgre, vedere le versioni dell'estensione SQLapg_plan_mgmt di Aurora Postgre nelle Note di rilascio per Aurora Postgre. SQL

Limitazioni della gestione del piano di query

L'attuale versione di Aurora Postgre SQL Query Plan Management presenta 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 Aurora Postgre SQL DB: a seconda del carico di lavoro, la gestione del piano di query potrebbe richiedere una classe di istanza DB con più di 2. vCPUs Il numero di max_worker_processes è limitato dalla dimensione della classe di istanza database. Il numero max_worker_processes fornito da una classe di istanza CPU DB 2-v (db.t3.medium, ad esempio) potrebbe non essere sufficiente per un determinato carico di lavoro. Ti consigliamo di scegliere una classe di istanza DB con più di 2 vCPUs per il tuo cluster Aurora Postgre SQL 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 scalare il cluster Aurora Postgre SQL DB fino a una classe di istanza DB 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 SQL query di Aurora Postgre, vedere le versioni dell'estensione SQLapg_plan_mgmt di Aurora Postgre nelle Note di rilascio per Aurora Postgre. SQL

Terminologia della gestione del piano di query

In questo argomento vengono utilizzati i seguenti termini.

istruzione gestita

Una dichiarazione acquisita dall'ottimizzatore nell'ambito della gestione del piano di query. SQL 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 meno ottimale rispetto a prima di una determinata modifica all'ambiente del database, come una nuova SQL versione di Postgre o modifiche alle statistiche.

Versioni di gestione del piano di interrogazione di Aurora Postgre SQL

La gestione dei piani di query è supportata da tutte le versioni di Aurora SQL Postgre attualmente disponibili. Per ulteriori informazioni, consulta l'elenco degli SQLaggiornamenti di Amazon Aurora Postgre nelle note di rilascio per Aurora Postgre. SQL

La funzionalità di gestione del piano di query viene aggiunta al cluster Aurora Postgre SQL DB quando si installa l'estensione. apg_plan_mgmt Diverse versioni di Aurora Postgre SQL supportano diverse versioni dell'estensione. apg_plan_mgmt Ti consigliamo di aggiornare l'estensione per la gestione del piano di query all'ultima versione per la tua versione di Aurora Postgre. SQL

Nota

Per le note di rilascio per ogni versione di apg_plan_mgmt estensione, vedere Aurora Postgre SQL apg_plan_mgmt extension versions nelle Note di rilascio per Aurora Postgre. SQL

È 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. Sono disponibili solo alcune apg_plan_mgmt versioni per una determinata versione di Aurora SQL Postgre. In alcuni casi, potrebbe essere necessario aggiornare il cluster Aurora Postgre SQL DB a una nuova release secondaria o applicare una patch in modo da poter eseguire l'aggiornamento alla versione più recente di gestione del piano di query. La apg_plan_mgmt versione 1.0 mostrata nell'output proviene da un cluster DB Aurora Postgre SQL versione 10.17, che non dispone di una versione più recente. apg_plan_mgmt In questo caso, il cluster Aurora Postgre SQL DB deve essere aggiornato a una versione più recente di Postgre. SQL

Per ulteriori informazioni sull'aggiornamento del cluster Aurora Postgre SQL DB a una nuova versione di Postgre, consulta. SQL Aggiornamenti di Amazon Aurora Postgre SQL

Per informazioni su come aggiornare l'estensione apg_plan_mgmt, consultare Aggiornamento della gestione del piano di interrogazione di Aurora Postgree SQL.

Attivazione della gestione del piano di interrogazione di Aurora SQL Postgree

La configurazione della gestione del piano di query per il cluster Aurora Postgre SQL DB comporta l'installazione di un'estensione e la modifica di diverse impostazioni dei parametri del cluster DB. Sono necessarie rds_superuser le autorizzazioni per installare l'apg_plan_mgmtestensione e attivare la funzionalità per il cluster Aurora SQL Postgre DB.

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.

Per attivare la gestione del piano di query per il cluster Aurora SQL Postgre DB

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

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

  2. Crea un gruppo di parametri del cluster DB personalizzato per il tuo cluster DB Aurora SQL Postgre. 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 writer del cluster Aurora Postgre SQL DB per utilizzare il gruppo di parametri DB personalizzato. Per ulteriori informazioni, consulta Modifica di un'istanza database in un cluster database.

  6. Modifica il cluster Aurora Postgre SQL DB per utilizzare il gruppo di parametri del cluster DB personalizzato. Per ulteriori informazioni, consulta Modifica del cluster DB utilizzando la console CLI e API.

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

  8. Connect all'endpoint dell'istanza DB del cluster Aurora Postgre SQL DB utilizzando o. psql 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 saperne di più, consulta Template Databases nella documentazione di Postgre. SQL

  10. Modifica il parametro apg_plan_mgmt.capture_plan_baselines in automatic. Questa impostazione fa sì che l'ottimizzatore generi piani per ogni SQL istruzione pianificata o eseguita due o più volte.

    Nota

    La gestione del piano di query dispone anche di una modalità manuale che è possibile utilizzare per SQL istruzioni 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 interrogazione di Aurora Postgree SQL

Ti consigliamo di aggiornare l'estensione per la gestione del piano di query all'ultima versione per la tua versione di Aurora Postgre. SQL

  1. Connect all'istanza writer del cluster Aurora Postgre SQL DB come utente con privilegi. rds_superuser Se hai mantenuto il nome predefinito quando configuri l'istanza, ti connetti come postgres Questo esempio mostra come usarepsql, ma puoi anche usarlo 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 Riferimento alla funzione per la gestione del piano di interrogazione di Aurora SQL Postgre.

Disattivazione della gestione del piano di interrogazione di Aurora SQL Postgre

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;