Caso de uso 5: subconsultas o CTE - AWS Guía prescriptiva

Las traducciones son generadas a través de traducción automática. En caso de conflicto entre la traducción y la version original de inglés, prevalecerá la version en inglés.

Caso de uso 5: subconsultas o CTE

Las expresiones de tabla comunes (CTE) ayudan a dividir las consultas grandes en consultas más pequeñas. Esto facilita el mantenimiento de toda la consulta.

Las uniones de subconsultas se sustituyen por combinaciones de CTE, que son más legibles porque la consulta se nombra y se separa dentro de la sección de CTE. Esto resulta especialmente útil cuando el tamaño de la consulta aumenta y resulta más difícil mantenerla. Además, se materializan los resultados del CTE en PostgreSQL. Si llama al CTE en varios lugares, la definición de consulta real se ejecutará solo una vez. El resultado se almacenará en la memoria. Puede usarlo para cualquier lógica compleja que deba usarse en varios lugares de la misma consulta. Coloca esa lógica dentro de un CTE y llama al CTE cualquier número de veces.

Por ejemplo, un cliente utilizaba consultas de aplicaciones en línea con muchas subconsultas dentro de las consultas. Las subconsultas se filtraban según los valores de los parámetros de entrada enviados desde las aplicaciones.

EXPLAIN ANALYZE SELECT * FROM ORDER_DETAILS A WHERE A.ORDID IN (SELECT ORDID FROM PAYMENT_DETAILS) AND A.ORDID IN (SELECT ORDID FROM ITEM_DETAILS ) AND A.ORDID = 1000000;
"Nested Loop Semi Join (cost=3000.00..194258.21 rows=5 width=74) (actual time=201.605..747.945 rows=5 loops=1)" " -> Nested Loop Semi Join (cost=2000.00..135040.47 rows=5 width=74) (actual time=146.016..666.779 rows=5 loops=1)" " -> Gather (cost=1000.00..78580.31 rows=5 width=74) (actual time=58.893..463.570 rows=5 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Parallel Seq Scan on order_details a (cost=0.00..77579.81 rows=2 width=74) (actual time=165.627..549.702 rows=2 loops=3)" " Filter: (ordid = 1000000)" " Rows Removed by Filter: 1666665" " -> Materialize (cost=1000.00..56460.07 rows=3 width=4) (actual time=17.424..40.638 rows=1 loops=5)" " -> Gather (cost=1000.00..56460.06 rows=3 width=4) (actual time=87.113..203.178 rows=1 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Parallel Seq Scan on payment_details (cost=0.00..55459.76 rows=1 width=4) (actual time=174.431..423.792 rows=1 loops=3)" " Filter: (ordid = 1000000)" " Rows Removed by Filter: 1333002" " -> Materialize (cost=1000.00..59217.64 rows=4 width=4) (actual time=11.117..16.231 rows=1 loops=5)" " -> Gather (cost=1000.00..59217.62 rows=4 width=4) (actual time=55.581..81.148 rows=1 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Parallel Seq Scan on item_details (cost=0.00..58217.22 rows=2 width=4) (actual time=287.030..411.004 rows=1 loops=3)" " Filter: (ordid = 1000000)" " Rows Removed by Filter: 1333080" "Planning Time: 0.266 ms" "Execution Time: 747.986 ms"

Tras modificar las subconsultas mediante un CTE y añadir filtros para que solo se recuperen los conjuntos de filas necesarios, el rendimiento de la consulta mejora.

EXPLAIN ANALYZE WITH PAYMENT AS ( SELECT * FROM PAYMENT_DETAILS WHERE ORDID = 1000000 ), ITEM AS (SELECT * FROM ITEM_DETAILS WHERE ORDID = 1000000) SELECT * FROM ORDER_DETAILS A JOIN PAYMENT B ON A.ORDID=B.ORDID JOIN ITEM C ON B.ORDID=C.ORDID
"Nested Loop (cost=3000.00..194258.91 rows=60 width=166) (actual time=586.410..732.918 rows=80 loops=1)" " -> Nested Loop (cost=2000.00..115677.83 rows=12 width=92) (actual time=456.760..457.083 rows=16 loops=1)" " -> Gather (cost=1000.00..59217.62 rows=4 width=48) (actual time=153.802..154.060 rows=4 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Parallel Seq Scan on item_details (cost=0.00..58217.22 rows=2 width=48) (actual time=85.417..249.045 rows=1 loops=3)" " Filter: (ordid = 1000000)" " Rows Removed by Filter: 1333332" " -> Materialize (cost=1000.00..56460.07 rows=3 width=44) (actual time=75.738..75.753 rows=4 loops=4)" " -> Gather (cost=1000.00..56460.06 rows=3 width=44) (actual time=302.947..303.005 rows=4 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Parallel Seq Scan on payment_details (cost=0.00..55459.76 rows=1 width=44) (actual time=184.609..294.784 rows=1 loops=3)" " Filter: (ordid = 1000000)" " Rows Removed by Filter: 1333332" " -> Materialize (cost=1000.00..78580.34 rows=5 width=74) (actual time=8.103..17.238 rows=5 loops=16)" " -> Gather (cost=1000.00..78580.31 rows=5 width=74) (actual time=129.641..275.795 rows=5 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Parallel Seq Scan on order_details a (cost=0.00..77579.81 rows=2 width=74) (actual time=78.556..268.994 rows=2 loops=3)" " Filter: (ordid = 1000000)" " Rows Removed by Filter: 1666665" "Planning Time: 0.108 ms" "Execution Time: 732.953 ms"

Estas son las observaciones de los datos del ejemplo. Al ejecutar la consulta en un conjunto de datos enorme, la diferencia de rendimiento será muy alta.