Índices parciales

Hasta ahora, se ha hablado solamente de las columnas para agregar a los índices. Con los índices parciales (PostgreSQL) o filtrados (SQL Server), se pueden especificar qué registros serán indexados.

Un índice parcial es muy útil para los filtros where comúnmente usados, que usan valores constantes, como un código de estado en el siguiente ejemplo:

SELECT message
  FROM messages
 WHERE processed = 'N'
   AND receiver  = ?

Las sentencias como esta son muy comunes en los sistemas de colas. La sentencia busca todos los mensajes no procesados para un destinatario particular. Los mensajes que ya han sido procesados raramente se usan. Si se necesitan, se puede tener acceso a ellos por un criterio más específico como la llave primaria.

Se puede optimizar esta sentencia con un índice de dos columnas. Considerando solamente esta sentencia, el orden de las columnas no importa porque no existe un rango de condición.

CREATE INDEX messages_todo
          ON messages (receiver, processed)

El índice satisface el objetivo, pero incluye muchos registros que nunca son buscados, en concreto todos los mensajes que ya se han procesados. Gracias a la escalabilidad logarítmica del índice, la sentencia es muy rápida, pero se desperdicia mucho espacio en disco.

Con un índice parcial, se puede limitar el índice para incluir solamente los mensajes no procesados. La sintaxis para este índice es sorprendentemente sencilla: un filtro where.

CREATE INDEX messages_todo
          ON messages (receiver)
       WHERE processed = 'N'

El índice contiene solamente los registros que cumplen con el filtro where. En este caso particular, se puede también eliminar la columna PROCESSED porque de todos modos, es siempre 'N'. Eso significa que el índice reduce su tamaño en dos dimensiones: verticalmente, porque contiene menos registros; horizontalmente, debido a la columna eliminada.

El índice es por lo tanto muy pequeño. Para una cola de mensaje, puede significar que el tamaño del índice es fijo, aunque la tabla crezca sin límite. El índice no contiene todos los mensajes, solamente los no procesados.

El filtro where de un índice parcial puede llegar a ser arbitrariamente complejo. La única limitación esencial está en las funciones: se pueden usar solamente las funciones deterministas, al igual que sucede en las otras partes de la definición del índice. SQL Server tiene, sin embargo, reglas más restrictivas y no permite las funciones ni el operador OR dentro de los predicados del índice.

Una base de datos podrá usar un índice parcial cada vez que el filtro where aparece en la sentencia.

SELECT message
  FROM messages
 WHERE processed = 'N'

Si te gusta mi manera de explicar, te encantará mi libro.