Evitando que se use un índice en una consulta

Deja un comentario

El optimizador de consultas de Firebird es muy bueno y en casi la totalidad de los casos elegirá la mejor alternativa: cual de los índices usar o no usar un índice.

Sin embargo no es perfecto y alguna vez podrías descubrir que está usando un índice cuando no debería hacerlo. Esto en general ocurre cuando el índice tiene poca selectivdad (es decir: muchos valores repetidos).

Puedes saber cual índice se usa en una consulta mirando el PLAN de la misma.

Si descubres que usando un índice obtienes los resultados a una cierta velocidad (por ejemplo: 1500 ms) y sin usarlo obtienes los resultados a una velocidad mayor (por ejemplo: 100 ms) lo que debes hacer es pedirle al Firebird que no use ese índice. Eso lo consigues agregándole a tu SELECT la cláusula PLAN.

Ejemplo:

Queremos obtener los nombres de los proveedores que empiezan con “H”. La tabla de PROVEEDORES tiene un índice según las columnas (PRO_CODSUC y PRO_NOMBRE).

En PRO_CODSUC se guarda el código de la Sucursal.

En PRO_NOMBRE se guarda el nombre del Proveedor.

SELECT
   PRO_NOMBRE
FROM
   PROVEEDORES
WHERE
   PRO_CODSUC = 0 AND
   PRO_NOMBRE LIKE 'H%'
ORDER BY
   PRO_CODSUC,
   PRO_NOMBRE

Verificamos el tiempo que tardó el SELECT en extraer los datos de la tabla de PROVEEDORES y encontramos que es de 1500 ms y que está usando el índice, así que ahora le pedimos que no use el índice:

SELECT
   PRO_NOMBRE
FROM
   PROVEEDORES
WHERE
   PRO_CODSUC = 0 AND
   PRO_NOMBRE LIKE 'H%'
PLAN
   (PROVEEDORES NATURAL)
ORDER BY
   PRO_CODSUC,
   PRO_NOMBRE

y encontramos que el tiempo disminuyó a solamente 100 ms.

Conclusión:

Aunque casi siempre el Firebird hace un muy buen trabajo eligiendo el índice más adecuado para una consulta no debes asumir que siempre será así, a veces puede equivocarse. Para descubrirlo siempre tienes que observar el tiempo de extracción de los datos y si es mayor que 500 ms entonces es casi seguro que el PLAN puede mejorarse. Un buen profesional siempre verifica los tiempos de extracción de los datos y los optimiza al máximo. Esto es de suma importancia cuando las consultas son ejecutadas frecuentemente y las tablas involucradas tienen muchas filas.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

El índice 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

Optimizando las consultas

2 comentarios

Este artículo está basado en el documento “Planos de Optimizaçao do Firebird” de Gladiston Santana.

Seguramente ya has leído o escuchado que el Firebird es rapidísimo para devolver el resultado de las consultas. ¿A qué se debe esa gran velocidad?

Quienes están acostumbrados a usar el modelo desktop (tablas .DBF, Paradox, Access) saben que el rendimiento está directamente ligado al rendimiento del Servidor de archivos, rendimiento de la red, tamaño de las tablas y el uso de índices correctos. Todo eso también es cierto en Firebird pero éste dispone de algo más, muy poderoso: el PLAN de optimización (“PLAN optimizer” en inglés).

¿Qué es el PLAN de optimización?

Es la lista del índice (o ningún índice) de cada tabla involucrada en una consulta que el Firebird utilizará para devolver el resultado de esa consulta (o sea, de ese SELECT)

La intención al usar un PLAN es que los resultados sean devueltos lo más rápidamente posible. Todas las consultas, absolutamente todas, tienen un PLAN de optimización, el cual puede ser puesto:

  • Automáticamente, por el Firebird
  • Manualmente, por el programador

¿Cómo el Firebird determina el PLAN?

Cuando el programador no le dice cual PLAN usar, el Firebird crea su propio PLAN usando para ello un módulo llamado “Query optimizer”, en castellano: optimizador de la consulta.

La tarea de este optimizador es analizar la consulta, evaluando: índices, combinaciones de índices, agrupamientos como sort, union, (inner, left, outer) join, y muchas cosas más y después de haber analizado todo eso evaluar el costo de esa consulta.

Este “costo” es una nota, una calificación, que indica si la optimización de la consulta es ventajosa o desventajosa. Si el optimizador encuentra que la nota es desventajosa entonces puede realizar una optimización distinta en la consulta o decidir no usar optimización. A este último caso se le llama NATURAL PLAN (o sea, un PLAN que no usa índices).

¿En qué casos se usaría el NATURAL PLAN?

Podría parecer extraña la idea de no utilizar optimización pero en algunos casos la mejor alternativa es no usar optimización ya que si se debe re-evaluar una operación o seleccionar un índice, eso a veces toma más tiempo que recorrer la tabla entera. Y en este caso no vale la pena usar el optimizador.

Este es el caso de tablas pequeñas o de SELECTs que no precisan de índices para realizar una búsqueda. También puede ocurrir que una tabla tenga una columna indexada pero que el optimizador elija no utilizar ese índice.

Ejemplo 1:

Tenemos una tabla llamada PERSONAS, la cual tiene una columna llamada PER_APELLD (apellidos de las personas) y un índice llamado IDX_PERSONAS sobre esa columna:

CREATE INDEX IDX_PERSONAS ON PERSONAS(PER_APELLD);

Ahora escribimos esta consulta, porque deseamos obtener los datos de todas las personas cuyo apellido sea ‘TORRES’ o cuyo apellido sea ‘CABRAL’:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE '%TORRES%CABRAL%'

El Firebird usará el índice IDX_PERSONAS, ¿verdad?. No, falso. No usará el índice IDX_PERSONAS como podemos ver al revisar el PLAN utilizado.

PLAN1

(haciendo clic en la imagen la verás más grande)

¿Por qué no usó el índice? Porque hay un “%” en el inicio del LIKE y eso implica que debe recorrer la tabla completa para mostrar el resultado de la consulta. Y no tiene sentido usar un índice en ese caso, ya que tomará más tiempo y no se obtendrá algún beneficio.

Inclusive, llamar al optimizador ya sería un desperdicio porque ningún índice ayudaría a acelerar esta consulta, entonces ni siquiera llamará al optimizador.

Para este caso de consultas que no pueden ser optimizadas es que existe el PLAN NATURAL.

Ejemplo 2:

Usando la misma tabla del ejemplo 1 escribimos esta consulta:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE 'TORRES%CABRAL%'

Fíjate que se borró el “%” que estaba al principio del LIKE. Volvemos a revisar cual es el PLAN utilizado y nos muestra:

PLAN2

(haciendo clic en la imagen la verás más grande)

 Y ahora sí está usando el índice porque es adecuado usarlo.

El SELECT extendido

Los SELECTs que escribimos en los dos ejemplos anteriores no son los que realmente utilizará el Firebird cuando los ejecutemos. ¿Por qué no? porque el Firebird automáticamente le agrega la cláusula PLAN a todos los SELECTs que no tengan dicha cláusula escrita.

Así, para el primer ejemplo, el SELECT que usará el Firebird es:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE '%TORRES%CABRAL%'
PLAN
   (PERSONAS NATURAL)

Fíjate que le agrega las palabras PLAN y luego el PLAN utilizado (en este caso PERSONAS NATURAL). PERSONAS es el nombre de la tabla y NATURAL le indica que no debe usar un índice.

Para el segundo ejemplo el SELECT que usará el Firebird es:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE 'TORRES%CABRAL%'
PLAN
   (PERSONAS INDEX (IDX_PERSONAS))

Fíjate que ahora sí usa el índice IDX_PERSONAS

Preparando un PLAN

El proceso de someter una consulta al optimizador para que éste la analice y determine el mejor PLAN se llama “Prepare query” (preparar la consulta). Los programadores experimentados siempre escriben un PLAN en el SELECT porque eso ahorra tiempo y mejora el rendimiento ya que el Firebird usa el PLAN especificado y no debe llamar al optimizador para determinar cual PLAN utilizará.

En EMS SQL Manager, haríamos clic en la opción “Prepare query” para preparar la consulta  y en “Explain query” para ver cual será el PLAN que utilizará el Firebird, como se ve a continuación:

PLAN3

(haciendo clic en la imagen la verás más grande)

 Entonces, como ya se le indica cual PLAN debe usar el Firebird no pierde tiempo analizando la consulta y determinando ese PLAN.

Advertencia

Si al escribir un SELECT especificamos el PLAN a utilizar y ese PLAN es el más adecuado entonces el SELECT se ejecutará más rápido, sin embargo hay que tener en cuenta algo muy importante: a veces, el PLAN que hoy es excelente puede dejar de serlo conforme las condiciones de la Base de Datos van cambiando, por ejemplo si se le van agregando nuevos índices, fórmulas, e inclusive el mismo aumento en la cantidad de datos. Esto implica que periódicamente debemos verificar el rendimiento de nuestros SELECTs para detectar aquellos planes que ya no son los más adecuados.

Un PLAN inadecuado no es solamente malo para esa consulta en particular, es malo para toda la Base de Datos porque consultas concurrentes podrían sumarse al problema y tornar al Servidor en una verdadera tortuga de tres patas.

Tiempos de ejecución

Veamos ahora cuanto tarda ejecutar cada uno de los SELECTs de los ejemplos anteriores:

PLAN4

(haciendo clic en la imagen la verás más grande)

 La tabla PERSONAS tiene 3.516.272 registros y ejecutar el primer SELECT (el que tiene PLAN NATURAL y por lo tanto no usa índices) tardó 14,093 segundos.

PLAN5

(haciendo clic en la imagen la verás más grande)

 Al ejecutar el segundo SELECT (que usa al índice IDX_PERSONAS) el tiempo se redujo considerablemente, ahora es de solamente 1,282 segundos. ¿Y qué ocurrirá si quitamos el apellido CABRAL de la consulta y dejamos solamente al apellido TORRES? ¿El tiempo mejorará o empeorará? veamos:

PLAN6

(haciendo clic en la imagen la verás más grande)

 Esto puede parecer muy extraño, ya que ahora tenemos muchos más datos (ya que hay más apellidos ‘TORRES’ que ‘TORRES CABRAL’) sin embargo el tiempo de la consulta disminuyó.

¿Por qué?

Porque el Firebird recupera los datos por páginas y en una página generalmente caben muchos registros. Esos registros ya están en la memoria y por lo tanto mostrarlos es muy rápido.

Veamos ahora lo que sucede al volver a ejecutar el último SELECT

PLAN7

(haciendo clic en la imagen la verás más grande)

 El tiempo se redujo aún más, ahora ni siquiera tardó 1 segundo en finalizar.

¿Por qué?

Porque esa consulta ya estaba en la memoria, en el caché del Firebird, y por lo tanto no tuvo que ser leída desde el disco duro, con lo cual se aceleró aún más obtener los resultados pedidos.

Lo bueno de esto es que si el mismo usuario ejecuta el mismo SELECT o si algún otro usuario lo hace, como los datos ya están en la memoria mostrarlos es súper rápido.

Estos tiempos de ejecución fueron obtenidos con una tabla que tiene 3.516.272 registros y una computadora que ya tiene varios años: Pentium IV con 4 Gbytes de RAM y de un solo núcleo. En cualquier computadora nueva los tiempos serán mucho menores.

En Firebird si multiplicamos por 10 la cantidad de registros de una tabla no aumenta por 10 el tiempo de consulta. Si la tabla PERSONAS tuviera 35.162.720 registros el tiempo de ejecución de la consulta aún sería de alrededor de 1 segundo.

En cambio, en las tablas desktop (.DBF, Paradox, Access) si multiplicamos por 10 la cantidad de registros el tiempo de consulta se multiplica por 10, por 12, por 15.

Conclusión:

Entender lo que es el PLAN y aprender a usar el PLAN correcto hará que tus consultas sean rapidísimas. Sin embargo debes recordar que a veces el PLAN va decayendo en su rendimiento cuando le vas agregando índices o fórmulas a las tablas o mismo por el aumento de los datos así que es buena práctica revisar los planes periódicamente para asegurar que siguen siendo los más adecuados.

La gran diferencia en el rendimiento que se observa cuando se usa Firebird en comparación con tablas .DBF, Paradox y Access es que Firebird, al igual que Oracle, Sybase, MSSQL, usa un PLAN. Pero mientras que por usar esos SGBD debes pagar mucho dinero, por usar Firebird no pagas, es gratis.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

El índice del blog Firebird21