La estructura de un plan de consulta es un árbol de nodos de plan (plan nodes). Los nodos en el nivel inferior del árbol son nodos de escaneo (scan nodes): devuelven filas directamente desde una tabla. Existen diferentes tipos de nodos de escaneo para distintos métodos de acceso a tablas: escaneos secuenciales (sequential scans), escaneos de índices (index scans) y escaneos de índices por mapa de bits (bitmap index scans). También hay fuentes de filas que no provienen de tablas, como las cláusulas VALUES y las funciones que devuelven conjuntos de filas en la cláusula FROM, las cuales tienen sus propios tipos de nodos de escaneo. Si la consulta requiere uniones (joins), agregación, ordenación u otras operaciones sobre las filas obtenidas, habrá nodos adicionales por encima de los nodos de escaneo para realizar estas operaciones. Una vez más, suele haber más de una forma posible de realizar estas operaciones, por lo que aquí también pueden aparecer diferentes tipos de nodos. La salida de EXPLAIN tiene una línea para cada nodo en el árbol del plan, mostrando el tipo de nodo básico junto con las estimaciones de coste que el planificador realizó para la ejecución de ese nodo del plan. Pueden aparecer líneas adicionales, sangradas desde la línea de resumen del nodo, para mostrar propiedades adicionales del nodo. La primera línea (la línea de resumen del nodo superior) tiene el coste total estimado de ejecución para el plan; es este número el que el planificador busca minimizar.

Aquí tienes un ejemplo trivial, solo para mostrar cómo se ve la salida:

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

Dado que esta consulta no tiene cláusula WHERE, debe escanear todas las filas de la tabla, por lo que el planificador ha elegido utilizar un plan de escaneo secuencial simple. Los números que aparecen entre paréntesis son (de izquierda a derecha):

Los costes se miden en unidades arbitrárias determinadas por los parámetros de coste del planificador (ver Section 19.7.2). La práctica tradicional consiste en medir los costes en unidades de lecturas de páginas de disco; es decir, seq_page_cost se establece por defecto en 1.0 y los demás parámetros de coste se definen en relación con este. Los ejemplos de esta sección se ejecutan con los parámetros de coste por defecto.

Es importante entender que el coste de un nodo de nivel superior incluye el coste de todos sus nodos hijos. También es importante tener en cuenta que el coste solo refleja los aspectos que importan al planificador. En particular, el coste no considera el tiempo dedicado a convertir los valores de salida a formato de texto o a transmitirlos al cliente, lo cual podría ser un factor importante en el tiempo real transcurrido; pero el planificador ignora esos costes porque no puede cambiarlos alterando el plan. (Confiamos en que cada plan correcto producirá la misma salida de filas).

El valor de rows es un poco confuso porque no es el número de filas procesadas o escaneadas por el nodo del plan, sino el número emitido por el nodo. A menudo es menor que el número de filas escaneadas, como resultado del filtrado por cualquier condición de la cláusula WHERE que se aplique en el nodo. Idealmente, la estimación de filas del nivel superior se aproximará al número de filas realmente devueltas, actualizadas o eliminadas por la consulta.

Volviendo a nuestro ejemplo:

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

Estos números se obtienen de forma muy directa. Si ejecutas:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

descubrirás que tenk1 tiene 345 páginas de disco y 10000 filas. El coste estimado se calcula como (páginas de disco leídas * seq_page_cost) + (filas escaneadas * cpu_tuple_cost). Por defecto, seq_page_cost es 1.0 y cpu_tuple_cost es 0.01, por lo que el coste estimado es (345 * 1.0) + (10000 * 0.01) = 445.

Now modifiquemos la consulta para añadir una condición WHERE:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244)
    Filter: (unique1 < 7000)

Observa que la salida de EXPLAIN muestra la cláusula WHERE aplicada como una condición de “filtro” asociada al nodo de plan Seq Scan. Esto significa que el nodo de plan verifica la condición para cada fila que escanea y emite solo las que pasan la condición. La estimación de filas de salida se ha reducido debido a la cláusula WHERE. Sin embargo, el escaneo aún tendrá que visitar las 10000 filas, por lo que el coste no ha disminuido; de hecho, ha subido un poco (en 10000 * cpu_operator_cost, para ser exactos) para reflejar el tiempo de CPU adicional dedicado a verificar la condición WHERE.

El número real de filas que seleccionaría esta consulta es 7000, pero la estimación de rows es solo aproximada. Si intentas duplicar este experimento, es muy posible que obtengas una estimación ligeramente diferente; además, esta puede cambiar después de cada comando ANALYZE, porque las estadísticas producidas por ANALYZE se toman de una muestra aleatoria de la tabla.

Ahora, hagamos la condición más restrictiva:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

Aquí el planificador ha decidido utilizar un plan de dos pasos: el nodo de plan hijo visita un índice para encontrar las ubicaciones de las filas que coinciden con la condición del índice, y luego el nodo de plan superior recupera esas filas de la propia tabla. Recuperar filas por separado es mucho más costoso que leerlas secuencialmente, pero como no es necesario visitar todas las páginas de la tabla, esto sigue siendo más barato que un escaneo secuencial. (La razón para usar dos niveles de plan es que el nodo de plan superior ordena las ubicaciones de las filas identificadas por el índice en orden físico antes de leerlas, para minimizar el coste de las lecturas independientes. El “bitmap” mencionado en los nombres de los nodos es el mecanismo que realiza la ordenación).

Ahora añadamos otra condición a la cláusula WHERE:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.04..225.20 rows=1 width=244)
   Recheck Cond: (unique1 < 100)
   Filter: (stringu1 = 'xxx'::name)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

La condición añadida stringu1 = 'xxx' reduce la estimación de filas de salida, pero no el coste, porque todavía tenemos que visitar el mismo conjunto de filas. Esto se debe a que la cláusula stringu1 no se puede aplicar como una condición de índice, ya que este índice solo está en la columna unique1. En su lugar, se aplica como un filtro sobre las filas recuperadas utilizando el índice. Por lo tanto, el coste de hecho ha subido ligeramente para reflejar esta verificación adicional.

En algunos casos, el planificador preferirá un plan de escaneo de índice “simple”:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: (unique1 = 42)

En este tipo de plan, las filas de la tabla se recuperan en el orden del índice, lo que las hace aún más costosas de leer, pero son tan pocas que no vale la pena pagar el coste de ordenar sus ubicaciones. Verás este tipo de plan con mayor frecuencia para consultas que recuperan una sola fila. También se utiliza a menudo para consultas que tienen una condición ORDER BY que coincide con el orden del índice, ya que entonces no se necesita ningún paso de ordenación adicional para satisfacer el ORDER BY. En este ejemplo, añadir ORDER BY unique1 utilizaría el mismo plan porque el índice ya proporciona implícitamente la ordenación solicitada.

El planificador puede implementar una cláusula ORDER BY de varias maneras. El ejemplo anterior muestra que dicha cláusula de ordenación puede implementarse implícitamente. El planificador también puede añadir un paso Sort explícito:

EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;

                            QUERY PLAN
-------------------------------------------------------------------
 Sort  (cost=1109.39..1134.39 rows=10000 width=244)
   Sort Key: unique1
   ->  Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

Si una parte del plan garantiza una ordenación en un prefijo de las claves de ordenación requeridas, el planificador puede decidir en su lugar utilizar un paso de ordenación incremental (Incremental Sort):

EXPLAIN SELECT * FROM tenk1 ORDER BY hundred, ten LIMIT 100;

                                              QUERY PLAN
---------------------------------------------------------------------------------------------
 Limit  (cost=19.35..39.49 rows=100 width=244)
   ->  Incremental Sort  (cost=19.35..2033.39 rows=10000 width=244)
         Sort Key: hundred, ten
         Presorted Key: hundred
         ->  Index Scan using tenk1_hundred on tenk1  (cost=0.29..1574.20 rows=10000 width=244)

En comparación con las ordenaciones habituales, ordenar incrementalmente permite devolver tuplas antes de que se haya ordenado todo el conjunto de resultados, lo que permite optimizar especialmente las consultas con LIMIT. También puede reducir el uso de memoria y la probabilidad de volcar las ordenaciones al disco, pero tiene el coste de la sobrecarga adicional de dividir el conjunto de resultados en múltiples lotes de ordenación.

If hay índices independientes en varias de las columnas referenciadas en WHERE, el planificador podría elegir utilizar una combinación AND u OR de los índices:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                      QUERY PLAN
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  BitmapAnd  (cost=25.07..25.07 rows=10 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
               Index Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0)
               Index Cond: (unique2 > 9000)

Pero esto requiere visitar ambos índices, por lo que no es necesariamente una victoria en comparación con el uso de un solo índice y el tratamiento de la otra condición como un filtro. Si varías los rangos involucrados, verás cambiar el plan en consecuencia.

Aquí tienes un ejemplo que muestra los efectos de LIMIT:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

                                      QUERY PLAN
-------------------------------------------------------------------------------------
 Limit  (cost=0.29..14.28 rows=2 width=244)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.27 rows=10 width=244)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)

Esta es la misma consulta que la anterior, pero hemos añadido un LIMIT de modo que no se necesiten recuperar todas las filas, y el planificador ha cambiado de opinión sobre qué hacer. Observa que el coste total y el número de filas del nodo Index Scan se muestran como si se ejecutara hasta su finalización. Sin embargo, se espera que el nodo Limit se detenga tras recuperar solo una quinta parte de esas filas, por lo que su coste total es solo una quinta parte del total, y ese es el coste estimado real de la consulta. Este plan se prefiere a añadir un nodo Limit al plan anterior porque el Limit no podría evitar pagar el coste de inicio del escaneo de mapa de bits, por lo que el coste total superaría las 25 unidades con ese enfoque.

Intentemos unir dos tablas, utilizando las columnas de las que hemos estado hablando:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                       QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)
         Index Cond: (unique2 = t1.unique2)

En este plan, tenemos un nodo de unión de bucle anidado (nested-loop join) con dos escaneos de tabla como entradas o hijos. La sangría de las líneas de resumen de los nodos refleja la estructura del árbol del plan. El primer hijo del join, o hijo “externo” (outer), es un escaneo de mapa de bits similar a los que vimos antes. Su coste y recuento de filas son los mismos que obtendríamos de SELECT ... WHERE unique1 < 10 porque estamos aplicando la cláusula WHERE unique1 < 10 en ese nodo. La cláusula t1.unique2 = t2.unique2 aún no es relevante, por lo que no afecta al recuento de filas del escaneo externo. El nodo de unión de bucle anidado ejecutará su segundo hijo, o hijo “interno” (inner), una vez por cada fila obtenida de su hijo externo. Los valores de las columnas de la fila externa actual pueden introducirse en el escaneo interno; aquí, el valor t1.unique2 de la fila externa está disponible, por lo que obtenemos un plan y unos costes similares a los que vimos anteriormente para un caso simple SELECT ... WHERE t2.unique2 = constante. (El coste estimado es en realidad un poco más bajo que el visto anteriormente, como resultado de la caché que se espera que se produzca durante los repetidos escaneos de índices en t2). Los costes del nodo del bucle se establecen entonces sobre la base del coste del escaneo externo, más una repetición del escaneo interno por cada fila externa (10 * 7.90, en este caso), más un poco de tiempo de CPU para el procesamiento de la unión.

En este ejemplo, el número de filas de salida de la unión es el mismo que el producto de los números de filas de los dos escaneos, pero eso no es cierto en todos los casos porque puede haber cláusulas WHERE adicionales que mencionen ambas tablas y que, por tanto, solo puedan aplicarse en el punto de unión, no a ninguno de los escaneos de entrada. He aquí un ejemplo:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;

                                          QUERY PLAN
---------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..49.36 rows=33 width=488)
   Join Filter: (t1.hundred < t2.hundred)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)
         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)
               Index Cond: (unique2 < 10)

La condición t1.hundred < t2.hundred no se puede comprobar en el índice tenk2_unique2, por lo que se aplica en el nodo de unión. Esto reduce el número estimado de filas de salida del nodo de unión, pero no cambia ninguno de los escaneos de entrada.

Observa que aquí el planificador ha elegido “materializar” la relación interna de la unión, colocando un nodo de plan Materialize encima de ella. Esto significa que el escaneo del índice t2 se realizará una sola vez, a pesar de que el nodo de unión de bucle anidado necesita leer esos datos diez veces, una por cada fila de la relación externa. El nodo Materialize conserva los datos en memoria a medida que se leen, y luego los devuelve desde la memoria en cada paso subsiguiente.

Al trabajar con uniones externas (outer joins), es posible que veas nodos de plan de unión con condiciones de “Join Filter” y de “Filter” simple asociadas. Las condiciones de Join Filter provienen de la cláusula ON de la unión externa, por lo que una fila que no cumpla la condición de Join Filter podría seguir emitiéndose como una fila extendida con nulos. Pero una condición de Filter simple se aplica después de las reglas de la unión externa y, por tanto, actúa para eliminar filas incondicionalmente. En una unión interna (inner join) no hay diferencia semántica entre estos tipos de filtros.

Si cambiamos un poco la selectividad de la consulta, podríamos obtener un plan de unión muy diferente:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                         QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=226.23..709.73 rows=100 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

Aquí, el planificador ha elegido utilizar una unión hash (hash join), en la cual las filas de una tabla se introducen en una tabla hash en memoria, tras lo cual se escanea la otra tabla y se examina la tabla hash para buscar coincidencias para cada fila. De nuevo, observa cómo la sangría refleja la estructura del plan: el escaneo de mapa de bits en tenk1 es la entrada al nodo Hash, que construye la tabla hash. Esta se devuelve al nodo Hash Join, que lee las filas de su hijo externo y busca cada una de ellas en la tabla hash.

Otro tipo posible de unión es la unión por fusión (merge join), ilustrada aquí:

EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                         QUERY PLAN
------------------------------------------------------------------------------------------
 Merge Join  (cost=0.56..233.49 rows=10 width=488)
   Merge Cond: (t1.unique2 = t2.unique2)
   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)
         Filter: (unique1 < 100)
   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)

La unión por fusión requiere que sus datos de entrada estén ordenados según las claves de la unión. En este ejemplo, cada entrada se ordena mediante un escaneo de índice para visitar las filas en el orden correcto; pero también se podría utilizar un escaneo secuencial y una ordenación. (El escaneo secuencial seguido de una ordenación a menudo supera a un escaneo de índice para ordenar muchas filas, debido al acceso no secuencial a disco requerido por el escaneo de índice).

Una forma de analizar planes alternativos es obligar al planificador a descartar la estrategia que consideraba más barata, utilizando las banderas de habilitación/deshabilitación descritas en Section 19.7.1. (Esta es una herramienta rudimentaria, pero útil. Consulta también la Section 14.3). Por ejemplo, si no estamos convencidos de que la unión por fusión sea el mejor tipo de unión para el ejemplo anterior, podríamos probar:

SET enable_mergejoin = off;

EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                         QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=226.23..344.08 rows=10 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on onek t2  (cost=0.00..114.00 rows=1000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

lo que muestra que el planificador estima que la unión hash sería casi un 50% más costosa que la unión por fusión en este caso. Por supuesto, la siguiente pregunta es si tiene razón al respecto. Podemos investigarlo utilizando EXPLAIN ANALYZE, como se describe más abajo.

Al utilizar las banderas de habilitación/deshabilitación para desactivar tipos de nodos de plan, muchas de las banderas solo desincentivan el uso del nodo correspondiente y no prohíben por completo la capacidad del planificador para utilizar ese tipo de nodo. Esto es así por diseño para que el planificador siga manteniendo la capacidad de formar un plan para una consulta determinada. Cuando el plan resultante contenga un nodo deshabilitado, la salida de EXPLAIN indicará este hecho.

SET enable_seqscan = off;
EXPLAIN SELECT * FROM unit;

                        QUERY PLAN
---------------------------------------------------------
 Seq Scan on unit  (cost=0.00..21.30 rows=1130 width=44)
   Disabled: true

Debido a que la tabla unit no tiene índices, no hay otro medio para leer los datos de la tabla, por lo que el escaneo secuencial es la única opción disponible para el planificador de consultas.

Algunos planes de consultas implican subplanes (subplans), que surgen de subconsultas SELECT en la consulta original. A veces estas consultas pueden transformarse en planes de unión ordinarios, pero cuando no se puede, obtenemos planes como:

EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t
WHERE t.ten < ALL (SELECT o.ten FROM onek o WHERE o.four = t.four);

                                QUERY PLAN
-------------------------------------------------------------------------
 Seq Scan on public.tenk1 t  (cost=0.00..586095.00 rows=5000 width=4)
   Output: t.unique1
   Filter: (ALL (t.ten < (SubPlan 1).col1))
   SubPlan 1
     ->  Seq Scan on public.onek o  (cost=0.00..116.50 rows=250 width=4)
           Output: o.ten
           Filter: (o.four = t.four)

Este ejemplo bastante artificial sirve para ilustrar un par de puntos: los valores del nivel superior del plan pueden pasarse a un subplan (aquí se pasa t.four) y los resultados de la subconsulta están disponibles para el plan externo. Esos valores de resultados se muestran mediante EXPLAIN con anotaciones como (nombre_subplan).colN, que hace referencia a la columna de salida N de la subconsulta SELECT.

En el ejemplo anterior, el operador ALL ejecuta el subplan de nuevo para cada fila de la consulta externa (lo que explica el alto coste estimado). Algunas consultas pueden utilizar un subplan con hash (hashed subplan) para evitar esto:

EXPLAIN SELECT *
FROM tenk1 t
WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);

                                          QUERY PLAN
--------------------------------------------------------------------------------------------
 Seq Scan on tenk1 t  (cost=61.77..531.77 rows=5000 width=244)
   Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
   SubPlan 1
     ->  Index Only Scan using onek_unique1 on onek o  (cost=0.28..59.27 rows=1000 width=4)
(4 rows)

Aquí, el subplan se ejecuta una sola vez y su salida se carga en una tabla hash en memoria, la cual es examinada por el operador ANY externo. Esto requiere que la subconsulta SELECT no haga referencia a ninguna variable de la consulta externa, y que el operador de comparación de ANY sea apto para hash.

Si, además de no hacer referencia a ninguna variable de la consulta externa, la subconsulta SELECT no puede devolver más de una fila, podría implementarse en su lugar como un plan inicial (initplan):

EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);

                              QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on public.tenk1 t1  (cost=0.02..470.02 rows=1000 width=4)
   Output: t1.unique1
   Filter: (t1.ten = (InitPlan 1).col1)
   InitPlan 1
     ->  Result  (cost=0.00..0.02 rows=1 width=4)
           Output: ((random() * '10'::double precision))::integer

Un initplan se ejecuta una sola vez por cada ejecución del plan externo, y sus resultados se guardan para su reutilización en filas posteriores del plan externo. Así que en este ejemplo, random() se evalúa una sola vez y todos los valores de t1.ten se comparan con el mismo entero elegido al azar. Esto es bastante diferente de lo que ocurriría sin la estructura de la subconsulta SELECT.

Es posible comprobar la precisión de las estimaciones del planificador utilizando la opción ANALYZE de EXPLAIN. Con esta opción, EXPLAIN ejecuta realmente la consulta y luego muestra los recuentos de filas reales y los tiempos de ejecución reales acumulados en cada nodo del plan, junto con las mismas estimaciones que muestra un EXPLAIN simple. Por ejemplo, podríamos obtener un resultado como este:

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                                            QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10.00 loops=1)
   Buffers: shared hit=36 read=6
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10.00 loops=1)
         Recheck Cond: (unique1 < 10)
         Heap Blocks: exact=10
         Buffers: shared hit=3 read=5 written=4
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10.00 loops=1)
               Index Cond: (unique1 < 10)
               Index Searches: 1
               Buffers: shared hit=2
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1.00 loops=10)
         Index Cond: (unique2 = t1.unique2)
         Index Searches: 10
         Buffers: shared hit=24 read=6
 Planning:
   Buffers: shared hit=15 dirtied=9
 Planning Time: 0.485 ms
 Execution Time: 0.073 ms

Ten en cuenta que los valores de “actual time” (tiempo real) están en milisegundos de tiempo real, mientras que las estimaciones de cost (coste) se expresan en unidades arbitrarias; por lo tanto, es poco probable que coincidan. Lo que suele ser más importante buscar es si los recuentos de filas estimados se acercan razonablemente a la realidad. En este ejemplo, las estimaciones fueron todas exactas, pero eso es bastante inusual en la práctica.

En algunos planes de consulta, es posible que un nodo de subplan se ejecute más de una vez. Por ejemplo, el escaneo del índice interno se ejecutará una vez por cada fila externa en el plan de bucle anidado anterior. En tales casos, el valor de loops indica el número total de ejecuciones del nodo, y los valores mostrados de tiempo real y filas son promedios por ejecución. Esto se hace para que los números sean comparables con la forma en que se muestran las estimaciones de costes. Multiplica por el valor de loops para obtener el tiempo total realmente empleado en el nodo. En el ejemplo anterior, dedicamos un total de 0.030 milisegundos a ejecutar los escaneos de índices en t2.

En algunos casos, EXPLAIN ANALYZE muestra estadísticas de ejecución adicionales más allá de los tiempos de ejecución de los nodos del plan y los recuentos de filas. Por ejemplo, los nodos Sort y Hash proporcionan información adicional:

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;

                                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100.00 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 74kB
   Buffers: shared hit=440
   ->  Hash Join  (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100.00 loops=1)
         Hash Cond: (t2.unique2 = t1.unique2)
         Buffers: shared hit=437
         ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000.00 loops=1)
               Buffers: shared hit=345
         ->  Hash  (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100.00 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 35kB
               Buffers: shared hit=92
               ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100.00 loops=1)
                     Recheck Cond: (unique1 < 100)
                     Heap Blocks: exact=90
                     Buffers: shared hit=92
                     ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100.00 loops=1)
                           Index Cond: (unique1 < 100)
                           Index Searches: 1
                           Buffers: shared hit=2
 Planning:
   Buffers: shared hit=12
 Planning Time: 0.187 ms
 Execution Time: 3.036 ms

El nodo Sort muestra el método de ordenación utilizado (en particular, si la ordenación se realizó en memoria o en disco) y la cantidad de memoria o espacio de disco necesario. El nodo Hash muestra el número de buckets de hash y lotes (batches), así como la cantidad pico de memoria utilizada para la tabla hash. (Si el número de lotes supera a uno, también habrá uso de espacio en disco implicado, pero eso no se muestra).

Los nodos Index Scan (así como los nodos Bitmap Index Scan e Index-Only Scan) muestran una línea de “Index Searches” que informa del número total de búsquedas en todas las ejecuciones del nodo/loops:

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 500, 700, 999);
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=9.45..73.44 rows=40 width=244) (actual time=0.012..0.028 rows=40.00 loops=1)
   Recheck Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
   Heap Blocks: exact=39
   Buffers: shared hit=47
   ->  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..9.44 rows=40 width=0) (actual time=0.009..0.009 rows=40.00 loops=1)
         Index Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
         Index Searches: 4
         Buffers: shared hit=8
 Planning Time: 0.029 ms
 Execution Time: 0.034 ms

Aquí vemos un nodo Bitmap Index Scan que necesitó 4 búsquedas de índice independientes. El escaneo tuvo que buscar en el índice desde la página raíz del índice tenk1_thous_tenthous una vez por cada valor integer de la estructura IN del predicado. Sin embargo, el número de búsquedas de índice a menudo no tendrá una correspondencia tan directa con el predicado de la consulta:

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 2, 3, 4);
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=9.45..73.44 rows=40 width=244) (actual time=0.009..0.019 rows=40.00 loops=1)
   Recheck Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
   Heap Blocks: exact=38
   Buffers: shared hit=40
   ->  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..9.44 rows=40 width=0) (actual time=0.005..0.005 rows=40.00 loops=1)
         Index Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
         Index Searches: 1
         Buffers: shared hit=2
 Planning Time: 0.029 ms
 Execution Time: 0.026 ms

Esta variante de nuestra consulta IN realizó solo 1 búsqueda en el índice. Dedicó menos tiempo a recorrer el índice (en comparación con la consulta original) porque su estructura IN utiliza valores que coinciden con tuplas del índice almacenadas unas al lado de otras, en la misma página de hoja del índice tenk1_thous_tenthous.

La línea “Index Searches” también es útil con los escaneos de índices B-tree que aplican la optimización de escaneo con saltos (skip scan) para recorrer un índice de forma más eficiente:

EXPLAIN ANALYZE SELECT four, unique1 FROM tenk1 WHERE four BETWEEN 1 AND 3 AND unique1 = 42;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using tenk1_four_unique1_idx on tenk1  (cost=0.29..6.90 rows=1 width=8) (actual time=0.006..0.007 rows=1.00 loops=1)
   Index Cond: ((four >= 1) AND (four <= 3) AND (unique1 = 42))
   Heap Fetches: 0
   Index Searches: 3
   Buffers: shared hit=7
 Planning Time: 0.029 ms
 Execution Time: 0.012 ms

Aquí vemos un nodo Index-Only Scan que utiliza tenk1_four_unique1_idx, un índice multicolumna en las columnas four y unique1 de la tabla tenk1. El escaneo realiza 3 búsquedas que leen cada una una sola página de hoja del índice: “four = 1 AND unique1 = 42”, “four = 2 AND unique1 = 42” y “four = 3 AND unique1 = 42”. Este índice suele ser un buen candidato para skip scan, ya que, como se explica en la Section 11.3, su primera columna (la columna four) contiene solo 4 valores distintos, mientras que su segunda y última columna (la columna unique1) contiene muchos valores distintos.

Otro tipo de información adicional es el número de filas eliminadas por una condición de filtro:

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;

                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000.00 loops=1)
   Filter: (ten < 7)
   Rows Removed by Filter: 3000
   Buffers: shared hit=345
 Planning Time: 0.102 ms
 Execution Time: 2.145 ms

Estos recuentos pueden ser especialmente valiosos para las condiciones de filtro aplicadas en los nodos de unión. La línea “Rows Removed” solo aparece cuando al menos una fila escaneada (o un par de unión potencial en el caso de un nodo de unión) es rechazada por la condición del filtro.

Un caso similar a las condiciones de filtro ocurre con los escaneos de índices “lossy” (con pérdidas). Por ejemplo, considera esta búsqueda de polígonos que contienen un punto específico:

EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                               QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on polygon_tbl  (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0.00 loops=1)
   Filter: (f1 @> '((0.5,2))'::polygon)
   Rows Removed by Filter: 7
   Buffers: shared hit=1
 Planning Time: 0.039 ms
 Execution Time: 0.033 ms

El planificador estima (con toda razón) que esta tabla de muestra es demasiado pequeña como para justificar un escaneo de índice, por lo que tenemos un escaneo secuencial plano en el cual todas las filas fueron rechazadas por la condición del filtro. Pero si forzamos el uso de un escaneo de índice, vemos:

SET enable_seqscan TO off;

EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using gpolygonind on polygon_tbl  (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0.00 loops=1)
   Index Cond: (f1 @> '((0.5,2))'::polygon)
   Rows Removed by Index Recheck: 1
   Index Searches: 1
   Buffers: shared hit=1
 Planning Time: 0.039 ms
 Execution Time: 0.098 ms

Aquí podemos ver que el índice devolvió una fila candidata, la cual fue rechazada por una re-verificación de la condición del índice. Esto sucede porque un índice GiST es “lossy” (con pérdidas) para las pruebas de contención de polígonos: en realidad devuelve las filas con polígonos que se superponen al objetivo, y luego tenemos que realizar la prueba de contención exacta en esas filas.

EXPLAIN tiene una opción BUFFERS que proporciona detalles adicionales sobre las operaciones de E/S realizadas durante la planificación y ejecución de la consulta dada. Los números de búfer mostrados indican la cantidad de búferes no distintos accedidos (hit), leídos (read), modificados (dirtied) y escritos (written) para el nodo dado y todos sus nodos hijos. La opción ANALYZE habilita implícitamente la opción BUFFERS. Si esto no se desea, BUFFERS se puede desactivar explícitamente:

EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                                            QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10.00 loops=1)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   Heap Blocks: exact=10
   ->  BitmapAnd  (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0.00 loops=1)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100.00 loops=1)
               Index Cond: (unique1 < 100)
               Index Searches: 1
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999.00 loops=1)
               Index Cond: (unique2 > 9000)
               Index Searches: 1
 Planning Time: 0.162 ms
 Execution Time: 0.143 ms

Ten en cuenta que debido a que EXPLAIN ANALYZE ejecuta realmente la consulta, cualquier efecto secundario ocurrirá como de costumbre, a pesar de que los resultados que la consulta pueda devolver se descartan en favor de la impresión de los datos de EXPLAIN. Si deseas analizar una consulta que modifica datos sin cambiar tus tablas, puedes revertir (rollback) el comando después, por ejemplo:

BEGIN;

EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;

                                                            QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Update on tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1)
   ->  Bitmap Heap Scan on tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100.00 loops=1)
         Recheck Cond: (unique1 < 100)
         Heap Blocks: exact=90
         Buffers: shared hit=4 read=2
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1)
               Index Cond: (unique1 < 100)
               Index Searches: 1
               Buffers: shared read=2
 Planning Time: 0.151 ms
 Execution Time: 1.856 ms

ROLLBACK;

Como se ve en este ejemplo, cuando la consulta es un comando INSERT, UPDATE, DELETE o MERGE, el trabajo real de aplicar los cambios en la tabla se realiza mediante un nodo de plan superior de tipo Insert, Update, Delete o Merge. Los nodos de plan debajo de este realizan el trabajo de localizar las filas antiguas y/o calcular los nuevos datos. Así que arriba, vemos el mismo tipo de escaneo de tabla por mapa de bits que ya hemos visto, y su salida se pasa a un nodo Update que almacena las filas actualizadas. Vale la pena señalar que aunque el nodo que modifica datos puede requerir una cantidad considerable de tiempo de ejecución (aquí consume la mayor parte del tiempo), el planificador actualmente no añade nada a las estimaciones de costes para reflejar ese trabajo. Esto se debe a que el trabajo que se debe realizar es el mismo para cada plan de consulta correcto, por lo que no afecta a las decisiones de planificación.

Cuando un comando UPDATE, DELETE o MERGE afecta a una tabla particionada o a una jerarquía de herencia, la salida podría verse así:

EXPLAIN UPDATE gtest_parent SET f1 = CURRENT_DATE WHERE f2 = 101;

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Update on gtest_parent  (cost=0.00..3.06 rows=0 width=0)
   Update on gtest_child gtest_parent_1
   Update on gtest_child2 gtest_parent_2
   Update on gtest_child3 gtest_parent_3
   ->  Append  (cost=0.00..3.06 rows=3 width=14)
         ->  Seq Scan on gtest_child gtest_parent_1  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)
         ->  Seq Scan on gtest_child2 gtest_parent_2  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)
         ->  Seq Scan on gtest_child3 gtest_parent_3  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)

En este ejemplo, el nodo Update necesita considerar tres tablas hijas, pero no la tabla particionada mencionada originalmente (ya que esta nunca almacena datos). Así que hay tres subplanes de escaneo de entrada, uno por tabla. Para mayor claridad, el nodo Update está anotado para mostrar las tablas destino específicas que se actualizarán, en el mismo orden que los subplanes correspondientes.

El Planning time (tiempo de planificación) mostrado por EXPLAIN ANALYZE es el tiempo que se tardó en generar el plan de consulta a partir de la consulta analizada sintácticamente y optimizarla. No incluye el análisis sintáctico ni la reescritura.

El Execution time (tiempo de ejecución) mostrado por EXPLAIN ANALYZE incluye el tiempo de inicio y finalización del ejecutor, así como el tiempo para ejecutar cualquier disparador (trigger) que se active, pero no incluye el tiempo de análisis sintáctico, reescritura o planificación. El tiempo dedicado a ejecutar los disparadores BEFORE, si los hay, se incluye en el tiempo del nodo Insert, Update o Delete relacionado; pero el tiempo dedicado a ejecutar los disparadores AFTER no se cuenta allí porque los disparadores AFTER se activan después de completarse todo el plan. El tiempo total transcurrido en cada disparador (ya sea BEFORE o AFTER) también se muestra por separado. Ten en cuenta que los disparadores de restricciones diferidos no se ejecutarán hasta el final de la transacción y, por lo tanto, EXPLAIN ANALYZE no los considera en absoluto.

El tiempo mostrado para el nodo de nivel superior no incluye el tiempo necesario para convertir los datos de salida de la consulta a un formato visualizable ni para enviarlos al cliente. Aunque EXPLAIN ANALYZE nunca enviará los datos al cliente, se le puede indicar que convierta los datos de salida de la consulta a un formato visualizable y que mida el tiempo necesario para ello, especificando la opción SERIALIZE. Ese tiempo se mostrará por separado y también se incluye en el Execution time total.

Hay dos formas significativas en las que los tiempos de ejecución medidos por EXPLAIN ANALYZE pueden desviarse de la ejecución normal de la misma consulta. Primero, dado que no se entregan filas de salida al cliente, no se incluyen los costes de transmisión de red. Los costes de conversión de E/S tampoco se incluyen a menos que se especifique SERIALIZE. Segundo, la sobrecarga de medición añadida por EXPLAIN ANALYZE puede ser significativa, especialmente en máquinas con llamadas al sistema operativo gettimeofday() lentas. Puedes utilizar la herramienta pg_test_timing para medir la sobrecarga de temporización en tu sistema.

Los resultados de EXPLAIN no deben extrapolarse a situaciones muy diferentes de la que realmente estás probando; por ejemplo, no se puede asumir que los resultados en una tabla de tamaño minúsculo se apliquen a tablas grandes. Las estimaciones de costes del planificador no son lineales y, por lo tanto, podría elegir un plan diferente para una tabla más grande o más pequeña. Un ejemplo extremo es que en una tabla que solo ocupa una página de disco, casi siempre obtendrás un plan de escaneo secuencial, independientemente de si hay índices disponibles o no. El planificador se da cuenta de que va a requerir la lectura de una página de disco para procesar la tabla en cualquier caso, por lo que no tiene sentido realizar lecturas de páginas adicionales para consultar un índice. (Vimos que esto ocurría en el ejemplo de polygon_tbl anterior).

Hay casos en los que los valores reales y los estimados no coincidirán bien, pero no ocurre nada realmente malo. Uno de estos casos ocurre cuando la ejecución del nodo de plan se detiene antes de tiempo debido a un LIMIT o efecto similar. Por ejemplo, en la consulta LIMIT que usamos antes,

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2.00 loops=1)
   Buffers: shared hit=16
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2.00 loops=1)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)
         Rows Removed by Filter: 287
         Index Searches: 1
         Buffers: shared hit=16
 Planning Time: 0.077 ms
 Execution Time: 0.086 ms

el coste estimado y el número de filas para el nodo Index Scan se muestran como si se ejecutara hasta su finalización. Pero en realidad, el nodo Limit dejó de solicitar filas después de obtener dos, por lo que el recuento de filas real es solo 2 y el tiempo de ejecución es menor de lo que sugeriría la estimación de costes. Esto no es un error de estimación, sino solo una discrepancia en la forma en que se muestran las estimaciones y los valores reales.

Las uniones por fusión (merge joins) también presentan artefactos de medición que pueden confundir a los incautos. Una unión por fusión dejará de leer una entrada si ha agotado la otra entrada y el siguiente valor de clave en la primera entrada es mayor que el último valor de clave de la otra entrada; en tal caso no puede haber más coincidencias y, por lo tanto, no es necesario escanear el resto de la primera entrada. Esto da como resultado que no se lea todo un hijo, con resultados como los personalizados para LIMIT. Además, si el hijo externo (primero) contiene filas con valores de clave duplicados, el hijo interno (segundo) se retrocede y se vuelve a escanear para la porción de sus filas que coinciden con ese valor de clave. EXPLAIN ANALYZE cuenta estas emisiones repetidas de las mismas inner rows como si fueran filas adicionales reales. Cuando hay muchos duplicados externos, el número de filas reales reportado para el nodo de plan del hijo interno puede ser significativamente mayor que el número de filas que hay realmente en la relación interna.

Los nodos BitmapAnd y BitmapOr siempre reportan sus recuentos de filas reales como cero, debido a limitaciones de la implementación.

Normally, EXPLAIN will display every plan node created by the planner. However, there are cases where the executor can determine that certain nodes need not be executed because they cannot produce any rows, based on parameter values that were not available at planning time. (Currently this can only happen for child nodes of an Append or MergeAppend node that is scanning a partitioned table.) When this happens, those plan nodes are omitted from the EXPLAIN output and a Subplans Removed: N annotation appears instead.