Entendiendo el contenido de un PLAN

6 comentarios

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

Usando índices en Firebird

7 comentarios

Como seguramente ya sabes, los índices sirven para dos cosas:

  1. Buscar datos
  2. Mostrar las consultas ordenadas

Para poder realizar efectivamente ambas tareas los índices deben encontrarse en buen estado y es tu responsabilidad asegurarte de que eso sea así. El mantenimiento de los índices de Firebird debe contemplar dos puntos:

  1. Que el índice esté bien balanceado
  2. Que las estadísticas de los índices sean las correctas

Balanceando los índices:

Si un índice no está bien balanceado entonces las operaciones INSERT, UPDATE, DELETE, FECTCH y SELECT de esa tabla tomarán más tiempo del debido, haciendo lentas operaciones que podrían ejecutarse más rápidamente. Para balancear un índice debes escribir:

ALTER INDEX MiIndice INACTIVE

ALTER INDEX MiIndice ACTIVE

Este ciclo de inactivar/activar un índice tiene como efecto volver a crearlo. Es el equivalente al comando REINDEX de los lenguajes xBase (dBase, Clipper, FoxPro, etc.). Es conveniente ejecutar esos comandos después de haber realizado muchas operaciones en la tabla (por ejemplo: 20.000 ó más) para tener la seguridad de que el índice se encuentra bien balanceado.

En este artículo encontrarás más información:

https://firebird21.wordpress.com/2013/03/03/recreando-los-indices-de-las-tablas/

Recalculando las estadísticas:

El optimizador de consultas del Firebird revisa la estadística de un índice para decidir si lo utilizará en el PLAN de una consulta o no. Si la estadística de un índice no está actualizada entonces podría dejar de utilizar un índice que sí debería haber utilizado y el resultado será que tendrás una consulta mucho más lenta de lo que debería haber sido si el optimizador usaba el índice.

Es por ese motivo que siempre debemos asegurarnos de tener las estadísticas actualizadas, para que la decisión del optimizador sea la mejor posible. Como el Firebird calcula la estadística solamente cuando crea el índice y después de un ciclo backup/restore, es tu responsabilidad recalcularla periódicamente porque todas las operaciones de INSERT, UPDATE, DELETE que realices en esa tabla afectarán a esa selectividad.

Para recalcular la estadística de un índice debes escribir:

SET STATISTICS INDEX MiIndice

Puedes encontrar más información en este artículo:

https://firebird21.wordpress.com/2013/03/09/selectividad-de-los-indices/

¿Cuáles columnas indexar?

Puede ser tentador pensar que si se indexan todas las columnas de una tabla se conseguirá mucha velocidad en todas las consultas a esa tabla. Eso generalmente es falso. Indexar una columna que tiene pocos valores distintos es un error porque lo más probable es que el optimizador de consultas no utilice ese índice, y todas las operaciones de inserción, actualización, borrado que involucren a esa columna modificarán al índice y eso lleva tiempo entonces ¿para qué indexar esa columna si su índice nunca será usado y actualizarlo hará las operaciones más lentas?

La decisión de indexar o no una columna debe estar basada en si se obtiene una ganancia de velocidad considerable utilizándolo. Si no se obtiene una ganancia de velocidad o si la ganancia de velocidad obtenida es muy pequeña, lo correcto es no indexar esa columna para no gastar recursos en ella.

No tiene sentido indexar una columna si no se realizan búsquedas utilizando esa columna ni se ordenan las consultas según esa columna ni se obtiene una buena ganancia de tiempo.

Tener los índices correctos en cada tabla, y solamente los índices correctos, es una decisión de diseño que debe estar bien estudiada para evitar indexar una columna que no debería estar indexada o dejar de indexar una columna que sí debería estarlo.

Para complicar las cosas, un índice que ahora es bueno dentro de unos meses podría dejar de serlo. Y viceversa. Porque al aumentar grandemente la cantidad de datos en la tabla la selectividad varía y por lo tanto la bondad de indexar o no esa columna. Es por lo tanto necesario que al menos en las tablas más grandes de nuestra Base de Datos periódicamente verifiquemos los índices, su selectividad, y si son o no usados en los PLANES de los SELECTS. Un índice que nunca es utilizado en una búsqueda o en el ordenamiento de una consulta no tiene razón de ser, está de más, está sobrando, es inútil, solamente gasta recursos.

Tampoco es correcto indexar una columna y que su índice se use poquísimo, quizás una o dos veces al mes en una consulta para ganar 2 ó 3 segundos.

Conclusión:

Tener siempre los índices bien balanceados y con sus estadísticas actualizadas es lo que debes preocuparte en conseguir para que todas las operaciones en tus tablas (INSERT, UPDATE, DELETE, FETCH, SELECT) sean lo más rápidas posibles. Un índice en mal estado de conservación perjudica más de lo que ayuda. Por ello es conveniente que tengas un stored procedure que se encargue de esas tareas, el cual podrá ser llamado desde tu aplicación (el programa que hiciste en Visual FoxPro, Delphi, C, C++, etc.)

En muchos lenguajes de programación es el propio programador quien decide si utilizará un índice o no y en caso afirmativo cual índice utilizará, pero en el caso de Firebird es el optimizador de consultas quien toma esa decisión (si no se le ha dicho lo contrario) y basa esa decisión en la selectividad de los índices. ¿Quiéres que tome la decisión más correcta? entonces la selectividad de tus índices debe estar actualizada.

Artículos relacionados:

Recreando los índices de las tablas

Selectividad de los índices

Usando un PLAN

Usando índices correctos para aumentar la velocidad de las consultas

Optimizando las consultas

El índice del blog Firebird21