Obtener y analizar 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 obteniendo y analizando planes de ejecución. Un plan de ejecución o EXPLAIN es una representación detallada de cómo tiene previsto ejecutar el motor de tu base de datos de AlloyDB una consulta SQL. El plan de ejecución consta de un árbol de nodos que describe la secuencia de operaciones, como análisis de tablas, combinaciones, ordenaciones y agregaciones, que la base de datos de AlloyDB realiza para obtener los datos solicitados. Cada paso de este plan se denomina nodo.

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

Los planes de ejecución incluyen los siguientes componentes:

  • Nodos del plan: representan los diferentes pasos de la ejecución de la consulta, como una operación de análisis, combinación u ordenación.
  • 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 cuántos datos se leen del disco en comparación con la caché, lo que ayuda a identificar problemas de lectura de disco.
  • Configuración de los parámetros: el plan muestra la configuración de los parámetros que se aplica 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 empezar

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

Generar 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, siga estos pasos:

  1. Conecta un cliente psql a una instancia.

  2. 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)

  3. 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 de 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.

Ver y analizar datos del plan EXPLAIN

Una vez que obtengas un plan de ejecución, podrás ver y analizar los resultados.

De forma predeterminada, la salida de EXPLAIN muestra la actividad de las consultas del lado 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 has generado, usa el comando EXPLAIN antes de la consulta 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 costes de consulta estimados 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)

La salida del plan incluye los siguientes datos:

  • coste = 0,00..1735481,00: el primer número indica el coste de recuperar la primera fila. El segundo número indica el coste de recuperar la última fila.
  • rows = 100000000: es el número estimado de filas que devuelve la consulta.
  • width = 27: es la anchura estimada de la fila devuelta, lo que te ayuda a entender los bloques a los que se ha accedido.

Opción ANALYZE

Para mostrar las estadísticas de ejecución reales, así como las estimaciones de ejecución, añade 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)

La salida del plan incluye los siguientes datos:

  • Tiempo real (en ms) = 0,165..9342,424: muestra el tiempo real que se tarda en devolver la primera fila y el tiempo total que se tarda en devolver todas las filas.
  • rows = 100000001: es el número real de filas devueltas.
  • loops = 1: este valor es importante para los nodos de bucle anidado. Muestra el tiempo medio por bucle si loops es superior a 1.
  • Tiempo de planificación: 0,025 ms: indica el tiempo que ha tardado el planificador en determinar la ruta de ejecución.
  • Tiempo de ejecución: 13674,794 ms: indica el tiempo que ha tardado en ejecutarse después de que el planificador haya determinado la ruta.
  • Tiempo total de ejecución: suma de Planning Time y Execution Time. (0,025 + 13674,794 = 13674,819)

Opción VERBOSE

Para añadir más información al plan de ejecución, usa la opción VERBOSE. En el siguiente ejemplo, al usar VERBOSE se añaden cualificaciones de esquema a los nombres de las tablas y se muestra un identificador de consulta interno que puede usar para correlacionar su consulta con otras herramientas de monitorizació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

La salida del plan incluye los siguientes datos:

  • Nodo de salida: muestra las columnas incluidas en la consulta. El planificador a veces incluye más columnas de las solicitadas si determina que dejar de incluirlas es más caro.
  • Identificador de consulta: identificador de PostgreSQL que se asigna a pg_stat_statements.
  • ID de consulta de AlloyDB: identificador de consulta de AlloyDB que se puede usar para correlacionar la información de las estadísticas de las consultas.

Opción COLUMNAR ENGINE

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

Si la tabla no está presente en el motor columnar, consulte la columna Comprobación columnar en el siguiente plan de explicación para ver 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 utiliza el formato de columnas, 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

La salida del plan incluye los siguientes datos:

  • Filtro de consulta (predicado): muestra el filtro aplicado, si usas alguno.
  • Filas retiradas por el filtro columnar: indica el número de filas que ha retirado el filtro columnar.
  • Bytes obtenidos de la caché de almacenamiento: muestra el número de bytes obtenidos de la caché de almacenamiento.
  • Tiempo de intercambio: es el tiempo necesario para intercambiar datos de la caché de desbordamiento columnar (SSD) si la relación no cabe en la memoria.

Opción CONFIGURACIÓN

Para mostrar cualquier ajuste de sesión, de base de datos o global que no sea el predeterminado y que use el planificador, añade 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 concreto 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:

  • Éxito compartido: el número de bloques de datos de 8 kB encontrados en la caché de búfer compartida principal de PostgreSQL.
  • Lectura compartida: número de bloques de datos de 8 kB leídos del sistema operativo. Esto suele indicar E/S de disco.
  • Modificado: número de bloques que no se habían modificado anteriormente y que la consulta ha cambiado (cambios en el mapa de visibilidad).
  • Escrito: número de bloques modificados anteriormente que este backend ha expulsado de la caché durante el procesamiento de la consulta, normalmente debido a cambios en el bit de sugerencia o en el mapa de visibilidad, y que se han escrito en el disco.
  • Aciertos de caché ultrarrápidos: número de bloques recuperados de la caché ultrarrápida.
  • Tiempos de E/S: duración de cualquier E/S de disco o SSD en milisegundos.
  • Planificación: actividad de almacenamiento en búfer durante la fase de planificación, como la lectura de metadatos o estadísticas de tablas de catálogo.
  • Tiempo dedicado a las operaciones de E/S durante la planificación: muestra el tiempo de E/S si se requiere leer metadatos del disco.

Opción WAL

Para obtener información sobre la actividad de registro anticipado de escritura (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 de WAL incluye lo siguiente:

  • Registros WAL: número de registros WAL leídos para mantener la coherencia.
  • Bytes de WAL: número de bytes leídos de WAL para mantener la coherencia.
  • Registros que se han modificado, pero aún no se han guardado: indica los registros que se han modificado, pero aún no se han guardado.

Siguientes pasos