Entendiendo a GSTAT (4)

Deja un comentario

Entender como funciona el programa GSTAT es bastante largo. Ya hemos visto algo sobre él en estos artículos:

Entendiendo a GSTAT (1)

Entendiendo a GSTAT (2)

Entendiendo a GSTAT (3)

ahora, continuamos.

Opción -index

Esta es la opción que debemos elegir cuando solamente nos interesan los índices de nuestras tablas.

gstat01

Captura 1. Si haces clic en la imagen la verás más grande

Como es lo usual, la contraseña la extraemos de un archivo de texto y la salida del programa GSTAT la enviamos a otro archivo de texto. Esto último es para facilitarnos la tarea.

gstat02

Captura 2. Si haces clic en la imagen la verás más grande

En la Captura 2. vemos una parte del contenido del archivo de texto que nos muestra información sobre nuestros índices. Los nombres de los índices siempre aparecen ordenados alfabéticamente.

¿Cuál es el significado de lo que estamos viendo?

ASIENTOSCAB es el nombre de la tabla

131 es el identificador que la tabla ASIENTOSCAB tiene dentro de la tabla del sistema RDB$RELATIONS. Es en RDB$RELATIONS donde se  guardan los nombres de todas las tablas de la Base de Datos.

ASC01, ASC02, ASC03, y PK_ASIENTOSCAB son los nombres de los índices

0, 1, 2, 3, los números entre paréntesis que vemos después de los nombres de los índices son los identificadores de los índices menos 1 y nos indican el orden en el cual fueron creados, siendo 0 el primer índice que creamos, 1 el segundo índice que creamos, 2 el tercer índice que creamos y así sucesivamente. Si se creó un índice y luego se lo eliminó, el número que tenía no aparecerá en la lista.

Listado 1.

SELECT
   RDB$INDEX_ID,
   RDB$RELATION_NAME,
   RDB$INDEX_NAME
FROM
   RDB$INDICES
WHERE
   RDB$RELATION_NAME = 'ASIENTOSCAB'
ORDER BY
   RDB$INDEX_ID

gstat03

Captura 3. Si haces clic en la imagen la verás más grande

¿Por qué en la Captura 2. no muestra los verdaderos identificadores sino los identificadores menos 1? Es un verdadero misterio, quizás haya alguna razón valedera para ello pero es dudoso que exista. Probablemente sólo sea por costumbre.

Depth es la cantidad de indirecciones o desviaciones que hay en el árbol B-tree del índice. Lo ideal es que ese número sea como máximo 3. Si es mayor que 3 entonces el índice no será tan eficiente como podría ser ¿cómo lo solucionamos? aumentando el tamaño de la página de nuestra Base de Datos. Si por ejemplo el tamaño de la página es 4096 lo aumentamos a 8192 y volvemos a ejecutar a GSTAT con la opción -index para comprobar si Depth ahora es 1, 2, ó 3 (esos son los mejores valores que puede tener Depth). Si sigue siendo mayor que 3 entonces volvemos a aumentar el tamaño de la página de nuestra Base de Datos y ahora lo ponemos en 16384.

Leaf buckets es la cantidad de páginas que están en el nivel más bajo del árbol B-tree. Es en estas páginas donde se guardan los punteros a las filas de la tabla. En las demás páginas de índice se guardan los enlaces a otras páginas de índice.

Nodes es la cantidad total de filas en la tabla que han sido indexadas. Sin embargo, este número puede ser erróneo porque podrían aparecer filas que han sido borradas con DELETE y aún su basura no ha sido recolectada o también porque las columnas del índice cambiaron de valor. Por eso, es conveniente ejecutar a GSTAT con la opción -index solamente después de un sweep o de un ciclo backup/restore.

Average data length es el tamaño en bytes promedio de los datos de la columna (o columnas) que se indexaron. Como Firebird comprime esos datos antes de grabarlos en una página de índices, el average data length será siempre menor a la suma del tamaño de las columnas de la tabla.

Total dup es la cantidad total de duplicados que tiene un índice. Los índices que se utilizan en las restricciones Primary Key y Unique Key no admiten duplicados, pero los otros índices sí los admiten. Cuantos más duplicados haya, peor es el índice.

 Listado 2.

SELECT
   ASC_ANOEJE,
   ASC_CODSUC,
   ASC_NUMERO,
   COUNT(*)
FROM
   ASIENTOSCAB
GROUP BY
   ASC_ANOEJE,
   ASC_CODSUC,
   ASC_NUMERO

gstat05

Captura 4. Si haces clic en la imagen la verás más grande

gstat04

Captura 5. Si haces clic en la imagen la verás más grande

En la Captura 4. vemos que al ejecutar el Listado 2. se usó el índice ASC01, y en la Captura 5. vemos la cantidad de veces que los valores de ese índice aparecieron. En este caso no hay duplicados, ya que COUNT siempre nos muestra el número 1 pero en otros índices sí podría haber duplicados. El Listado 2. nos ayudará a conocer cuantos duplicados tiene nuestro índice. Recuerda que Total dup puede mostrarte una cantidad incorrecta de duplicados si no has hecho previamente un ciclo backup/restore.

Max dup es la cantidad máxima de valores duplicados que tiene un índice.

Fill distribution es una tabla de frecuencias y seguramente te recordarás de ellas si alguna vez estudiaste Estadísticas. Hay 5 filas, yendo de 20% en 20%, cada fila indicando la cantidad de páginas de índices que están completadas hasta ese porcentaje.

gstat02

Captura 6. Si haces clic en la imagen la verás más grande

En la Captura 6. vemos que hay 8 páginas de índices que están llenas entre un 20% y un 39%. Y hay 3 páginas de índices que están llenas entre un 40% y un 59%. La suma de 8 + 3 es 11, y siempre debe coincidir con leaf buckets.

Esta distribución es mala, estamos usando más páginas que las necesarias. ¿Cómo sabemos eso? porque no hay páginas rellenas entre un 80% y un 99%. En una buena distribución el número mayor debería estar siempre en la última fila, (es decir, en 80% a 99%). ¿Cómo conseguimos eso? Con un ciclo backup/restore y luego usando el backup restaurado.

Artículos relacionados:

Entendiendo a GSTAT (1)

Entendiendo a GSTAT (2)

Entendiendo a GSTAT (3)

El índice del blog Firebird21

El foro del blog Firebird21

Indices ascendentes y descendentes

2 comentarios

Los índices en Firebird pueden ser ascendentes (1, 2, 3, 4, 5, 6, …) o descendentes (999, 998, 997, 996, …)

Lo importante a recordar es que son siempre recorridos en el mismo orden en que fueron creados. O sea que un índice ascendente siempre se recorre de menor a mayor y un índice descendente siempre se recorre de mayor a menor.

¿Por qué es importante saber eso?

Porque tener el índice adecuado puede hacer que nuestras consultas sean rapidísimas.

Veamos un ejemplo:

Listado 1.

SELECT
   MAX (MiColumna)
FROM
   MiTabla

Aquí tenemos tres posibilidades:

  1. No hay un índice sobre la columna MiColumna
  2. Hay un índice ascendente sobre la columna MiColumna
  3. Hay un índice descendente sobre la columna MiColumna

En el caso 1. y en el caso 2. para hallar el valor máximo de MiColumna el Firebird debe recorrer la tabla completa, desde la primera fila hasta la última fila, porque no sabe cual de esas filas tendrá al mayor valor. Un índice ascendente jamás se usaría en este caso.

En cambio, en el caso 3. el valor máximo estará sí o sí en la primera fila del índice.

La diferencia en tiempo puede ser muy grande. En tablas que tienen millones de filas la diferencia será muy notoria porque el caso 1. y el caso 2. deben recorrer todas esas millones de filas y en cambio el caso 3. siempre encontrará el valor buscado en la primera fila.

¿Y si en lugar de escribir la función MAX() escribimos la función MIN()?

Pues allí se usará un índice ascendente, si es que existe.

Recuerda que crear un índice tiene sus ventajas y sus desventajas:

  • La ventaja es que si se lo utiliza en un SELECT entonces los resultados se obtendrán muy rápido
  • La desventaja es que debe ser actualizado cada vez que se realiza un INSERT, un UPDATE o un DELETE en esa tabla

Hay por lo tanto que comprobar si vale la pena crear un índice.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Usando un índice descendente

3 comentarios

Cuando creamos un nuevo índice y no especificamos si lo queremos ascendente (es decir: 1, 2, 3, 4, 5, etc.) o descendente (es decir: 10, 9, 8, 7, 6, etc.) el Firebird por defecto lo crea ascendente.

¿Por qué?

Porque para la mayoría de los casos es la mejor opción, porque es la que más frecuentemente se usa.

Cuando definimos una restricción Primary Key, Foreign Key o Unique Key el Firebird automáticamente crea un índice para esas restricciones. Y ese índice es siempre ascendente.

¿Y qué sucede si se le envía como argumento a la función MAX() la columna de la Primary Key?

SELECT
   MAX(PRD_IDENTI)     /* PRD_IDENTI es la Primary Key de la tabla PRODUCTOS */
FROM
   PRODUCTOS

Si miramos el PLAN de esta consulta veremos que no usa un índice:

INDICE1

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

¿Por qué no se usó un PLAN?

Porque el índice de la Primary Key, el índice que el Firebird automáticamente crea para esa restricción es siempre ascendente. Si queremos que la función MAX() use un índice en esta consulta entonces tenemos que crear ese índice de forma descendente.

CREATE DESC INDEX IDX_PRODUCTOS ON PRODUCTOS(PRD_IDENTI);

Si después de crear un índice descendente para la Primary Key nos fijamos cual es el PLAN que se usará en la consulta de arriba esto será lo que veremos:

INDICE2

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

 O sea que ahora sí el Firebird está usando un índice.

Conclusión:

Verifica siempre cual es el PLAN de tus consultas, a veces agregándoles un índice se puede obtener una ganancia en velocidad impresionantemente grande.

Artículos relacionados:

https://firebird21.wordpress.com/2013/04/30/usando-un-plan/

https://firebird21.wordpress.com/2013/05/03/algo-mas-sobre-plan/