Optimización de subconsultas correlacionadas en Babelfish
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 t1. 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 t1 tiene 1 millón de filas, la subconsulta debe evaluarse 1 millón de veces.
SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);
Mejora del rendimiento de las consultas de Babelfish mediante la transformación de subconsultas
Babelfish puede acelerar las subconsultas correlacionadas transformándolas en uniones externas equivalentes. Esta optimización se aplica a los dos tipos siguientes de subconsultas correlacionadas:
-
Subconsultas que devuelven un único valor agregado y aparecen en la lista SELECT. Para obtener información, consulte SELECT clause
en la documentación de Microsoft Transact-SQL. SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1; -
Subconsultas que devuelven un único valor agregado y aparecen en una cláusula WHERE.
SELECT * FROM outer_sb_t1 WHERE ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) > 1.0;
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. Esta característica solo está disponible en Babelfish 4.2.0 o en versiones posteriores. 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.
También puede configurar los ajustes solo para la sesión actual llamando a la función set_config. Por ejemplo, para activar la caché de subconsultas en Babelfish, ejecute el siguiente comando. Para obtener más información, consulte Configuration Settings Functions
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
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. Para obtener más información, consulte Uso del plan de EXPLAIN para mejorar el rendimiento de las consultas en Babelfish.
Cuando la transformación esté habilitada, la parte de la subconsulta correlacionada aplicable se transformará en una combinación externa. Por ejemplo:
1>select set_config('apg_enable_correlated_scalar_transform', 'true', false);2>GO1>set BABELFISH_STATISTICS PROFILE on2>GO1>select customer_name, ( select max(o.cost) from correlated_orders o2>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount3>from correlated_customers c order by customer_name ;4>GOQUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Text: select customer_name, ( select max(o.cost) from correlated_orders o where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10 ) AS max_order_amount from correlated_customers c order by customer_name Sort (cost=88.23..90.18 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Hash Left Join (cost=30.90..50.76 rows=780 width=40) Hash Cond: (c.customer_id = o.customer_id) -> Seq Scan on correlated_customers c (cost=0.00..17.80 rows=780 width=36) -> Hash (cost=28.73..28.73 rows=174 width=12) -> HashAggregate (cost=26.99..28.73 rows=174 width=12) Group Key: o.customer_id -> Seq Scan on correlated_orders o (cost=0.00..25.30 rows=338 width=12) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10)) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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.
1>select set_config('apg_enable_correlated_scalar_transform', 'false', false);2>GO1>select customer_name, ( select max(o.cost)2>from correlated_orders o3>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount4>from correlated_customers c order by customer_name ;5>GOQUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Sort (cost=21789.97..21791.92 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Seq Scan on correlated_customers c (cost=0.00..21752.50 rows=780 width=40) SubPlan 1 -> Aggregate (cost=27.86..27.87 rows=1 width=8) -> Seq Scan on correlated_orders o (cost=0.00..27.85 rows=2 width=8) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10) AND (customer_id = c.customer_id))
Limitaciones
-
La subconsulta debe estar en la select_list o en una de las condiciones de la cláusula WHERE; de lo contrario, no se transformará.
-
La subconsulta debe devolver una función agregada. Las funciones de agregado definidas por el usuario no se admiten para la transformación.
-
Una subconsulta cuya expresión de devolución no sea una simple función agregada no se transformará.
-
La condición correlacionada en las cláusulas WHERE de la subconsulta debe ser una referencia de columna simple. De lo contrario, no se transformará.
La condición correlacionada en las cláusulas WHERE de la subconsulta debe ser un predicado de igualdad simple.
No se puede transformar una subconsulta correlacionada que contenga una cláusula TOP.
-
La subconsulta no puede contener una cláusula HAVING o una cláusula GROUP BY.
-
La cláusula WHERE de la subconsulta puede contener uno o más predicados combinados con AND. Si la cláusula WHERE contiene una cláusula OR, no se puede transformar.
Uso de la caché de subconsultas para mejorar el rendimiento de las consultas en Babelfish
A partir de la versión 4.2.0, Babelfish 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.
|
Parámetro |
Descripción |
Predeterminado |
Permitida |
|---|---|---|---|
apg_enable_subquery_cache |
Permite el uso de la memoria caché para subconsultas escalares correlacionadas. |
OFF |
ON, OFF |
apg_subquery_cache_check_interval |
Establece la frecuencia, en número de errores de caché, para evaluar la tasa de aciertos de caché de subconsultas. |
500 |
0–2147483647 |
apg_subquery_cache_hit_rate_threshold |
Establece el umbral de la tasa de aciertos de caché de subconsultas. |
0.3 |
0,0-1,0 |
nota
Los valores más altos de
apg_subquery_cache_check_intervalpueden mejorar la precisión de la estimación de los beneficios de la memoria caché según la CHR, pero aumentarán la sobrecarga de la memoria caché, ya que la CHR no se evaluará hasta que la tabla caché tenga un númeroapg_subquery_cache_check_intervalde filas.Los valores altos de
apg_subquery_cache_hit_rate_thresholdsugieren el abandono de la caché de subconsultas y la vuelta a la opción original (reejecución de subconsultas sin caché).
Puede modificar el grupo de parámetros de instancia o clúster para establecer los parámetros. Para obtener más información, consulte Trabajar con grupos de parámetros.
También puede configurar los ajustes solo para la sesión actual llamando a la función set_config. Por ejemplo, para activar la caché de subconsultas en Babelfish, ejecute el siguiente comando. Para obtener más información, consulte Configuration Settings Functions
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
Activación de la caché de subconsultas en Babelfish
Actualmente, la caché de subconsultas está OFF de forma predeterminada. Como ya se ha mencionado, puede activarla modificando el grupo de parámetros. Cuando apg_enable_subquery_cache está ON, Babelfish aplica la caché de subconsultas 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. Para obtener más información, consulte Uso del plan de EXPLAIN para mejorar el rendimiento de las consultas en Babelfish.
1>CREATE TABLE outer_table (outer_col1 INT, outer_col2 INT)2>CREATE TABLE inner_table (inner_col1 INT, inner_col2 INT)3>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'off'2>GO1>SET BABELFISH_SHOWPLAN_ALL ON2>GO1>SELECT outer_col1, (2>SELECT inner_col13>FROM inner_table4>WHERE inner_col2 = outer_col25>) FROM outer_table6>GOQUERY PLAN ------------------------------------------------------------ Query Text: SELECT outer_col1, ( SELECT inner_col1 FROM inner_table WHERE inner_col2 = outer_col2 ) FROM outer_table Seq Scan on outer_table SubPlan 1 -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
1>SET BABELFISH_SHOWPLAN_ALL OFF2>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'on'2>GO
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é.
Seq Scan on outer_table SubPlan 1 -> Memoize Cache Key: outer_table.outer_col2 Cache Mode: logical -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
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.
Seq Scan on outer_table (actual rows=10 loops=1) SubPlan 1 -> Memoize (actual rows=1 loops=10) Cache Key: outer_table.outer_col2 Cache Mode: logical Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on inner_table (actual rows=1 loops=6) Filter: (inner_col2 = outer_table.outer_col2) 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:
-
Subconsultas correlacionadas IN/EXISTS/ANY/ALL
-
Subconsultas correlacionadas que contienen funciones no deterministas.
-
Subconsultas correlacionadas que hacen referencia a una columna de tabla externa del tipo de datos BIT, VARBINARY o BINARY.
