Contenido de esta guía
Respuesta rápida
Un índice parcial —o filtrado, en la terminología de SQL Server— indexa solo las filas que cumplen una condición. Es útil cuando una fracción estable y relevante de la tabla concentra muchas consultas, por ejemplo tareas pendientes dentro de un histórico mayoritariamente completado.
CREATE INDEX idx_tareas_pendientes_prioridad
ON tareas (prioridad)
WHERE estado = 'pendiente';El WHERE del índice no filtra la consulta por ti: define qué filas tienen una entrada dentro de ese índice.
Modelo mental: un índice que ignora filas fuera de interés
Un índice normal mantiene entradas para toda la tabla. Un índice parcial mantiene entradas solo para el subconjunto cuyo predicado es verdadero. Si casi todas las tareas están terminadas y tu aplicación consulta repetidamente las pendientes, el subconjunto activo puede ser mucho menor que la tabla completa.
No es una receta automática: el beneficio depende de la distribución real de datos, las consultas y el optimizador. Diseña el predicado desde una carga concreta y comprueba el plan con EXPLAIN.
Ejemplo comprobado: priorizar una cola pendiente
CREATE TEMP TABLE tareas_demo (
id INTEGER PRIMARY KEY,
estado TEXT NOT NULL,
prioridad INTEGER NOT NULL
);
INSERT INTO tareas_demo VALUES
(1, 'hecha', 1),
(2, 'pendiente', 2),
(3, 'pendiente', 4),
(4, 'hecha', 5),
(5, 'pendiente', 3),
(6, 'hecha', 2);
CREATE INDEX idx_tareas_pendientes_prioridad
ON tareas_demo (prioridad)
WHERE estado = 'pendiente';
SELECT id, estado, prioridad
FROM tareas_demo
WHERE estado = 'pendiente'
AND prioridad >= 2
ORDER BY prioridad DESC, id;| id | estado | prioridad |
|---|---|---|
| 3 | pendiente | 4 |
| 5 | pendiente | 3 |
| 2 | pendiente | 2 |
En esta demo, EXPLAIN QUERY PLAN de SQLite reconoce el índice parcial para la condición estado = 'pendiente' y el rango de prioridad. En otra tabla, con otras estadísticas o una consulta diferente, el plan puede cambiar.
La consulta debe ser compatible con el predicado
Para aprovechar un índice parcial, el optimizador necesita demostrar que la consulta trabaja dentro del subconjunto indexado. PostgreSQL documenta que el predicado de la consulta debe implicar el predicado del índice y que esa comprobación ocurre al planificar. SQLite también aplica reglas para determinar cuándo el WHERE de una consulta permite utilizar un índice parcial.
| Consulta | Encaje |
|---|---|
WHERE estado = 'pendiente' AND prioridad >= 2 | Trabaja dentro del subconjunto indexado. |
WHERE estado = 'hecha' | Busca precisamente filas que el índice no contiene. |
| Sin filtro por estado | Necesita filas de ambos estados; el índice parcial por sí solo no representa toda la tabla. |
También puede imponer unicidad solo en un subconjunto
PostgreSQL y SQLite permiten un índice parcial UNIQUE. Ese patrón sirve, por ejemplo, para exigir una sola fila “activa” por entidad sin imponer la misma regla a filas históricas que ya no están activas.
CREATE UNIQUE INDEX uq_suscripcion_activa
ON suscripciones (cliente_id)
WHERE activa = 1;Antes de usar este diseño, comprueba que la condición expresa una regla de negocio estable y que el motor soporta la sintaxis concreta.
Compatibilidad entre motores
| Motor | Soporte directo |
|---|---|
| PostgreSQL | Índices parciales mediante CREATE INDEX ... WHERE .... |
| SQLite | Índices parciales mediante CREATE INDEX ... WHERE ..., incluidos índices parciales únicos. |
| SQL Server | Los denomina índices filtrados y también usa un predicado WHERE en un índice no agrupado. |
| MySQL 8.4 | La gramática documentada de CREATE INDEX no incluye una cláusula WHERE; no copies este patrón como si fuera portable. |
Errores frecuentes
Crear el índice porque el subconjunto “parece pequeño”
Comprueba distribución, consultas y planes reales. Un índice adicional también consume espacio y trabajo de mantenimiento.
Usar un predicado distinto al de las consultas
Si el filtro del índice y el filtro habitual no encajan de forma demostrable, el optimizador puede no usarlo.
Confundir índice parcial con índice compuesto
Un índice compuesto decide qué columnas forman la clave; uno parcial decide qué filas entran. Un mismo índice puede ser ambas cosas.
Práctica guiada
Indexa solo pedidos pendientes
Una tabla pedidos(id, estado, fecha) conserva años de históricos, pero el panel operativo consulta constantemente los pedidos con estado = 'pendiente' ordenados por fecha. Escribe un índice parcial para PostgreSQL o SQLite.
Criterio de éxito: la clave del índice es fecha y el predicado limita las filas a pendientes.
CREATE INDEX.WHERE al propio índice.Solución razonada
CREATE INDEX idx_pedidos_pendientes_fecha
ON pedidos (fecha)
WHERE estado = 'pendiente';El índice conserva entradas de fecha solo para pedidos pendientes. Después debes comprobar con EXPLAIN y datos representativos si tu motor lo elige y si el coste de mantenimiento compensa.
Qué debes recordar
- Un índice parcial o filtrado cubre solo filas que cumplen un predicado.
- Puede reducir tamaño y mantenimiento cuando el subconjunto relevante es mucho menor.
- La consulta debe ser compatible con el predicado para que el índice resulte utilizable.
- Puede combinarse con varias columnas o con
UNIQUE. - La sintaxis y el nombre cambian por motor; valida siempre con
EXPLAINy mediciones.