Creación e interpretación de un plan de consultas - Amazon Redshift
Operadores EXPLAINCombinaciones con EXPLAIN

Creación e interpretación de un plan de consultas

Puede utilizar el plan de consulta para obtener información sobre las operaciones individuales necesarias para ejecutar una consulta. Antes de trabajar con un plan de consulta, le recomendamos que primero comprenda cómo Amazon Redshift administra el procesamiento de las consultas y la creación de planes de consultas. Para obtener más información, consulte Flujo de trabajo de planificación y ejecución de consultas.

Para crear un plan de consulta, ejecute el comando EXPLAIN seguido del texto real de la consulta. En el plan de consulta, se proporciona la siguiente información:

  • Las operaciones que realizará el motor de ejecución, leyendo los resultados de abajo arriba.

  • El tipo de paso que realiza cada operación.

  • Las tablas y las columnas que se utilizan en cada operación.

  • La cantidad de datos que se procesa en cada operación, en cuanto a la cantidad de filas y al ancho de datos en bytes.

  • El costo relativo de la operación. El costo es una medida que compara los tiempos relativos de ejecución de los pasos de un plan. El costo no proporciona información precisa acerca de los tiempo de ejecución reales ni sobre el consumo de memoria, como tampoco proporciona una comparación significativa entre los planes de ejecución. El costo le da una idea de cuáles son las operaciones de una consulta que están consumiendo la mayor cantidad de recursos.

El comando EXPLAIN no ejecuta propiamente la consulta. Solo muestra el plan que ejecuta Amazon Redshift si la consulta se lleva a cabo en las condiciones operativas actuales. Si cambia el esquema o los datos de una tabla y ejecuta nuevamente el comando ANALYZE para actualizar los metadatos estadísticos, el plan de consulta podría ser diferente.

La salida del plan de consulta que genera el comando EXPLAIN es una vista general simplificada de la ejecución de consultas. No muestra los detalles del procesamiento en paralelo de las consultas. Para ver información detallada, ejecute la consulta en cuestión y, luego, obtenga información resumida de la consulta desde la vista SVL_QUERY_SUMMARY o SVL_QUERY_REPORT. Para obtener más información acerca de cómo usar estas vistas, consulte Análisis del resumen de consultas.

En el siguiente ejemplo, se muestra una salida del comando EXPLAIN para una consulta simple GROUP BY sobre la tabla EVENT:

explain select eventname, count(*) from event group by eventname;

                            QUERY PLAN
-------------------------------------------------------------------
XN HashAggregate  (cost=131.97..133.41 rows=576 width=17)
  ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=17)

EXPLAIN devuelve las siguientes métricas para cada operación:

Costo

Un valor relativo que es útil para comparar operaciones dentro de un plan. El costo se compone de dos valores decimales separados por dos puntos, por ejemplo cost=131.97..133.41. El primer valor, en este caso 131.97, proporciona el costo relativo del retorno de la primera fila de esta operación. El segundo valor, en este caso 133.41, proporciona el costo relativo de completar la operación. Los costos del plan de consulta se van acumulando a medida que estudia el plan, por lo que el costo HashAggregate de este ejemplo (131.97..133.41) incluye el costo del examen secuencial que está debajo (0.00..87.98).

Filas

La cantidad estimada de filas que se devuelven. En este ejemplo, se espera que el examen devuelva 8798 filas. Se espera que el operador HashAggregate por sí solo devuelva 576 filas (después de descartar los nombres de eventos duplicados del conjunto de resultados).

nota

El cálculo de filas se basa en las estadísticas disponibles que genera el comando ANALYZE. Si ANALYZE no se ejecutó recientemente, el cálculo será menos fiable.

Ancho

El ancho estimado de la fila promedio, medido en bytes. En este ejemplo, se espera que la fila promedio sea de 17 bytes de ancho.

Operadores EXPLAIN

En esta sección, se describen brevemente los operadores que ven con mayor frecuencia en la salida del comando EXPLAIN. Para obtener una lista completa de los operadores, consulte EXPLAIN en la sección sobre comandos SQL.

Operador de examen secuencial

El operador de examen secuencial (Seq Scan) indica el examen de una tabla. Seq Scan examina cada columna de la tabla de manera secuencial, de principio a fin, y evalúa las restricciones de la consulta (en la cláusula WHERE) para cada una de las filas.

Operadores de combinación

Amazon Redshift selecciona los operadores de combinación en función del diseño físico de las tablas que se combinarán, la ubicación de los datos que se necesitan para la combinación y los requisitos específicos de la propia consulta.

  • Bucle anidado

    La combinación menos óptima es el bucle anidado, que se utiliza principalmente para las combinaciones cruzadas (productos cartesianos) y para algunas combinaciones de desigualdades.

  • Operadores hash join y hash

    Se utilizan los operadores hash join y hash, por lo general más rápidos que la combinación de bucle anidado, para las combinaciones internas y las combinaciones externas izquierda y derecha. Estos operadores se utilizan para combinar tablas en las que las columnas de combinación no son claves de distribución ni claves de ordenación al mismo tiempo. El operador hash crea una tabla hash para la tabla interna de la combinación; el operador hash join lee la tabla exterior, aplica la función hash a la columna de combinación y encuentra coincidencias en la tabla hash interna.

  • Merge Join

    Se utiliza el operador merge join, que, por lo general, es el operador más rápido, para las combinaciones internas y externas. Merge join no se utiliza para las combinaciones totales. Este operador se utiliza para combinar tablas en las que las columnas de combinación son claves de distribución y claves de ordenación al mismo tiempo, y cuando menos del 20 por ciento de las tablas de combinación está desordenado. Lee dos tablas ordenadas en orden y encuentra las filas que coinciden. Para ver el porcentaje de filas desordenadas, consulta la tabla de sistema SVV_TABLE_INFO.

  • Unión espacial

    Suele ser una unión rápida basada en la proximidad de los datos espaciales, que se utiliza para los tipos de datos GEOMETRY y GEOGRAPHY.

Operadores de agregación

El plan de consulta utiliza los siguientes operadores en las consultas que implican funciones de agregación y operaciones GROUP BY.

  • Aggregate

    Operador para las funciones de agregación escalar, como AVG y SUM.

  • HashAggregate

    Operador para las funciones de agregación agrupadas desordenadas.

  • GroupAggregate

    Operador para las funciones de agregación agrupadas ordenadas.

Operadores de ordenación

El plan de consulta utiliza los siguientes operadores cuando las consultas tienen que ordenar o fusionar conjuntos de resultados.

  • Sort

    Evalúa la cláusula ORDER BY y otras operaciones de ordenación, como las que se necesitan en las consultas y combinaciones UNION, en las consultas SELECT DISTINCT y en las funciones de ventana.

  • Merge

    Produce resultados ordenados finales conforme a los resultados intermedios ordenados que se derivan de las operaciones en paralelo.

Operadores UNION, INTERSECT y EXCEPT

El plan de consulta utiliza los siguientes operadores en las consultas que implican operaciones de conjunto con los operadores UNION, INTERSECT y EXCEPT.

  • Operador subquery

    Se utiliza para ejecutar consultas UNION.

  • Hash Intersect Distinct

    Se utiliza para ejecutar consultas INTERSECT .

  • Operador SetOp Except

    Se utiliza para ejecutar consultas EXCEPT (o MINUS).

Otros operadores

Los siguientes operadores también aparecen con frecuencia en la salida EXPLAIN para las consultas rutinarias.

  • Unique

    Elimina los duplicados de las consultas SELECT DISTINCT y UNION.

  • Límite

    Procesa la cláusula LIMIT.

  • Window

    Ejecuta las funciones de ventana.

  • Resultado

    Ejecuta las funciones escalares que no implican obtener acceso a ninguna tabla.

  • Subplan

    Se utiliza para algunas subconsultas.

  • Red

    Envía los resultados intermedios al nodo principal para seguir trabajando con ellos.

  • Materialize

    Guarda las filas para entradas de combinaciones de bucle anidado y en algunas de combinaciones de fusión.

Combinaciones con EXPLAIN

El optimizador de consultas utiliza diferentes tipos de combinaciones para recuperar datos de tablas, en función de la estructura de la consulta y de las tablas subyacentes. La salida EXPLAIN indica el tipo de combinación, las tablas utilizadas y la manera en la que se distribuyen los datos de la tabla en todo el clúster para describir cómo se procesa la consulta.

Ejemplos de tipos de combinación

En los siguientes ejemplos, se muestran los distintos tipos de combinación que puede utilizar el optimizador de consultas. El tipo de combinación que se utiliza en el plan de consulta depende del diseño físico de las tablas implicadas.

Ejemplo: Combinación de dos tablas mediante el operador hash

La siguiente consulta combina EVENT y CATEGORY en la columna CATID. CATID es la clave de distribución y ordenación para CATEGORY, pero no para EVENT. Se realiza una combinación hash con EVENT como la tabla externa y CATEGORY como la tabla interna. Como CATEGORY es la tabla más pequeña, el planificador difunde una copia de esta en los nodos de computación durante el procesamiento de la consulta mediante el comando DS_BCAST_INNER. El costo de combinación en este ejemplo contempla la mayor parte del costo acumulado del plan.

explain select * from category, event where category.catid=event.catid;

                               QUERY PLAN
-------------------------------------------------------------------------
 XN Hash Join DS_BCAST_INNER  (cost=0.14..6600286.07 rows=8798 width=84)
   Hash Cond: ("outer".catid = "inner".catid)
   ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=35)
   ->  XN Hash  (cost=0.11..0.11 rows=11 width=49)
         ->  XN Seq Scan on category  (cost=0.00..0.11 rows=11 width=49)
nota

Las sangrías alineadas de los operadores en la salida EXPLAIN, a menudo, indican que esas operaciones no dependen unas de otras y que pueden comenzar en paralelo. En el ejemplo anterior, si bien el examen de la tabla EVENT y la operación hash están alineados, el examen EVENT debe esperar hasta que la operación hash haya terminado por completo.

Ejemplo: Combinación de dos tablas mediante el operador merge

La siguiente consulta también usa el operador SELECT*, pero combina SALES y LISTING en la columna LISTID, donde LISTID está configurada como la clave de distribución y de ordenación para ambas tablas. Se elige una combinación de fusión y no se necesita redistribuir datos para la combinación (DS_DIST_NONE).

explain select * from sales, listing where sales.listid = listing.listid;
QUERY PLAN
-----------------------------------------------------------------------------
XN Merge Join DS_DIST_NONE  (cost=0.00..6285.93 rows=172456 width=97)
  Merge Cond: ("outer".listid = "inner".listid)
  ->  XN Seq Scan on listing  (cost=0.00..1924.97 rows=192497 width=44)
  ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=53)

En el siguiente ejemplo, se demuestran los distintos tipos de combinación dentro de la misma consulta. Como en el ejemplo anterior, SALES y LISTING están combinadas por fusión, pero la tercera tabla, EVENT, debe estar combinada por hash con los resultados de la combinación de fusión. Una vez más, la combinación hash implica un costo de difusión.

explain select * from sales, listing, event
where sales.listid = listing.listid and sales.eventid = event.eventid;
                                  QUERY PLAN
----------------------------------------------------------------------------
XN Hash Join DS_BCAST_INNER  (cost=109.98..3871130276.17 rows=172456 width=132)
  Hash Cond: ("outer".eventid = "inner".eventid)
  ->  XN Merge Join DS_DIST_NONE  (cost=0.00..6285.93 rows=172456 width=97)
        Merge Cond: ("outer".listid = "inner".listid)
        ->  XN Seq Scan on listing  (cost=0.00..1924.97 rows=192497 width=44)
        ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=53)
  ->  XN Hash  (cost=87.98..87.98 rows=8798 width=35)
        ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=35)

Ejemplo: Operadores join, aggregate y sort

La siguiente consulta ejecuta una unión hash de las tablas SALES y EVENT, seguida de las operaciones de agregación y ordenación para justificar la función agrupada SUM y la cláusula ORDER BY. El operador inicial sort se ejecuta en paralelo en los nodos de computación. Luego, el operador network envía los resultados al nodo principal, donde el operador merge produce resultados finales ordenados.

explain select eventname, sum(pricepaid) from sales, event 
where sales.eventid=event.eventid group by eventname
order by 2 desc;
                                           QUERY PLAN
---------------------------------------------------------------------------------
 XN Merge  (cost=1002815366604.92..1002815366606.36 rows=576 width=27)
  Merge Key: sum(sales.pricepaid)
  ->  XN Network  (cost=1002815366604.92..1002815366606.36 rows=576 width=27)
        Send to leader
        ->  XN Sort  (cost=1002815366604.92..1002815366606.36 rows=576 width=27)
              Sort Key: sum(sales.pricepaid)
              ->  XN HashAggregate  (cost=2815366577.07..2815366578.51 rows=576 width=27)
                    ->  XN Hash Join DS_BCAST_INNER  (cost=109.98..2815365714.80 rows=172456 width=27)
                          Hash Cond: ("outer".eventid = "inner".eventid)
                          ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=14)
                          ->  XN Hash  (cost=87.98..87.98 rows=8798 width=21)
                                ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=21)

Redistribución de datos

La salida EXPLAIN para las combinaciones también especifica un método con el cual los datos se mueven en torno a un clúster para facilitar la combinación. Este movimiento de datos puede ser una difusión o bien una redistribución. En una difusión, los valores de los datos de un lado de la combinación se copian desde cada nodo de computación a los demás nodos de computación de manera que cada nodo de computación tenga una copia completa de los datos. En una redistribución, se envían los valores de los datos participantes desde el sector donde están hacia un nuevo sector (posiblemente en un nodo diferente). Los datos, por lo general, se redistribuyen para coincidir con la clave de distribución de la otra tabla participante de la combinación si esa clave de distribución es una de las columnas que se está combinando. Si ninguna de las tablas tiene claves de distribución en las columnas que se están combinando, se distribuyen ambas tablas, o bien se difunde la tabla interna en cada uno de los nodos.

La salida EXPLAIN también hace referencia a las tablas internas y externas. Primero se examina la tabla interna; esta aparece cerca de la parte inferior del plan de consulta. La tabla interna es la tabla en la que se buscan coincidencias. Por lo general, se almacena en la memora y suele ser la tabla origen de algoritmos hash y, de ser posible, la tabla más pequeña de las dos que se están combinando. La tabla externa es el origen de las filas en las que se busca coincidencias con la tabla interna. Por lo general, se lee del disco. El optimizador de consultas selecciona la tabla interna y externa en función de las estadísticas de la base de datos extraídas de la última ejecución del comando ANALYZE. El orden de las tablas en la cláusula FROM de una consulta no determina cuál es la tabla interna y cuál es la externa.

Utilice los siguientes atributos en los planes de consulta para identificar cómo se mueven los datos y, así, facilitar una consulta:

  • DS_BCAST_INNER

    Se difunde una copia de toda la tabla interna a todos los nodos de computación.

  • DS_DIST_ALL_NONE

    No es necesario redistribuir, porque ya se distribuyó la tabla interna a cada nodo con el atributo DISTSTYLE ALL.

  • DS_DIST_NONE

    No se redistribuye ninguna tabla. Es posible que haya combinaciones colocadas porque se combinan los sectores correspondientes sin mover los datos entre los nodos.

  • DS_DIST_INNER

    Se redistribuye la tabla interna.

  • DS_DIST_OUTER

    Se redistribuye la tabla externa.

  • DS_DIST_ALL_INNER

    Se redistribuye toda la tabla interna a un único sector porque la tabla externa utiliza el atributo DISTSTYLE ALL.

  • DS_DIST_BOTH

    Ambas tablas se redistribuyen.