# Trabajo con planes EXPLAIN de Aurora DSQL Aurora DSQL utiliza una estructura de plan EXPLAIN similar a la de PostgreSQL, pero con adiciones clave que reflejan su arquitectura distribuida y su modelo de ejecución. En esta documentación, proporcionaremos información general de los planes EXPLAIN de Aurora DSQL y destacaremos las similitudes y diferencias en comparación con PostgreSQL. Cubriremos los distintos tipos de operaciones de escaneo disponibles en Aurora DSQL y lo ayudaremos a comprender el costo de ejecutar las consultas. ## Planes EXPLAIN de PostgreSQL frente a Aurora DSQL Aurora SQL se basa en la base de datos de PostgreSQL y comparte la mayoría de las estructuras planificadas con PostgreSQL, pero presenta diferencias arquitectónicas clave que afectan a la ejecución y optimización de las consultas: | Característica | PostgreSQL | Aurora DSQL | | --- | --- | --- | | Almacenamiento de datos | Almacenamiento de montón | Sin montón, todas las filas están indexadas por un identificador único | | Clave principal | El índice de clave principal está separado de los datos de la tabla | El índice de clave principal es la tabla con todas las columnas adicionales como columnas INCLUDE | | Índices secundarios | Índices secundarios estándar | Funciona igual que PostgreSQL, con la posibilidad de incluir columnas no clave | | Capacidades de filtrado | Condición de índice, filtro de montón | Condición de índice, filtro de almacenamiento, filtro de procesador de consultas | | Tipos de exámenes | Escaneo secuencial, escaneo de índice, escaneo solo de índice | Escaneo completo, escaneo solo de índice, escaneo de índice | | Ejecución de consulta | Local en la base de datos | Distribuido (el procesamiento y el almacenamiento son independientes) | Aurora DSQL almacena los datos de la tabla directamente en orden de clave principal en lugar de en un montón independiente. Cada fila se identifica mediante una clave única, normalmente la clave principal, que permite a la base de datos optimizar las búsquedas de manera más eficiente. La diferencia arquitectónica explica por qué Aurora DSQL suele utilizar escaneos solo de índice en los casos en que PostgreSQL puede elegir un escaneo secuencial. Otra distinción clave es que Aurora DSQL separa la informática del almacenamiento, lo que permite aplicar filtros en una fase más temprana de la ruta de ejecución para reducir el movimiento de datos y mejorar el rendimiento. Para obtener más información sobre el uso de los planes EXPLAIN con PostgreSQL, consulte la [documentación EXPLAIN de PostgreSQL](https://www.postgresql.org/docs/current/using-explain.html). ## Elementos clave de los planes EXPLAIN de Aurora DSQL Los planes EXPLAIN de Aurora DSQL proporcionan información detallada sobre cómo se ejecutan las consultas, incluido dónde se produce el filtrado y qué columnas se recuperan del almacenamiento. La comprensión de este resultado ayuda a optimizar el rendimiento de las consultas. Condición de índice Condiciones utilizadas para navegar por el índice. El filtrado más eficiente que reduce los datos escaneados. En Aurora DSQL, las condiciones de índice se pueden aplicar en varios niveles del plan de ejecución. Proyecciones Columnas recuperadas del almacenamiento. Menos proyecciones significan un mejor rendimiento. Filtro de almacenamiento Condiciones aplicadas por almacenamiento. Más eficiente que los filtros del procesador de consultas. Filtro de procesador de consultas Condiciones aplicadas por procesador de consultas. Requiere transferir todos los datos antes de filtrarlos, lo que se traduce en una mayor sobrecarga de procesamiento y movimiento de datos. ## Filtros en Aurora DSQL Aurora DSQL separa la informática del almacenamiento, lo que significa que el punto en el que se aplican los filtros durante la ejecución de la consulta tiene un impacto significativo en el rendimiento. Los filtros que se aplican antes de transferir grandes volúmenes de datos reducen la latencia y mejoran la eficiencia. Cuanto antes se aplique un filtro, menos datos deberán procesarse, moverse y escanearse, lo que se traducirá en consultas más rápidas. Aurora DSQL puede aplicar filtros en varias etapas de la ruta de consulta. la comprensión de estas etapas es clave para interpretar los planes de consultas y optimizar el rendimiento. | Nivel | Tipo de filtro | Descripción | | --- | --- | --- | | 1 | Condición de índice | Se aplica al escanear el índice. Limita la cantidad de datos que se leen del almacenamiento y reduce los datos que se envían a la capa de procesamiento. | | 2 | Filtro de almacenamiento | Se aplica después de leer los datos del almacenamiento, pero antes de enviarlos a informática. Un ejemplo de esto es un filtro en una columna de inclusión de un índice. Reduce la transferencia de datos, pero no la cantidad leída. | | 3 | Filtro de procesador de consultas | Se aplica después de que los datos lleguen a la capa de informática. Todos los datos se deben transferir primero, lo que aumenta la latencia y el costo. Actualmente, Aurora DSQL no puede realizar todas las operaciones de filtrado y proyección en el almacenamiento, por lo que es posible que algunas consultas se vean obligadas a recurrir a este tipo de filtrado. | # Lectura de los planes EXPLAIN de Aurora DSQL Comprender cómo leer los planes EXPLAIN es clave para optimizar el rendimiento de las consultas. En esta sección, analizaremos ejemplos reales de planes de consultas de Aurora DSQL, mostraremos cómo se comportan los diferentes tipos de escaneo, explicaremos dónde se aplican los filtros y destacaremos las oportunidades de optimización. ## Tablas de muestra utilizadas en estos ejemplos Los ejemplos siguientes hacen referencia a dos tablas: `transaction` y `account`. La tabla `transaction` o dispone de clave principal, lo que provoca que Aurora DSQL escanee toda la tabla al consultarla. La tabla `account` tiene un índice en `customer_id`. Este índice incluye `balance` y `status` como columnas de cobertura, lo que permite responder a determinadas consultas directamente desde el índice sin necesidad de leer la tabla base. Sin embargo, el índice no incluye `created_at`, por lo que las consultas que hacen referencia a esta columna requieren un acceso adicional a la tabla. ``` CREATE TABLE transaction ( account_id uuid, transaction_date timestamp, description text ); CREATE TABLE account ( customer_id uuid, balance numeric, status varchar, created_at timestamp ); CREATE INDEX ASYNC idx1 ON account (customer_id) INCLUDE (balance, status); ``` ## Ejemplo de escaneo completo Aurora DSQL tiene escaneos secuenciales, que funcionan de manera idéntica a PostgreSQL, así como escaneos completos. La única diferencia entre estos dos es que los escaneos completos pueden utilizar filtros adicionales en el almacenamiento. Debido a esto, casi siempre se selecciona por encima de los escaneos secuenciales. Debido a la similitud, solo trataremos ejemplos de los escaneos completos más interesantes. Los escaneos completos se utilizarán principalmente en tablas sin clave principal. Como las claves principales de Aurora DSQL son índices de cobertura completa de forma predeterminada, lo más probable es que Aurora DSQL utilice escaneos solo indexados en la clave principal en muchas situaciones en las que PostgreSQL utilizaría un escaneo secuencial. Como ocurre con la mayoría de las demás bases de datos, una tabla sin índices se escalará mal. ``` EXPLAIN SELECT account_id FROM transaction WHERE transaction_date > '2025-01-01' AND description LIKE '%external%'; ``` ``` QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Full Scan (btree-table) on transaction (cost=125100.05..177933.38 rows=33333 width=16) Filter: (description ~~ '%external%'::text) -> Storage Scan on transaction (cost=12510.05..17793.38 rows=66666 width=16) Projections: account_id, description Filters: (transaction_date > '2025-01-01 00:00:00'::timestamp without time zone) -> B-Tree Scan on transaction (cost=12510.05..17793.38 rows=100000 width=30) ``` Este plan muestra dos filtros aplicados en diferentes etapas. La condición `transaction_date > '2025-01-01'` se aplica en la capa de almacenamiento, lo que reduce la cantidad de datos que se devuelven. La condición `description LIKE '%external%'` se aplica más adelante en el procesador de consultas, una vez transferidos los datos, lo que hace que sea menos eficiente. Insertar filtros más selectivos en las capas de almacenamiento o índice generalmente mejora el rendimiento. ## Ejemplo de escaneo solo de índices Los escaneos solo de índices son los tipos de escaneo más óptimos en Aurora DSQL, ya que producen el menor número de viajes de ida y vuelta a la capa de almacenamiento y son los que más filtran. Sin embargo, el hecho de que vea escaneo solo de índice no significa que tenga el mejor plan. Debido a los diferentes niveles de filtrado que se pueden producir, es esencial seguir prestando atención a los diferentes lugares en los que se puede producir el filtrado. ``` EXPLAIN SELECT balance FROM account WHERE customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb' AND balance > 100 AND status = 'pending'; ``` ``` QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using idx1 on account (cost=725.05..1025.08 rows=8 width=18) Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid) Filter: (balance > '100'::numeric) -> Storage Scan on idx1 (cost=12510.05..17793.38 rows=9 width=16) Projections: balance Filters: ((status)::text = 'pending'::text) -> B-Tree Scan on idx1 (cost=12510.05..17793.38 rows=10 width=30) Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid) ``` En este plan, la condición de indexación (`customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'`), se evalúa primero durante el escaneo del índice, que es la etapa más eficiente porque limita la cantidad de datos que se leen del almacenamiento. El filtro de almacenamiento, `status = 'pending'`, se aplica después de leer los datos, pero antes de enviarlos a la capa de procesamiento, lo que reduce la cantidad de datos transferidos. Por último, el filtro del procesador de consultas, `balance > 100`, se ejecuta en último lugar, después de mover los datos, por lo que es el menos eficiente. De estas, la condición de índice es la que ofrece el mejor rendimiento, ya que controla directamente la cantidad de datos que se escanean. ## Ejemplo de escaneo de índices Los escaneos de índices son similares a los escaneos solo de índices, excepto que tienen el paso adicional de tener que llamar a la tabla base. Como Aurora DSQL puede especificar filtros de almacenamiento, puede hacerlo tanto en la llamada de índice como en la llamada de búsqueda. Para que quede claro, Aurora DSQL presenta el plan como dos nodos. De esta forma, puede ver claramente en qué medida puede ayudar agregar una columna de inclusión en términos de filas devueltas desde el almacenamiento. ``` EXPLAIN SELECT balance FROM account WHERE customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb' AND balance > 100 AND status = 'pending' AND created_at > '2025-01-01'; ``` ``` QUERY PLAN ---------------------------------------------------------------------------------------------------------- Index Scan using idx1 on account (cost=728.18..1132.20 rows=3 width=18) Filter: (balance > '100'::numeric) Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid) -> Storage Scan on idx1 (cost=12510.05..17793.38 rows=8 width=16) Projections: balance Filters: ((status)::text = 'pending'::text) -> B-Tree Scan on account (cost=12510.05..17793.38 rows=10 width=30) Index Cond: (customer_id = '4b18a761-5870-4d7c-95ce-0a48eca3fceb'::uuid) -> Storage Lookup on account (cost=12510.05..17793.38 rows=4 width=16) Filters: (created_at > '2025-01-01 00:00:00'::timestamp without time zone) -> B-Tree Lookup on transaction (cost=12510.05..17793.38 rows=8 width=30) ``` Este plan muestra cómo se filtra en varias etapas: + La condición de indexación en `customer_id ` filtra los datos de forma temprana. + El filtro de almacenamiento en `status` reduce aún más los resultados antes de que se envíen a informática. + El filtro del procesador de consultas en `balance` se aplica más adelante, después de la transferencia. + El filtro de búsqueda en `created_at` se evalúa al buscar columnas adicionales de la tabla base. Agregar columnas de uso frecuente como campos de `INCLUDE` suele eliminar esta búsqueda y mejorar el rendimiento. ## Prácticas recomendadas + **Alinee los filtros con las columnas indexadas** para acelerar el filtrado. + **Utilice las columnas INCLUDE** para permitir escaneos solo de índices y evitar búsquedas. + **Valide las estimaciones de filas** al investigar los problemas de rendimiento. Aurora DSQL administra las estadísticas automáticamente ejecutando `ANALYZE` en segundo plano en función de las tasas de cambio de los datos. Si las estimaciones parecen inexactas, puede ejecutar `ANALYZE` manualmente para actualizar las estadísticas inmediatamente. + **Evite las consultas no indexadas** en tablas grandes para evitar costosos escaneos completos. # Descripción de las DPU en EXPLAIN ANALYZE Aurora DSQL proporciona información sobre la unidad de procesamiento distribuido (DPU) **por instrucción** en la producción del plan de `EXPLAIN ANALYZE VERBOSE`, lo que le brinda una mayor visibilidad del costo de las consultas durante el desarrollo. Esta sección explica qué son las DPU y cómo interpretarlas en el resultado de `EXPLAIN ANALYZE VERBOSE`. ## ¿Qué es una DPU? Una unidad de procesamiento distribuido (DPU) es la medida normalizada del trabajo realizado por Aurora DSQL. Está compuesta por: + **ComputeDPU**: tiempo dedicado a ejecutar consultas SQL + **ReadDPU**: recursos utilizados para leer datos del almacenamiento + **WriteDPU**: recursos que se utilizan para escribir datos en el almacenamiento + **MultiRegionWriteDPU**: recursos que se utilizan para replicar las escrituras en clústeres interconectados en configuraciones multirregionales. ## Uso de la DPU en EXPLAIN ANALYZE VERBOSE Aurora DSQL amplía `EXPLAIN ANALYZE VERBOSE` para incluir una estimación del uso de la DPU por instrucción al final del resultado. Esto proporciona una visibilidad inmediata del costo de las consultas, lo que le ayuda a identificar los factores de costo de la carga de trabajo, ajustar el rendimiento de las consultas y pronosticar mejor el uso de los recursos. Los siguientes ejemplos muestran cómo interpretar las estimaciones de la DPU por instrucción incluidas en el resultado de EXPLAIN ANALYZE VERBOSE. ### Ejemplo 1: consulta SELECT ``` EXPLAIN ANALYZE VERBOSE SELECT * FROM test_table; ``` ``` QUERY PLAN ---------------------------------------------------- Index Only Scan using test_table_pkey on public.test_table (cost=125100.05..171100.05 rows=1000000 width=36) (actual time=2.973..4.482 rows=120 loops=1) Output: id, context -> Storage Scan on test_table_pkey (cost=125100.05..171100.05 rows=1000000 width=36) (actual rows=120 loops=1) Projections: id, context -> B-Tree Scan on test_table_pkey (cost=125100.05..171100.05 rows=1000000 width=36) (actual rows=120 loops=1) Query Identifier: qymgw1m77maoe Planning Time: 11.415 ms Execution Time: 4.528 ms Statement DPU Estimate: Compute: 0.01607 DPU Read: 0.04312 DPU Write: 0.00000 DPU Total: 0.05919 DPU ``` En este ejemplo, la instrucción SELECT realiza un análisis solo de índices, por lo que la mayor parte del costo proviene de la DPU de lectura (0.04312), que representa los datos recuperados del almacenamiento y Compute DPU (0.01607), que refleja los recursos informáticos utilizados para procesar y devolver los resultados. No hay ninguna DPU de escritura, ya que la consulta no modifica los datos. La DPU total (0.05919) es la suma de Informática \$1 Lectura \$1 Escritura. ### Ejemplo 2: consulta INSERT ``` EXPLAIN ANALYZE VERBOSE INSERT INTO test_table VALUES (1, 'name1'), (2, 'name2'), (3, 'name3'); ``` ``` QUERY PLAN ---------------------------------------------------- Insert on public.test_table (cost=0.00..0.04 rows=0 width=0) (actual time=0.055..0.056 rows=0 loops=1) -> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=122) (actual time=0.003..0.008 rows=3 loops=1) Output: "*VALUES*".column1, "*VALUES*".column2 Query Identifier: jtkjkexhjotbo Planning Time: 0.068 ms Execution Time: 0.543 ms Statement DPU Estimate: Compute: 0.01550 DPU Read: 0.00307 DPU (Transaction minimum: 0.00375) Write: 0.01875 DPU (Transaction minimum: 0.05000) Total: 0.03732 DPU ``` Esta instrucción realiza principalmente escrituras, por lo que la mayor parte del costo está asociado a una DPU de escritura. La DPU de informática (0.01550) representa el trabajo realizado para procesar e insertar los valores. La DPU de lectura (0.00307) refleja las lecturas menores del sistema (para búsquedas en catálogos o comprobaciones de índices). Observe los mínimos de transacciones que se muestran junto a las DPU de lectura y escritura. Indican los costos básicos por transacción que se aplican *solo cuando la operación incluye lecturas o escrituras*. No significan que cada transacción incurra automáticamente en un cargo de DPU de lectura de 0.00375 o DPU de escritura de 0.05. En cambio, estos mínimos se aplican por transacción durante la agregación de costos y solo si se realizan lecturas o escrituras dentro de esa transacción. Debido a esta diferencia de alcance, es posible que las estimaciones por instrucción en `EXPLAIN ANALYZE VERBOSE` no coincidan exactamente con las métricas por transacción de las que se informa en CloudWatch o en los datos de facturación. ## Uso de la información de la DPU para la optimización Las estimaciones de la DPU por instrucción ofrecen una forma eficaz de optimizar las consultas más allá del tiempo de ejecución. Los casos de uso comunes incluyen: + **Conocimiento de los costos:** comprenda lo caro que es una consulta en relación con otras. + **Optimización del esquema:** compare el impacto de los índices o los cambios en el esquema tanto en el rendimiento como en la eficiencia de los recursos. + **Planificación presupuestaria:** calcule el costo de la carga de trabajo en función del uso observado de la DPU. + **Comparación de consultas:** evalúe los enfoques de consulta alternativos según su consumo relativo de DPU. ## Interpretación de la información de la DPU Tenga en cuenta las siguientes prácticas recomendadas al utilizar datos de DPU de `EXPLAIN ANALYZE VERBOSE`: + **Úselo de forma direccional:** trate la DPU de la que se ha informado como una forma de entender el costo *relativo* de una consulta, en lugar de una coincidencia exacta con las métricas o los datos de facturación de CloudWatch. Se esperan diferencias porque `EXPLAIN ANALYZE VERBOSE` informa del costo por instrucción, mientras que CloudWatch agrega la actividad por transacción. CloudWatch también incluye operaciones en segundo plano (como ANALYZE o compactaciones) y gastos de transacción (`BEGIN`/`COMMIT`) que `EXPLAIN ANALYZE VERBOSE` excluye intencionadamente. + **La variabilidad de la DPU entre las ejecuciones es normal** en los sistemas distribuidos y no indica errores. Factores como el almacenamiento en caché, los cambios en el plan de ejecución, la simultaneidad o los cambios en la distribución de los datos pueden provocar que la misma consulta consuma recursos diferentes de una ejecución a la siguiente. + **Operaciones pequeñas por lotes:** si la carga de trabajo emite muchas instrucciones pequeñas, considere agruparlas en lotes en operaciones más grandes (que no superen los 10 MB). Esto reduce los gastos generales de redondeo y produce estimaciones de costos más significativas. + **Úselo para ajustar, no para facturar:** los datos de la DPU en `EXPLAIN ANALYZE VERBOSE` están diseñados para conocer los costos, ajustar las consultas y optimizar. No es una métrica apta para la facturación. Confíe siempre en las métricas de CloudWatch o en los informes de facturación mensuales para obtener datos fiables sobre costos y uso.