Questa pagina è stata tradotta dall'API Cloud Translation.

Recuperare e analizzare i piani di esecuzione di AlloyDB

Puoi identificare i colli di bottiglia delle prestazioni e ottimizzare le operazioni del database AlloyDB per PostgreSQL ottenendo e analizzando i piani di esecuzione. Un piano di esecuzione o un piano EXPLAIN è una rappresentazione dettagliata di come il motore del database AlloyDB intende eseguire una query SQL. Il piano di esecuzione comprende un albero di nodi che delinea la sequenza di operazioni, come scansioni di tabelle, join, ordinamento e aggregazioni, che il database AlloyDB esegue per recuperare i dati richiesti. Ogni passaggio di questo piano è chiamato nodo.

Un piano di esecuzione viene ottenuto utilizzando il comando EXPLAIN, che restituisce il piano generato dal planner di query AlloyDB per una determinata istruzione SQL. Un pianificatore di query, noto anche come ottimizzatore, determina il modo più efficiente per eseguire una determinata query SQL.

I piani di esecuzione includono i seguenti componenti:

Nodi del piano: rappresentano i diversi passaggi dell'esecuzione della query, ad esempio una scansione, un'unione o un'operazione di ordinamento.
Tempo di esecuzione: il piano EXPLAIN include il tempo di esecuzione stimato o effettivo per ogni passaggio, il che ti aiuta a identificare i colli di bottiglia del database.
Utilizzo del buffer: mostra la quantità di dati letti dal disco rispetto alla cache, il che aiuta a identificare i problemi di lettura del disco.
Impostazioni dei parametri: il piano mostra le impostazioni dei parametri effettive durante l'esecuzione della query.

PostgreSQL e, per estensione, AlloyDB, supportano i piani di esecuzione per le seguenti istruzioni:

SELECT
INSERT
UPDATE
DECLARE CURSOR
CREATE AS
CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
EXECUTE

Prima di iniziare

Devi disporre di un cluster e di un'istanza AlloyDB. Per saperne di più, consulta Creare un cluster e la relativa istanza principale.

Generare un piano di esecuzione

Generi un piano di esecuzione da un'applicazione client come psql, pgAdmin o DBeaver. AlloyDB supporta la generazione di piani di esecuzione in formato testo o JSON.

Per generare un piano di esecuzione:

Connetti un client psql a un'istanza.

Per generare un piano di esecuzione in formato di testo, esegui questo comando:

SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format text)

Per generare un piano di esecuzione in formato JSON, esegui il comando seguente:
```
SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format json)
```
Il comando EXPLAIN include tutte le opzioni disponibili: analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing e summary, per generare un piano di esecuzione dettagliato per una determinata query in formato di testo o JSON. L'opzione analyze indica che la query viene eseguita per fornire statistiche di runtime effettive nonché le stime del pianificatore di query.

Visualizzare e analizzare i dati del piano EXPLAIN

Dopo aver ottenuto un piano di esecuzione, visualizza e analizza i risultati.

Per impostazione predefinita, l'output di EXPLAIN mostra l'attività di query lato server. Per misurare il tempo di andata e ritorno end-to-end, utilizza l'opzione /timing in psql e scarica i risultati in /dev/null.

Per visualizzare il piano di esecuzione generato, utilizza il comando EXPLAIN prima della query SQL.

EXPLAIN SELECT...: mostra il piano che lo strumento di ottimizzazione sceglierebbe senza eseguire la query.
EXPLAIN ANALYZE SELECT...: esegue la query e mostra sia il piano previsto sia le statistiche di esecuzione effettive, inclusi i tempi di esecuzione effettivi e i conteggi delle righe.

EXPLAIN senza ANALYZE

Per visualizzare i costi stimati delle query di Query Planner, esegui un'istruzione EXPLAIN senza l'opzione ANALYZE .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27)
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(3 rows)

L'output del piano include i seguenti dati:

costo = 0,00..1735481,00: il primo numero indica il costo per recuperare la prima riga. Il secondo numero indica il costo per recuperare l'ultima riga.
rows = 100000000: questo è il numero stimato di righe restituite dalla query.
width = 27: questa è la larghezza stimata della riga restituita, che ti aiuta a comprendere i blocchi a cui è stato eseguito l'accesso.

Opzione ANALIZZA

Per visualizzare le statistiche di esecuzione effettive e le stime di esecuzione, aggiungi l'opzione ANALYZE

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.165..9342.424 rows=100000001 loops=1)
 Planning Time: 0.025 ms
 Execution Time: 13674.794 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(5 rows)

L'output del piano include i seguenti dati:

tempo effettivo (in ms) = 0,165..9342,424: mostra il tempo effettivo per restituire la prima riga e il tempo totale per restituire tutte le righe.
rows = 100000001: questo è il numero effettivo di righe restituite.
loops = 1: questo valore è importante per i nodi di ciclo nidificati. Mostra il tempo medio per ciclo se loops è maggiore di 1.
Tempo di pianificazione: 0,025 ms: indica il tempo impiegato dal planner per determinare il percorso di esecuzione.
Tempo di esecuzione: 13674,794 ms: indica il tempo di esecuzione dopo che il pianificatore ha determinato il percorso.
Tempo totale di esecuzione: la somma di Planning Time e Execution Time. (0,025 + 13674,794 = 13674,819)

Opzione VERBOSE

Per aggiungere ulteriori informazioni al piano di esecuzione, utilizza l'opzione VERBOSE. Nell'esempio seguente, l'utilizzo di VERBOSE aggiunge qualifiche dello schema ai nomi delle tabelle e mostra un identificatore di query interno che puoi utilizzare per correlare la query con altri strumenti di monitoraggio.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.164..6568.938 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10875.894 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

L'output del piano include i seguenti dati:

Nodo di output: elenca le colonne incluse nella query. A volte il pianificatore include più colonne di quelle richieste se determina che lasciarle indietro è più costoso.
Identificatore query: l'identificatore PostgreSQL che esegue il mapping a pg_stat_statements.
ID query AlloyDB: l'identificatore della query AlloyDB che può essere utilizzato per correlare le informazioni di Query Insights

Opzione MOTORE COLONNARE

Per visualizzare le informazioni per il motore colonnare AlloyDB, aggiungi l'opzione COLUMNAR_ENGINE.

Se la tabella non è presente nel motore colonnare, consulta la colonna Controllo colonnare nel seguente piano di esecuzione per lo stato.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.009..6328.154 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10673.310 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Se la tabella è presente nel motore colonnare e viene utilizzato il formato colonnare, viene indicata una scansione personalizzata insieme alle statistiche sull'utilizzo del motore colonnare.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test where product_id = 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Append  (cost=20.00..27438.78 rows=1166668 width=27) (actual time=0.066..377.029 rows=1000290 loops=1)
   ->  Custom Scan (columnar scan) on public.index_advisor_test  (cost=20.00..27437.66 rows=1166667 width=27) (actual time=0.065..296.904 rows=1000290 loops=1)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
         Rows Removed by Columnar Filter: 98999711
         Bytes fetched from storage cache: 774835915
         Columnar cache search mode: native
         Swap-in Time: 92.708 ms
   ->  Seq Scan on public.index_advisor_test  (cost=0.00..1.11 rows=1 width=27) (never executed)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
 Query Identifier: -4660018746142248761
 Planning Time: 0.217 ms
 Execution Time: 421.114 ms
 AlloyDB query id: 13855683355620344431
 AlloyDB plan id: 2126918133221480510

L'output del piano include i seguenti dati:

Filtro query (predicato): mostra il filtro applicato, se ne utilizzi uno.
Righe rimosse dal filtro colonnare: indica il numero di righe rimosse dal filtro colonnare.
Byte recuperati dalla cache di archiviazione: mostra il numero di byte recuperati dalla cache di archiviazione.
Tempo di swap-in: è il tempo necessario per scambiare i dati dalla cache di spill colonnare (SSD) se la relazione non rientra nella memoria.

Opzione IMPOSTAZIONI

Per mostrare le impostazioni non predefinite di sessione, database o globali utilizzate dal pianificatore, aggiungi l'opzione SETTINGS.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS) select * from public.index_advisor_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.007..6366.249 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10727.068 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Opzione BUFFERS

Per visualizzare informazioni sull'origine dati, utilizza la parola chiave BUFFERS. Il conteggio BUFFERS viene accumulato da tutti i passaggi, non solo da un passaggio specifico del piano.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.111..10007.193 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2588.597
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning:
   Buffers: shared hit=58 read=2, ultra fast cache hit=2
   I/O Timings: shared read=0.215
 Planning Time: 0.410 ms
 Execution Time: 14825.271 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

Le informazioni sul buffer includono quanto segue:

Hit condiviso: il numero di blocchi di dati da 8 kB trovati nella cache del buffer condiviso principale di PostgreSQL.
lettura condivisa: il numero di blocchi di dati da 8 kB letti dal sistema operativo. Spesso indica I/O disco.
dirtied: il numero di blocchi precedentemente non modificati che la query ha modificato (modifiche alla mappa di visibilità).
written: il numero di blocchi modificati in precedenza eliminati dalla cache da questo backend durante l'elaborazione delle query, in genere a causa di modifiche al bit di suggerimento o alla mappa di visibilità e scaricati sul disco.
ultra fast cache hit: il numero di blocchi recuperati dalla cache ultrarapida.
Tempi I/O: la durata di qualsiasi I/O del disco o dell'SSD in millisecondi.
Pianificazione: attività di buffer durante la fase di pianificazione, ad esempio lettura di metadati o statistiche dalle tabelle del catalogo.
Tempo dedicato all'I/O durante la pianificazione: mostra il tempo di I/O se è necessario leggere dal disco i metadati.

Opzione WAL

Per fornire informazioni sull'attività di Write-Ahead Logging (WAL), utilizza l'opzione WAL .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS, WAL) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.010..10147.314 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2590.410
   WAL: records=18 bytes=5178
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning Time: 0.030 ms
 Execution Time: 15033.004 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

Le informazioni WAL includono quanto segue:

Record WAL: il numero di record WAL letti per mantenere la coerenza.
Byte WAL: il numero di byte letti da WAL per mantenere la coerenza.
Record modificati ma non ancora sottoposti a checkpoint: indica i record modificati ma non ancora sottoposti a checkpoint.

Passaggi successivi

Scopri di più sui tipi di nodi del piano di esecuzione.

Recuperare e analizzare i piani di esecuzione di AlloyDB Mantieni tutto organizzato con le raccolte Salva e classifica i contenuti in base alle tue preferenze.

Prima di iniziare

Generare un piano di esecuzione

Visualizzare e analizzare i dati del piano EXPLAIN

EXPLAIN senza ANALYZE

Opzione ANALIZZA

Opzione VERBOSE

Opzione MOTORE COLONNARE

Opzione IMPOSTAZIONI

Opzione BUFFERS

Opzione WAL

Passaggi successivi

Recuperare e analizzare i piani di esecuzione di AlloyDB