Usando un PLAN

Cuando va a ejecutar un SELECT el Firebird automáticamente busca el mejor plan para que esa consulta sea lo más rápida posible. El Firebird posee lo que se denomina un «optimizador de consultas». La tarea de dicho optimizador es conseguir la consulta más rápida de todas.

¿Qué es un plan?

Es la lista de todos los índices u ordenamientos que usará una consulta. También puede darse el caso de que lo mejor sea no usar índices ni ordenar la consulta.

¿El Firebird siempre usa un plan?

Sí, siempre. Sea cual sea el SELECT que escribamos siempre tendrá un plan: el que le asignó automáticamente el Firebird o el que le especificamos manualmente nosotros.

¿Cómo se especifica un plan?

Dentro del comando SELECT existe una clásula llamada PLAN y es allí donde escribimos el plan que queremos usar

¿Por qué no usar siempre el plan que automáticamente asigna el Firebird?

Porque aunque en la gran mayoría de los casos esa es la mejor alternativa hay veces en que la elección del Firebird no es la más adecuada. Si sabemos los que es un plan y como funciona podemos darnos cuenta de que se podría mejorar el rendimiento usando un plan distinto. Afortunadamente podemos hacer eso.

¿En qué casos el Firebird puede elegir un mal plan?

En general cuando las estadísticas están desactualizadas y tenemos algún índice con baja selectividad (es decir: pocos valores distintos). El optimizador usa la selectividad en su algoritmo para decidir si debe usar un índice o no. Si la selectividad está desactualizada y no representa fielmente al índice entonces el optimizador puede usar un índice que no debería de usar o dejar de usar un índice que sí debería usar.

 Esta es la principal razón por la cual siempre debemos tener las estadísticas de nuestros índices actualizadas: para que el optimizador del Firebird utilice correctamente a los índices en cada SELECT.

¿Cómo le indico que debe recalcular la estadística de un índice para hallar su selectividad actual?

Con el comando:

SET STATISTICS NombreIndice;

¿Por qué debo pedirle que reconstruya las estadísticas?

Porque el Firebird solamente calcula las estadísticas cuando un índice es creado y después de un ciclo backup/restore. Eso implica que si no le pides que las reconstruya pueden estar desactualizadas.

¿Y cómo reconstruyo un índice?

Escribiendo:

ALTER INDEX NombreIndice INACTIVE;

ALTER INDEX NombreIndice ACTIVE;

¿Y por qué debo pedirle que reconstruya un índice?

Porque después de realizar muchas inserciones, borrados, o modificaciones que afectan a un índice el índice suele quedar desbalanceado y tú deberías asegurarte que está bien balanceado. Un índice desbalanceado es recorrido más lentamente. Escribiendo los comandos de arriba conseguirás balancearlo y por lo tanto mejorar la velocidad de consulta.

¿Cómo puedo saber cuál plan está usando el Firebird en cada consulta?

En ISQL, escribes:

SET PLAN;

Y luego, cada vez que escribas el comando SELECT lo primero que verás será el plan utilizado

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

En EMS SQL Manager, haces click en: Show SQL Editor (o presionas la tecla F12); o también puedes hacer click en: New SQL Editor (o presionas las teclas Shift + F12)

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

Verás una ventana donde podrás escribir tus comandos, luego de escribir tu SELECT en esa ventana haces click donde dice «Explain query» (explicar la consulta, en castellano)

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

Y en la parte inferior verás algo similar a esto:

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

¿Qúe significa la palabra «NATURAL» en un plan?

Que no se está usando ningún índice

¿Por qué a veces me dice que no se puede utilizar el plan especificado?

Porque en tu cláusula WHERE o en tu cláusula ORDER BY no estás teniendo todas las columnas que se usan en el índice

Ejemplo 1:

En la tabla CLIENTES tenemos una Primary Key compuesta por dos columnas: CLI_CODSUC (significa: código de la sucursal) y CLI_IDENTI (significa: identificador del cliente)

SELECT
   *
FROM
   CLIENTES
WHERE
   CLI_IDENTI > 0
PLAN
   (CLIENTES INDEX (PK_CLIENTES))

Al intentar ejecutar ese SELECT muestra el mensaje: «Index PK_CLIENTES cannot be used in the specified plan.» ¿Por qué muestra ese mensaje? Porque el índice PK_CLIENTES es un índice compuesto por dos columnas: CLI_CODSUC y CLI_IDENTI y arriba usamos la segunda columna solamente, al cambiar el SELECT anterior por este:

Ejemplo 2:

SELECT
   *
FROM
   CLIENTES
WHERE
   CLI_CODSUC = 0 AND
   CLI_IDENTI > 0
PLAN
   (CLIENTES INDEX (PK_CLIENTES))

todo funciona perfectamente porque en el WHERE se escribieron las dos columnas que componen al índice PK_CLIENTES. El Ejemplo 1. también habría funcionado perfectamente si en lugar de escribir: CLI_IDENTI > 0 hubiéramos escrito CLI_CODSUC = 0 ó también CLI_CODSUC > 0 ó cualquier otra combinación que involucre a la primera columna del índice.

¿Un plan puede involucrar a varias tablas?

Sí, por cada tabla que haya en tu SELECT en el plan se especificará cual índice se usará.

Ejemplo 3:

PLAN JOIN (V_ABM_VENTAS_CONTA V NATURAL,
           V_ABM_VENTAS_CONTA C INDEX (PK_CLIENTES),
           V_ABM_VENTAS_CONTA S INDEX (UQ_SUCURSALES))

En este caso vemos que la vista V_ABM_VENTAS_CONTA tiene:

• Un orden NATURAL (o sea que no se usará ningún ordenamiento específico)
• Un orden PK_CLIENTES (o sea que se usará la Primary Key de la tabla CLIENTES)
• Un orden UQ_SUCURSALES (o sea que se usará la Unique Key de la tabla SUCURSALES)

Como las tres tablas de esa vista tienen alias, en lugar de ver los nombres de las tablas verás sus alias (en este caso V para la tabla principal (VENTAS), C para una de las tablas usadas en el JOIN (que se llama CLIENTES) y S para la otra tabla usada en el JOIN (que se llama SUCURSALES)

¿Puedo tener un ORDER en un plan si no tengo la cláusula ORDER BY en el SELECT?

No, porque eso no tiene sentido; el plan solamente usa las columnas que se especifican en el SELECT.

Ejemplo 4:

La tabla ASIENTOSCAB tiene un índice llamado ASC01. Ese índice está compuesto por las columnas: ASC_ANOEJE, ASC_CODSUC, ASC_NUMERO (año del ejercicio contable, código de la sucursal, número del asiento)

Este SELECT nos mostrará el error de que el índice no puede ser usado en el plan especificado:

SELECT
   *
FROM
   ASIENTOSCAB
PLAN
   (ASIENTOSCAB INDEX (ASC01))

¿Por qué el error? porque el SELECT no tiene una cláusula WHERE ni una cláusula ORDER BY. En cambio este SELECT sí funcionará:

SELECT
   *
FROM
   ASIENTOSCAB
WHERE
   ASC_ANOEJE = 2013
PLAN
   (ASIENTOSCAB INDEX (ASC01))

El SELECT de arriba funciona porque en la cláusula WHERE se especificó la primera columna del índice ASC01. El SELECT de abajo también funcionará:

SELECT
   *
FROM
   ASIENTOSCAB
PLAN
   (ASIENTOSCAB INDEX (ASC01))
ORDER BY
   ASC_ANOEJE

Este SELECT funciona porque en la cláusula ORDER BY se especificó la primera columna del índice ASC01.

¿Cuándo se debería especificar un PLAN manualmente?

Cuando la consulta es lenta. Cualquier consulta que tarde más de 5 segundos en mostrar sus resultados amerita que se averigüe el motivo y uno de esos motivos puede ser que está usando un mal plan.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21