Se usó la API de Cloud Translation para traducir esta página.

Cómo obtener y analizar los planes de explicación de AlloyDB

Puedes identificar los cuellos de botella del rendimiento y optimizar las operaciones de tu base de datos de AlloyDB para PostgreSQL si obtienes y analizas los planes de ejecución. Un plan de ejecución o plan de EXPLAIN es una representación detallada de cómo el motor de base de datos de AlloyDB pretende ejecutar una consulta en SQL. El plan de ejecución comprende un árbol de nodos que describe la secuencia de operaciones, como análisis de tablas, uniones, ordenamientos y agregaciones, que la base de datos de AlloyDB realiza para recuperar los datos solicitados. Cada paso de este plan se conoce como un nodo.

Para obtener un plan de ejecución, se usa el comando EXPLAIN, que devuelve el plan que el planificador de consultas de AlloyDB genera para una instrucción de SQL determinada. Un planificador de consultas, también conocido como optimizador, determina la forma más eficiente de ejecutar una consulta en SQL determinada.

Los planes de ejecución incluyen los siguientes componentes:

Nodos del plan: Representan los diferentes pasos en la ejecución de la consulta, como un análisis, una unión o una operación de ordenamiento.
Tiempo de ejecución: El plan EXPLAIN incluye el tiempo de ejecución estimado o real de cada paso, lo que te ayuda a identificar los cuellos de botella de la base de datos.
Uso del búfer: Muestra la cantidad de datos que se leen del disco en comparación con la caché, lo que ayuda a identificar problemas de lectura del disco.
Configuración de parámetros: El plan muestra la configuración de parámetros que es efectiva durante la ejecución de la consulta.

PostgreSQL y, por extensión, AlloyDB admiten planes de ejecución para las siguientes instrucciones:

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

Antes de comenzar

Debes tener un clúster y una instancia de AlloyDB. Para obtener más información, consulta Crea un clúster y su instancia principal.

Genera un plan de ejecución

Generas un plan de ejecución desde una aplicación cliente, como psql, pgAdmin o DBeaver. AlloyDB admite la generación de planes de ejecución en formato de texto o JSON.

Para generar un plan de ejecución, sigue estos pasos:

Conecta un cliente psql a una instancia.

Para generar un plan de ejecución en formato de texto, ejecuta el siguiente 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)

Para generar un plan de ejecución en formato JSON, ejecuta el siguiente 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 json)
```
El comando EXPLAIN incluye todas las opciones disponibles (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing y summary) para generar un plan de ejecución detallado para una consulta determinada en formato de texto o JSON. La opción analyze significa que la consulta se ejecuta para proporcionar estadísticas de tiempo de ejecución reales, así como las estimaciones del planificador de consultas.

Cómo ver y analizar los datos del plan EXPLAIN

Después de obtener un plan de ejecución, puedes ver y analizar los resultados.

De forma predeterminada, el resultado de EXPLAIN muestra la actividad de la consulta del servidor. Para medir el tiempo de ida y vuelta de extremo a extremo, usa la opción /timing en psql y vuelca los resultados en /dev/null.

Para ver el plan de ejecución que generaste, usa el comando EXPLAIN antes de tu consulta en SQL.

EXPLAIN SELECT...: Muestra el plan que elegiría el optimizador sin ejecutar la consulta.
EXPLAIN ANALYZE SELECT...: Ejecuta la consulta y muestra tanto el plan previsto como las estadísticas de ejecución reales, incluidos los tiempos de ejecución reales y los recuentos de filas.

EXPLAIN sin ANALYZE

Para mostrar los costos estimados de la consulta del optimizador de consultas, ejecuta una instrucción EXPLAIN sin la opción 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)

El resultado del plan incluye los siguientes datos:

cost = 0.00..1735481.00: El primer número indica el costo de recuperar la primera fila. El segundo número indica el costo de recuperar la última fila.
rows = 100000000: Es la cantidad estimada de filas que devuelve la consulta.
width = 27: Es el ancho estimado de la fila devuelta, lo que te ayuda a comprender los bloques a los que se accedió.

Opción ANALYZE

Para mostrar las estadísticas de ejecución reales y las estimaciones de ejecución, agrega la opción 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)

El resultado del plan incluye los siguientes datos:

actual time (in ms) = 0.165..9342.424: Muestra el tiempo real para devolver la primera fila y el tiempo total para devolver todas las filas.
rows = 100000001: Es la cantidad real de filas que se devolvieron.
loops = 1: Este valor es importante para los nodos de bucle anidado. Muestra el tiempo promedio por bucle si loops es mayor que 1.
Tiempo de planificación: 0.025 ms: Indica el tiempo que tardó el planificador en determinar la ruta de ejecución.
Tiempo de ejecución: 13674.794 ms: Indica el tiempo que tardó la ejecución después de que el planificador determinó la ruta.
Tiempo total de ejecución: Es la suma de Planning Time y Execution Time. (0.025 + 13674.794 = 13674.819)

Opción VERBOSE

Para agregar más información al plan de ejecución, usa la opción VERBOSE. En el siguiente ejemplo, el uso de VERBOSE agrega calificaciones de esquema a los nombres de las tablas y muestra un identificador de consulta interno que puedes usar para correlacionar tu consulta con otras herramientas de supervisión.

(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

El resultado del plan incluye los siguientes datos:

Nodo de salida: Enumera las columnas incluidas en la consulta. A veces, el planificador incluye más columnas de las solicitadas si determina que dejarlas atrás es más costoso.
Identificador de consulta: Es el identificador de PostgreSQL que se asigna a pg_stat_statements.
ID de la consulta de AlloyDB: Es el identificador de la consulta de AlloyDB que se puede usar para correlacionar la información de las estadísticas de consultas.

Opción COLUMNAR ENGINE

Para mostrar información sobre el motor de columnas de AlloyDB, agrega la opción COLUMNAR_ENGINE.

Si la tabla no está presente en el motor columnar, consulta la columna Columnar Check en el siguiente plan de explicación para conocer el estado.

(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

Si la tabla está presente en el motor de columnas y se usa el formato columnar, se indica un Análisis personalizado junto con estadísticas sobre cómo se usa el motor de columnas.

(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

El resultado del plan incluye los siguientes datos:

Filtro de consulta (predicado): Muestra el filtro aplicado, si usas uno.
Filas quitadas por el filtro columnar: Indica la cantidad de filas que quitó el filtro columnar.
Bytes recuperados de la caché de almacenamiento: Muestra la cantidad de bytes recuperados de la caché de almacenamiento.
Tiempo de intercambio: Es el tiempo necesario para intercambiar datos de la caché de derrame columnar (SSD) si la relación no cabe en la memoria.

Opción CONFIGURACIÓN

Para mostrar cualquier configuración global, de base de datos o de sesión que no sea la predeterminada y que use el planificador, agrega la opción 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

Opción BUFFERS

Para mostrar información sobre la fuente de datos, usa la palabra clave BUFFERS. El recuento de BUFFERS se acumula a partir de todos los pasos, no solo de un paso en particular del plan.

(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

La información del búfer incluye lo siguiente:

Acierto compartido: Es la cantidad de bloques de datos de 8 kB que se encuentran en la caché del búfer compartido principal de PostgreSQL.
shared read: Es la cantidad de bloques de datos de 8 kB leídos del sistema operativo. A menudo, esto indica E/S de disco.
dirtied: Es la cantidad de bloques sin modificar previamente que cambió la búsqueda (cambios en el mapa de visibilidad).
written: Es la cantidad de bloques modificados previamente que este backend desalojó de la caché durante el procesamiento de la consulta, generalmente debido a cambios en el bit de sugerencia o en el mapa de visibilidad, y que se vaciaron en el disco.
ultra fast cache hit: Es la cantidad de bloques recuperados de la caché ultrarrápida.
I/O Timings: Es la duración de cualquier E/S de disco o SSD en milisegundos.
Planificación: Actividad de búfer durante la fase de planificación, como la lectura de metadatos o estadísticas de las tablas del catálogo.
Tiempo dedicado a la E/S durante la planificación: Muestra el tiempo de E/S si se requiere leer metadatos del disco.

Opción de WAL

Para proporcionar información sobre la actividad del registro de escritura anticipada (WAL), usa la opción 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

La información del WAL incluye lo siguiente:

Registros de WAL: Es la cantidad de registros de WAL leídos para mantener la coherencia.
Bytes de WAL: Es la cantidad de bytes leídos del WAL para mantener la coherencia.
Registros que se modificaron, pero aún no se guardaron en un punto de control: Indica los registros que se modificaron, pero aún no se guardaron en un punto de control.

¿Qué sigue?

Obtén información sobre los tipos de nodos del plan de ejecución.