Optimización de subconsultas correlacionadas en Aurora PostgreSQL

Una subconsulta correlacionada hace referencia a las columnas de la tabla desde la consulta externa. Se evalúa una vez por cada fila devuelta por la consulta externa. En el siguiente ejemplo, la subconsulta hace referencia a una columna de la tabla ot. Esta tabla no está incluida en la cláusula FROM de la subconsulta, pero se hace referencia a ella en la cláusula FROM de la consulta externa. Si la tabla ot tiene 1 millón de filas, la subconsulta debe evaluarse 1 millón de veces.

SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);

Mejora del rendimiento de las consultas de Aurora PostgreSQL mediante la transformación de subconsultas

Habilitar la transformación en la subconsulta

Para habilitar la transformación de subconsultas correlacionadas en uniones externas equivalentes, defina el parámetro apg_enable_correlated_scalar_transform en ON. El valor predeterminado de este parámetro es OFF.

Puede modificar el grupo de parámetros de instancia o clúster para establecer los parámetros. Para obtener más información, consulte Grupos de parámetros para Amazon Aurora.

De forma alternativa, puede configurar los ajustes solo para la sesión actual mediante el siguiente comando:

SET apg_enable_correlated_scalar_transform TO ON;

Verificación de transformaciones

Utilice el comando EXPLAIN para comprobar si la subconsulta correlacionada se ha transformado en una combinación externa en el plan de consultas.

Cuando la transformación esté habilitada, la parte de la subconsulta correlacionada aplicable se transformará en una combinación externa. Por ejemplo:

postgres=> CREATE TABLE ot (a INT, b INT);
CREATE TABLE
postgres=> CREATE TABLE it (a INT, b INT);
CREATE TABLE

postgres=> SET apg_enable_correlated_scalar_transform TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);

                         QUERY PLAN
--------------------------------------------------------------
 Hash Join
   Hash Cond: (ot.a = apg_scalar_subquery.scalar_output)
   Join Filter: ((ot.b)::numeric < apg_scalar_subquery.avg)
   ->  Seq Scan on ot
   ->  Hash
         ->  Subquery Scan on apg_scalar_subquery
               ->  HashAggregate
                     Group Key: it.a
                     ->  Seq Scan on it                                        

La misma consulta no se transforma cuando el parámetro GUC pasa a OFF. El plan no tendrá una combinación externa, sino un subplan.

postgres=> SET apg_enable_correlated_scalar_transform TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
                QUERY PLAN
----------------------------------------
 Seq Scan on ot
   Filter: ((b)::numeric < (SubPlan 1))
   SubPlan 1
     ->  Aggregate
           ->  Seq Scan on it
                 Filter: (a = ot.a)  

Limitaciones

Uso de la caché de subconsultas para mejorar el rendimiento de las consultas de Aurora PostgreSQL

Aurora PostgreSQL admite la caché de subconsultas para almacenar los resultados de las subconsultas correlacionadas. Esta característica omite las ejecuciones repetidas de subconsultas correlacionadas cuando los resultados de las subconsultas ya están en la memoria caché.

La caché de subconsultas

El nodo Memoize de PostgreSQL es la parte clave de la caché de subconsultas. El nodo Memoize mantiene una tabla hash en la caché local para hacer asignaciones desde los valores de los parámetros de entrada a las filas de resultados de las consultas. El límite de memoria de la tabla hash es el producto de work_mem y hash_mem_multiplier. Para obtener más información, consulte Resource Consumption.

Durante la ejecución de la consulta, la caché de subconsultas utiliza la tasa de aciertos de caché (CHR) para estimar si la caché mejora el rendimiento de las consultas y para decidir, en el tiempo de ejecución de la consulta, si se debe seguir utilizando la caché. La CHR es la relación entre el número de visitas a caché y el número total de solicitudes. Por ejemplo, si una subconsulta correlacionada debe ejecutarse 100 veces y 70 de esos resultados de ejecución se pueden recuperar de la memoria caché, la CHR es 0,7.

Por cada número apg_subquery_cache_check_interval de errores de caché, se evalúan las ventajas de la caché de subconsultas comprobando si la CHR es mayor que apg_subquery_cache_hit_rate_threshold. De lo contrario, la caché se borrará de la memoria y la ejecución de la consulta volverá a la ejecución original (reejecución de subconsulta sin caché).

Parámetros que controlan el comportamiento de la caché de subconsultas

En la siguiente tabla se muestran los parámetros que controlan el comportamiento de la caché de subconsultas.

Puede modificar el grupo de parámetros de instancia o clúster para establecer los parámetros. Para obtener más información, consulte Grupos de parámetros para Amazon Aurora.

De forma alternativa, puede configurar los ajustes solo para la sesión actual mediante el siguiente comando:

SET apg_enable_subquery_cache TO ON;

Activación de la caché de subconsultas en Aurora PostgreSQL

Cuando la caché de subconsultas está habilitada, Aurora PostgreSQL aplica la caché para guardar los resultados de las subconsultas. El plan de consultas tendrá entonces un nodo Memoize en SubPlan.

Por ejemplo, la siguiente secuencia de comandos muestra el plan de ejecución de consultas estimado de una subconsulta correlacionada simple sin caché de subconsultas.

postgres=> SET apg_enable_subquery_cache TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);

             QUERY PLAN
------------------------------------
 Seq Scan on ot
   Filter: (b < (SubPlan 1))
   SubPlan 1
     ->  Seq Scan on it
           Filter: (a = ot.a)

Tras activar apg_enable_subquery_cache, el plan de consultas incluirá un nodo Memoize bajo el nodo SubPlan, lo que indica que la subconsulta planea usar la memoria caché.

postgres=> SET apg_enable_subquery_cache TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);

             QUERY PLAN
------------------------------------
 Seq Scan on ot
   Filter: (b < (SubPlan 1))
   SubPlan 1
     ->  Memoize
           Cache Key: ot.a
           Cache Mode: binary
           ->  Seq Scan on it
                 Filter: (a = ot.a)

El plan de ejecución de consultas actual contiene más detalles de la caché de subconsultas, incluidos los aciertos y los errores de caché. El siguiente resultado muestra el plan real de ejecución de la consulta del ejemplo anterior tras insertar algunos valores en las tablas.

postgres=> EXPLAIN (COSTS FALSE, TIMING FALSE, ANALYZE TRUE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);
            QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on ot (actual rows=2 loops=1)
   Filter: (b < (SubPlan 1))
   Rows Removed by Filter: 8
   SubPlan 1
     ->  Memoize (actual rows=0 loops=10)
           Cache Key: ot.a
           Cache Mode: binary
           Hits: 4  Misses: 6  Evictions: 0  Overflows: 0  Memory Usage: 1kB
           ->  Seq Scan on it (actual rows=0 loops=6)
                 Filter: (a = ot.a)
                 Rows Removed by Filter: 4   

El número total de aciertos de caché es 4 y el número total de errores de caché es 6. Si el número total de aciertos y errores es inferior al número de bucles del nodo Memoize, la evaluación de la CHR no se ha aprobado, y la caché se ha borrado y abandonado en algún momento. A continuación, la ejecución de la subconsulta ha vuelto a la reejecución original sin caché.

Limitaciones

La caché de subconsultas no admite ciertos patrones de subconsultas correlacionadas. Estos tipos de consultas se ejecutarán sin caché, aunque la caché de subconsultas esté activada:

Mejora del rendimiento de las consultas de Aurora PostgreSQL mediante la transformación de ventanas de subconsultas

Aurora PostgreSQL puede acelerar las subconsultas agregadas correlacionadas transformándolas en funciones de ventana equivalentes. Esta optimización se aplica a las subconsultas correlacionadas escalares de una cláusula WHERE que comparan una expresión externa con un agregado, como el siguiente patrón de consulta:

SELECT o.id, o.amount
FROM orders o, products p
WHERE p.id = o.product_id
  AND o.amount < (
    SELECT 0.2 * avg(o2.amount)
    FROM orders o2
    WHERE o2.product_id = p.id
  );

La transformación es una reescritura por plan que solo cambia el plan de ejecución, no los resultados de la consulta.

Cuándo utilizar la transformación de la ventana de subconsultas

Considere la posibilidad de habilitar esta transformación si tiene consultas que coincidan con el patrón de autocorrelación: una fila exterior comparada con un agregado calculado en la misma tabla con una condición de correlación.

Esta transformación es complementaria a la transformación de subconsultas correlacionadas existente, que reescribe las subconsultas correlacionadas como uniones externas. En su lugar, la transformación de la ventana de subconsultas las reescribe como funciones de ventana. Las dos transformaciones cubren patrones diferentes:

Puede habilitar ambas transformaciones al mismo tiempo. Cuando una consulta cumple los requisitos para ambas, se aplica la transformación de la ventana de subconsultas; cuando solo cumple con los requisitos para una, se aplica esa transformación.

Habilitación de la transformación de la ventana de subconsultas

Para habilitar la transformación de subconsultas correlacionadas en funciones de ventanas, defina el parámetro apg_enable_subquery_to_window_transform en ON. El valor predeterminado de este parámetro es OFF.

Puede modificar el grupo de parámetros de instancia o clúster para establecer los parámetros. Para obtener más información, consulte Grupos de parámetros para Amazon Aurora.

De forma alternativa, puede configurar los ajustes solo para la sesión actual mediante el siguiente comando:

SET apg_enable_subquery_to_window_transform TO ON;

Para desactivar la transformación, establezca el parámetro en OFF con los mismos métodos. Los cambios se aplican sin necesidad de reiniciar la instancia; SET por sesión es inmediato y los cambios en los grupos de parámetros se aplican a las conexiones nuevas.

Verificación de transformaciones

Utilice el comando EXPLAIN para comprobar si la subconsulta correlacionada se ha transformado en una función de ventana en el plan de consultas.

Cuando la transformación está habilitada, el plan de consulta contiene un nodo WindowAgg en lugar de un nodo SubPlan. Por ejemplo:

postgres=> SET apg_enable_subquery_to_window_transform TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE)
SELECT o.id, o.amount
FROM orders o, products p
WHERE p.id = o.product_id
  AND o.amount < (
    SELECT 0.2 * avg(o2.amount)
    FROM orders o2
    WHERE o2.product_id = p.id
  );

                      QUERY PLAN
------------------------------------------------------
 Subquery Scan on _sw
   Filter: (_sw.amount < (0.2 * _sw._w_agg_result))
   ->  WindowAgg
         Window: w1 AS (PARTITION BY o.product_id)
         ->  Sort
               Sort Key: o.product_id
               ->  Hash Join
                     Hash Cond: (o.product_id = p.id)
                     ->  Seq Scan on orders o
                     ->  Hash
                           ->  Seq Scan on products p

La misma consulta no se transforma cuando el parámetro GUC pasa a OFF. El plan no tendrá un nodo WindowAgg, sino un SubPlan en su lugar.

postgres=> SET apg_enable_subquery_to_window_transform TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE)
SELECT o.id, o.amount
FROM orders o, products p
WHERE p.id = o.product_id
  AND o.amount < (
    SELECT 0.2 * avg(o2.amount)
    FROM orders o2
    WHERE o2.product_id = p.id
  );

                 QUERY PLAN
---------------------------------------------
 Hash Join
   Hash Cond: (o.product_id = p.id)
   Join Filter: (o.amount < (SubPlan 1))
   ->  Seq Scan on orders o
   ->  Hash
         ->  Seq Scan on products p
   SubPlan 1
     ->  Aggregate
           ->  Seq Scan on orders o2
                 Filter: (product_id = p.id)

SELECT set_config('babelfishpg_tsql.apg_enable_subquery_to_window_transform', 'true', false);
SELECT set_config('babelfishpg_tsql.apg_enable_subquery_to_window_transform', 'false', false);

SET BABELFISH_SHOWPLAN_ALL ON
-- run your query
SET BABELFISH_SHOWPLAN_ALL OFF

Limitaciones

Si una consulta no cumple estas condiciones, se ejecuta con su plan original; no se produce ningún error.