En Firebird todos los SELECTs que ejecutemos (manualmente o dentro de una vista) sí o sí usarán un PLAN. Ese PLAN puede ser elegido por el Firebird o por nosotros mismos. En general el Firebird hace un excelente trabajo en hallar el PLAN más adecuado pero en ocasiones no es así y allí nosotros podemos especificar uno mejor.

NOTA: Estos conocimientos no son necesarios para quienes son principiantes en Firebird, tales personas lo mejor que pueden hacer es dejarle al Firebird que especifique el PLAN a ser usado. Pero para quienes tienen un nivel más avanzado sí es importante, porque en algunos casos podrán mejorar drásticamente la velocidad de sus SELECTs.

Para entender mejor lo que es un PLAN puedes leer estos artículos:

Usando un PLAN

Algo más sobre PLAN

 En un PLAN lo que se especifica son los índices que serán usados. Las posibilidades son:

  • No hay un índice y no se usa un índice. Verás la palabra NATURAL.
  • No hay un índice y se necesita usar un índice. Verás la palabra SORT.
  • Hay un índice y no se usa ese índice. Verás la palabra NATURAL.
  • Hay un índice y se usa ese índice. Verás la palabra INDEX.

Si el SELECT tiene un JOIN (o más de uno, claro) entonces las posibilidades son:

  • No se necesita ordenar los conjuntos de resultados usados. Verás la palabra JOIN
  • Se necesita ordenar los conjuntos de resultados usados. Verás la palabra MERGE o más comúnmente las palabras SORT MERGE

Si se quiere mostrar las filas ordenadas:

  • Si hay un índice verás la palabra ORDER
  • Si no hay un índice verás la palabra SORT

El significado de NATURAL

Si ves esta palabra en el PLAN significa que no existe un índice o que sí existe pero no se lo utiliza. Si no se lo utiliza en general es por la pobre selectividad del mismo. Para saber más sobre la selectividad puedes leer estos artículos:

Selectividad de los índices

Recreando índices y calculando estadísticas

NATURAL significa que las filas son extraídas con el mismo orden en el cual fueron guardadas. Esto implica que todas las páginas deben ser leídas antes de que pueda ejecutarse el filtro (la condición que hayas puesto en la cláusula WHERE). Eso puede ser muy lento en tablas grandes.

Sin embargo, NATURAL no siempre es malo, si no te interesa mostrar a las filas con algún orden en particular entonces NATURAL es lo correcto porque es más rápido que usar un índice. Si se usa un índice primero se busca la clave en el índice y luego se extrae la fila correspondiente, si se usa NATURAL se ahorra el primer paso y por lo tanto es más rápido; la diferencia es muy notoria en tablas muy grandes.

El significado de SORT

La palabra SORT significa “ordenar” en inglés y eso es lo que hace el Firebird. Ordena el conjunto de resultados creando archivos temporales. Este proceso puede ser muy lento en tablas grandes porque crear esos archivos temporales puede demorar mucho tiempo. Si encuentras la palabra SORT eso es muy malo para el SELECT. Pero cuidado con esto, que sea malo para el SELECT no implica que necesariamente será malo para la Base de Datos. ¿Por qué? porque para evitar el SORT tendríamos que crear un índice, pero el mantenimiento de los índices es costoso, ya que cada vez que se hace un INSERT, un UPDATE, o un DELETE el índice debe ser modificado, eso toma tiempo y en tablas que constantemente están cambiando le provocarán un sobrecosto sustancial. Por lo tanto, la regla es crear un índice solamente si se lo usará frecuentemente. Por ejemplo, si tu SELECT se ejecutará una sola vez al año (o muy pocas veces en el año) y las filas involucradas se cuentan por millones entonces probablemente lo mejor sea no tener un índice y que el Firebird haga el SORT cuando lo necesite. Es cierto que el SELECT será lento, pero los INSERT, UPDATE, y DELETE serán más rápidos y como las filas se cuentan por millones entonces valdrá la pena no crear un índice.

El significado de INDEX

Esta palabra significa que existe un índice y que se lo utiliza.

El significado de JOIN

La palabra JOIN significa “juntar” en inglés. Si la ves en un PLAN significa que no se requiere ordenar ambos conjuntos de resultados porque ya se encuentran en el orden correcto.

El significado de MERGE o SORT MERGE

La palabra MERGE significa “combinar” o “mezclar” en inglés. MERGE y SORT MERGE hacen exactamente lo mismo, pero se suele escribir SORT MERGE para que quede más claro lo que hacen.

Lo que hacen es lo siguiente: un SORT al primer conjunto de resultados (como vimos más arriba), un SORT al segundo conjunto de resultados (como vimos más arriba) y luego combinar ambos conjuntos de resultados, ya ordenados.

Fíjate que hay dos SORT y una combinación de resultados. Cuidado con eso, algo así puede ser lentísimo en tablas grandes.

Un SORT MERGE es lo peor que puedes tener en un PLAN.

El significado de ORDER

La palabra ORDER significa “ordenar” en inglés, aunque en este caso la podríamos traducir “ordenar por el índice” y a continuación el nombre del índice que usa.

Analizando el PLAN

Para que todo esto quede más claro a continuación veremos varios ejemplos de SELECT con sus respectivos planes de ejecución; y explicaremos cada uno de ellos.

Ejemplo 1:

SELECT
   *
FROM
   BANCOS

PLAN (BANCOS NATURAL)

¿Qué significa este PLAN? que como no le dijimos en cual orden queremos ver a los Bancos entonces los mostró en el mismo orden conque los guardó en la tabla. Eso está muy bien, es lo correcto, porque es lo más rápido.

Ejemplo 2:

SELECT
   *
FROM
   BANCOS
ORDER BY
   BAN_CODSUC,
   BAN_IDENTI

PLAN (BANCOS ORDER PK_BANCOS)

¿Qué significa este PLAN? que como le dijimos que queremos ver a las filas ordenadas por Código de la Sucursal y por Identificador y tenemos un índice con esas columnas entonces usó a ese índice, cuyo nombre es PK_BANCOS.

Esto es más lento que el Ejemplo 1., pero si es lo que necesitamos mostrar entonces está ok.

Ejemplo 3:

SELECT
   *
FROM
   BANCOS
ORDER BY
   BAN_NOMBRE

PLAN SORT ((BANCOS NATURAL))

¿Qué significa este PLAN? que la tabla de BANCOS no estaba ordenada por la columna BAN_NOMBRE (por eso la palabra NATURAL) y que el Firebird tuvo que ordenarla (por eso la palabra SORT) antes de mostrar las filas. Ese ordenamiento crea archivos temporarios (en la memoria caché o en el disco duro) y puede ser muy lento en tablas muy grandes.

Ejemplo 4:

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_CODSUC = 0 AND
   BAN_IDENTI > 10

PLAN (BANCOS INDEX (PK_BANCOS))

¿Qué significa este PLAN? que la tabla BANCOS tiene un índice según las columnas BAN_CODSUC y BAN_IDENTI (que son las usadas para filtrar con la cláusula WHERE) y que el Firebird usó ese índice.

En los WHERE hay siempre que tratar de usar índices porque eso aumenta muchísimo la velocidad.

Ejemplo 5:

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_NOMBRE LIKE 'BAN%'

PLAN (BANCOS NATURAL)

¿Qué significa este PLAN? que la tabla BANCOS no tiene un índice según la columna BAN_NOMBRE y que como no le pedimos que muestre las filas con algún orden en particular las mostró como fueron guardadas.

Ejemplo 6:

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_NOMBRE LIKE 'BAN%'
ORDER BY
   BAN_NOMBRE

PLAN SORT ((BANCOS NATURAL))

¿Qué significa este PLAN? que la tabla BANCOS no tiene un orden según la columna BAN_NOMBRE pero como se necesita mostrar a las filas ordenadas según esa columna entonces se hizo un SORT (es decir, se creó un archivo temporal para poder mostrar a las filas ordenadas).

No te olvides que hacer un SORT es más lento que usar un índice, a veces muchísimo más lento, porque las filas deben ser ordenadas y guardadas en un archivo temporal antes de ser mostradas y eso toma su tiempo.

Ejemplo 7:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO

PLAN JOIN (B NATURAL, S INDEX (UQ_SUCURSALES))

¿Qué significa este PLAN? que la tabla S tiene un índice llamado UQ_SUCURSALES según su columna SUC_CODIGO, la tabla BANCOS no tiene un índice sobre su columna BAN_CODSUC y que se hará el JOIN sin ordenar las filas de la tabla B, así como están serán mostradas.

Ejemplo 8:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO
ORDER BY
   B.BAN_NOMBRE

PLAN SORT (JOIN (B NATURAL, S INDEX (UQ_SUCURSALES)))

¿Qué significa este PLAN? que la tabla S tiene un índice llamado UQ_SUCURSALES según su columna SUC_CODIGO, la tabla BANCOS no tiene un índice sobre su columna BAN_CODSUC, que se hará el JOIN de esas filas (como vimos en el Ejemplo 7.) y posteriormente se hará un SORT para mostrar a las filas ordenadas según la columna BAN_NOMBRE.

Conclusión:

Entender el contenido del PLAN es muy útil por dos cosas: a) porque sabremos lo que está haciendo el Firebird para mostrar las filas del SELECT, y b) porque podremos analizarlo y quizás descubrir una mejor alternativa. Claro que esto último lleva su buen tiempo, no es algo que conseguiremos hacer enseguida pero vale la pena el esfuerzo porque un PLAN adecuado muestra a las filas muy rápidamente.

Si en el PLAN vemos la palabra SORT eso es algo malo y si vemos las palabras SORT MERGE eso es mucho más malo. Pero son malos para el SELECT, quizás no lo sean para la Base de Datos. Eso porque para evitar los SORT hay que crear índices, pero los índices deben ser mantenidos cada vez que se realiza un INSERT, un UPDATE o un DELETE, y ese mantenimiento no es instantáneo, toma su tiempo, que en tablas muy grandes y con muchas operaciones de inserción, actualización y borrado puede ser muy grande.

Por lo tanto debemos estudiar bien el caso y decidir si creamos el índice o si dejamos que se haga el SORT. En general, si el SELECT será ejecutado muchas veces, frecuentemente, deberíamos crear el índice. En cambio, si se lo ejecutará raramente lo mejor puede ser no crear el índice.

Otro aspecto muy importante a tener en cuenta es que las tablas son dinámicas, constantemente están cambiando, entonces un PLAN que hoy puede ser perfecto podría no serlo dentro de algunos meses o años. Si vamos a escribir el PLAN manualmente debemos tener bien presente que eso implicará más trabajo para nosotros porque de vez en cuando tendremos que verificar que siga funcionando muy bien, y si no es así entonces buscar y escribir un mejor PLAN. Pero encontrar un mejor PLAN no es algo que haremos en dos segundos, puede hacernos perder muchos minutos o horas inclusive.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

Selectividad de los índices

Recreando índices y calculando estadísticas

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios