Diseño de índices en detalle
Un índice en PostgreSQL es una estructura separada y ordenada que se encuentra junto a una tabla y proporciona al planificador una ruta más rápida a filas específicas que leer toda la tabla.
Diseñar índices correctamente es menos memorizar la sintaxis de CREATE INDEX y más comprender qué es realmente un índice internamente, cómo el planificador decide si usarlo y qué te cuesta cada vez que cambia una fila.
Esta página construye ese modelo mental para que las páginas más tácticas de esta sección, sobre el orden de las columnas, la redundancia y la reorganización de índices, tengan sentido como consecuencias de la misma estructura subyacente en lugar de reglas separadas para memorizar.
Resumen
- Un índice es una estructura ordenada y mantenida por separado que permite al planificador localizar filas coincidentes sin leer la tabla completa.
- Por qué Importa: Sin un índice, cada consulta filtrada cuesta un escaneo completo de la tabla, y ese costo crece linealmente con el tamaño de la tabla.
- Conceptos Clave: B-tree, escaneo de índice, prefijo más a la izquierda, acceso al heap, escaneo solo con índice, mapa de visibilidad.
- Cuándo Usar: Diseñar un nuevo índice requiere conocer las formas de consulta que debe servir, no solo qué columnas "parecen importantes".
- Limitaciones / Compensaciones: Cada índice agrega sobrecarga de escritura, almacenamiento y trabajo de vacuum, y un índice no utilizado o redundante es un costo puro sin beneficio.
- Temas Relacionados: estadísticas del planificador de consultas, lectura del plan EXPLAIN, autovacuum e hinchazón, internos del método de acceso.
Fundamentos
El tipo de índice predeterminado y más común en PostgreSQL es el B-tree, una estructura de árbol balanceado que mantiene sus claves en orden ordenado y permite al planificador encontrar cualquier clave, o un rango contiguo de claves, en un número pequeño y limitado de lecturas de página.
Piénsalo de la misma manera que pensarías en un directorio telefónico ordenado por apellido: encontrar "Smith" no requiere leer todas las páginas, porque la estructura ordenada te permite saltar directamente al vecindario correcto y escanear un pequeño rango local.
Esa analogía también explica la regla más importante de los índices B-tree compuestos: solo te ayudan a localizar eficientemente valores por un prefijo más a la izquierda de sus columnas clave, en orden.
Un índice en (apellido, nombre) sirve perfectamente a una búsqueda solo por apellido, de la misma manera que un directorio telefónico ordenado por apellido todavía te permite encontrar todos los Smith, pero no puede servir eficientemente a una búsqueda solo por nombre, porque los nombres están dispersos en el orden ordenado.
Una entrada de índice simple almacena los valores de la columna indexada más un puntero, llamado TID, a la ubicación física de la fila en el heap de la tabla.
Debido a ese puntero, la mayoría de los escaneos de índice son en realidad operaciones de dos pasos: el planificador recorre el índice para encontrar TIDs coincidentes, luego sigue cada puntero de regreso al heap para recuperar el resto de la fila.
Ese segundo paso, llamado acceso al heap, es donde se esconde gran parte del costo real de un escaneo de índice, porque las páginas del heap no se almacenan necesariamente en el mismo orden que el índice, por lo que seguir muchos punteros puede significar muchas lecturas dispersas y no secuenciales.
PostgreSQL también admite tipos de índice no B-tree creados para diferentes formas de datos: GIN para consultas de contención sobre JSONB o arrays, GiST y sus parientes para búsqueda geométrica y de vecinos más cercanos, y BRIN para tablas extremadamente grandes y naturalmente ordenadas como datos de series temporales.
Cada uno de ellos existe porque el modelo de coincidencia de prefijo ordenado de un B-tree simplemente no se ajusta a todas las formas de consulta, no porque sean índices estrictamente "mejores".
Mecánica e Interacciones
Crear un índice no obliga al planificador a usarlo; solo agrega un nuevo candidato al conjunto de rutas de acceso que el planificador evaluará frente a todas las demás opciones, incluido un escaneo secuencial simple.
Si el planificador elige realmente tu nuevo índice, se reduce por completo al modelo de costos discutido en las páginas del planificador de este sitio: la selectividad estimada del predicado de tu consulta, combinada con random_page_cost y el tamaño de la tabla, determina si el acceso disperso al heap de un escaneo de índice supera simplemente leer la tabla directamente.
Es por eso que un índice en una columna de baja selectividad, una que coincide con una gran fracción de filas, a menudo es ignorado por el planificador, aunque técnicamente "podría" usarse, porque un escaneo secuencial es genuinamente más barato una vez que de otra manera estarías siguiendo punteros de regreso a la mayor parte de la tabla.
El mapa de visibilidad introduce una segunda vía de optimización importante: si se sabe que todas las filas en una página de heap son visibles para todas las transacciones, PostgreSQL a veces puede omitir el acceso al heap por completo y responder a una consulta utilizando solo el índice, siempre que todas las columnas que la consulta necesita estén presentes en el propio índice.
Esa optimización se llama escaneo solo con índice, y es la razón por la que existe la cláusula INCLUDE, que te permite transportar columnas de carga adicionales en las páginas hoja del índice puramente para satisfacer este atajo sin hacer que esas columnas formen parte de la clave ordenable.
Los índices parciales interactúan con este mismo modelo de costos desde un ángulo diferente: al agregar una cláusula WHERE a la definición del índice, reduces el índice solo a las filas que realmente importan para un patrón de consulta recurrente específico, lo que reduce tanto su tamaño como el costo que el planificador asigna a su escaneo.
Cada uno de estos beneficios tiene un costo reflejado en el lado de la escritura, porque un índice no es un artefacto de solo lectura; es una estructura viva que debe mantenerse correcta en cada INSERT, UPDATE y DELETE que toque una columna indexada.
Una UPDATE que cambia una columna indexada generalmente requiere que PostgreSQL inserte una nueva entrada de índice, y dependiendo de si la actualización califica para la optimización de tupla solo de heap, esa amplificación de escritura puede multiplicarse en cada índice definido en la tabla.
Con el tiempo, a medida que las filas se actualizan y eliminan, las páginas B-tree acumulan entradas muertas, una condición generalmente llamada hinchazón de índice, que aumenta el tamaño del índice en disco y ralentiza los escaneos hasta que vacuum recupera el espacio o una reconstrucción lo compacta.
-- Dos índices, dos trabajos muy diferentes de la misma estructura B-tree:
-- uno satisface WHERE + ORDER BY juntos, el otro se reduce a un subconjunto "caliente".
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
CREATE INDEX orders_open_idx
ON orders (customer_id) WHERE status = 'open';Consideraciones y Aplicaciones Avanzadas
A escala, el diseño de índices se convierte en un problema de cartera en lugar de una decisión por consulta, porque cada índice que agregas para servir a un patrón de consulta agrega un costo continuo de escritura y vacuum a cada escritura contra esa tabla, independientemente de si esa consulta se ejecuta alguna vez.
Los equipos que agregan un índice por cada consulta lenta ad hoc sin auditar periódicamente su uso tienden a acumular índices redundantes o superpuestos, donde un índice compuesto de tres columnas hace que un índice separado de una sola columna en su columna principal sea completamente superfluo.
CREATE INDEX CONCURRENTLY existe específicamente para sistemas de producción, construyendo el índice sin mantener el bloqueo exclusivo que tomaría un CREATE INDEX simple, a costa de una compilación más lenta y un pequeño riesgo de necesitar reintentar si se interrumpe a mitad de la compilación.
Las tablas de alta escritura, solo de inserción, con claves monótonamente crecientes, como las tablas de series temporales o de registros de eventos, a menudo prefieren los índices BRIN sobre los B-tree, porque BRIN almacena solo rangos de resumen aproximados por bloque en lugar de una entrada por fila, intercambiando precisión por una estructura drásticamente más pequeña que cuesta mucho menos mantener.
Las cargas de trabajo especializadas llevan el diseño de índices aún más lejos: los índices de vecino más cercano aproximado de pgvector intercambian la recuperación exacta por la velocidad de consulta en la búsqueda de similitud, y los índices espaciales de PostGIS dependen de GiST para hacer que las consultas de contención de cuadros delimitadores sean manejables, ninguna de las cuales un B-tree podría servir.
El mantenimiento de índices tampoco es una decisión única, ya que REINDEX CONCURRENTLY existe precisamente porque la hinchazón y la fragmentación de la división de páginas se acumulan durante la vida útil de un índice ocupado, y la reconstrucción periódica de un índice muy alterado puede reducirlo y restaurar un rendimiento de escaneo predecible.
| Tipo de Índice | Fortaleza | Debilidad | Mejor Ajuste |
|---|---|---|---|
| B-tree | Igualdad, rango, ordenación, búsquedas compuestas de prefijo más a la izquierda | Solo útil a través de prefijo de clave; grande en claves anchas de alta cardinalidad | La opción predeterminada para casi todas las columnas de filtro/ordenación escalar |
| GIN | Contención rápida en JSONB, arrays, texto completo | Escrituras más lentas y más grandes que B-tree | @>, pertenencia a arrays, columnas de búsqueda de texto completo |
| GiST/SP-GiST | Consultas geométricas, de superposición de rangos, de vecino más cercano | Más lento que B-tree para igualdad simple | Geometrías PostGIS, tipos de rango, búsqueda de similitud |
| BRIN | Huella diminuta en tablas muy grandes y naturalmente ordenadas | Inútil si los datos no están físicamente ordenados por la clave | Tablas de series temporales o de registro de solo adición |
Conceptos Erróneos Comunes
- "Agregar un índice garantiza que el planificador lo usará." - El planificador solo elige un escaneo de índice cuando su costo estimado supera a todos los demás planes candidatos para esa consulta específica.
- "Más índices siempre hacen las consultas más rápidas." - Cada índice agrega sobrecarga de escritura y vacuum, y un índice que nunca se escanea es un costo puro.
- "El orden de las columnas en un índice compuesto no importa, solo qué columnas se incluyen." - Los índices compuestos B-tree solo sirven búsquedas eficientes en un prefijo más a la izquierda, por lo que el orden es a menudo toda la decisión de diseño.
- "Un escaneo de índice nunca toca la tabla en sí." - La mayoría de los escaneos de índice todavía siguen un puntero de regreso al heap; solo los escaneos solo con índice, y solo cuando el mapa de visibilidad coopera, evitan ese paso.
- "Un índice único y una restricción UNIQUE son mecanismos diferentes." - Una restricción UNIQUE se implementa utilizando un índice único internamente; son la misma estructura con una restricción declarada encima.
- "Una vez construido, un índice permanece óptimamente eficiente para siempre." - Las actualizaciones y eliminaciones continuas fragmentan e hinchan las páginas B-tree, razón por la cual las operaciones de mantenimiento como REINDEX existen como una rutina, no como un rescate.
Preguntas Frecuentes
¿Qué almacena realmente un índice B-tree?
Almacena los valores de la columna indexada en orden ordenado, más un puntero (TID) a la ubicación de cada fila coincidente en el heap de la tabla.
Esa estructura ordenada es lo que permite a PostgreSQL localizar un valor, o un rango de valores, sin escanear cada fila.
¿Por qué un índice compuesto en (a, b) no ayuda a una consulta que solo filtra por b?
Un índice compuesto B-tree se ordena principalmente por su primera columna, por lo que los valores de la segunda columna están dispersos, no agrupados.
Solo una consulta que filtra por un prefijo más a la izquierda de las columnas clave puede usar el índice de manera eficiente para ese propósito.
¿Crear un índice obliga a PostgreSQL a usarlo?
No, el índice solo se convierte en una ruta de acceso candidata más que el planificador evalúa frente a alternativas como un escaneo secuencial.
Los predicados de baja selectividad en particular a menudo se sirven de manera más económica con un escaneo secuencial, incluso cuando existe un índice coincidente.
¿Qué es un escaneo solo con índice y por qué es más rápido?
Es un escaneo que responde a una consulta utilizando solo los datos del índice, omitiendo el segundo paso de seguir punteros de regreso al heap.
Requiere que todas las columnas que la consulta necesita ya estén presentes en el índice, y depende de que el mapa de visibilidad confirme que las páginas de heap relevantes no necesitan una verificación de visibilidad fresca.
¿Por qué agregaría columnas a un índice usando INCLUDE en lugar de simplemente agregarlas a la clave?
Las columnas INCLUDE acompañan en las páginas hoja del índice para habilitar escaneos solo con índice sin convertirlas en parte de la clave ordenable y buscable.
Mantener la clave en sí misma estrecha mantiene el índice más pequeño y las reglas de coincidencia de prefijo más a la izquierda más fáciles de razonar.
¿Por qué cada índice ralentiza las escrituras?
Cualquier INSERT, UPDATE o DELETE que toque una columna indexada también debe actualizar la propia estructura B-tree de ese índice para mantenerse correcto.
Una tabla con muchos índices multiplica este costo en cada uno de ellos en cada escritura relevante.
¿Qué es la hinchazón de índices y de dónde proviene?
La hinchazón es la acumulación de espacio muerto o medio vacío dentro de las páginas de un B-tree a medida que las filas se actualizan y eliminan con el tiempo.
Aumenta el tamaño del índice en disco y puede ralentizar los escaneos hasta que vacuum recupera el espacio o una reconstrucción compacta la estructura.
¿Cuándo debería optar por un índice parcial en lugar de uno completo?
Cuando un patrón de consulta filtra consistentemente a un subconjunto estable y bien conocido de filas, como status = 'open'.
Un índice parcial solo indexa ese subconjunto, lo que reduce tanto su tamaño como su costo de mantenimiento en comparación con indexar la tabla completa.
¿Por qué elegiría BRIN sobre un B-tree?
BRIN almacena rangos de resumen aproximados por bloque en lugar de una entrada por fila, lo que lo hace mucho más pequeño y más barato de mantener en tablas muy grandes.
Solo funciona bien cuando la columna indexada está naturalmente correlacionada con el orden físico de las filas, como una marca de tiempo de solo adición.
¿Es una restricción UNIQUE un mecanismo diferente de un índice único?
No, declarar una restricción UNIQUE crea un índice único detrás de escena, y ambos imponen y sirven búsquedas a través de la misma estructura.
No existe un mecanismo de verificación de restricciones separado aparte de ese índice.
¿Por qué dos índices en la misma tabla se considerarían redundantes?
Si las columnas clave de un índice son un prefijo más a la izquierda estricto de otro, el índice más corto rara vez justifica su propio costo de mantenimiento.
El planificador generalmente puede satisfacer los casos de uso del índice más corto utilizando el índice compuesto más largo en su lugar.
¿La creación de un índice siempre bloquea las escrituras en la tabla?
Un CREATE INDEX simple toma un bloqueo que bloquea escrituras concurrentes durante su duración.
CREATE INDEX CONCURRENTLY evita ese bloqueo exclusivo a costa de una compilación más lenta y una pequeña posibilidad de necesitar un reintento si se interrumpe.
Relacionados
- Conceptos básicos de diseño de índices - la receta práctica para hacer coincidir índices con formas de consulta
- Orden de índices multicolumna - la regla del prefijo más a la izquierda aplicada en la práctica
- Índices duplicados y redundantes - auditoría del problema de costo de cartera descrito aquí
- REINDEX y REINDEX CONCURRENTLY - reparación de la hinchazón una vez que se acumula
- Seq Scan vs Index Scan - cómo el planificador elige realmente entre estas rutas de acceso
Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (línea estable 18, línea de mantenimiento 17), con el comportamiento de GIN/GiST/BRIN y la mecánica de escaneo solo con índice estables en versiones principales recientes; pgvector 0.8+ y PostGIS 3.5+ se mencionan por sus tipos de índice especializados.