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: