Cómo funcionan los índices
Un índice es una estructura secundaria que permite a PostgreSQL encontrar filas sin tener que leer cada fila de una tabla.
Sin uno, responder a WHERE email = 'ada@example.com' significa escanear toda la tabla y verificar cada fila, lo cual está bien para mil filas y es doloroso para cien millones.
Esta página construye el modelo mental detrás de la indexación antes de que recurras a un método de acceso específico como B-tree, GIN, GiST o BRIN en las otras páginas de esta sección.
Obtener este modelo correctamente es importante porque los índices no son gratuitos, y la habilidad real es saber cuándo la mejora de velocidad de lectura vale el costo de escritura.
Resumen
- Un índice intercambia costo de tiempo de escritura y almacenamiento por velocidad de tiempo de lectura, manteniendo una estructura separada y buscable junto a la tabla.
- Por qué es Importante: Un índice faltante convierte una búsqueda de milisegundos en un escaneo completo de tabla, y un índice redundante grava silenciosamente cada escritura para siempre.
- Conceptos Clave: método de acceso, selectividad, estimación de costo del planificador, mapa de visibilidad, amplificación de escritura, inflado de índice.
- Cuándo Usar: Agrega un índice cuando una consulta filtra, une o ordena por una columna y la tabla es lo suficientemente grande como para que un escaneo sea costoso.
- Limitaciones / Compensaciones: Cada índice ralentiza las inserciones y actualizaciones, consume espacio en disco y necesita mantenimiento como
REINDEXo atención de autovacuum con el tiempo. - Temas Relacionados: Índices B-tree, GIN y GiST, BRIN, escaneos solo de índice, índices parciales y de cobertura.
Fundamentos
El tipo de índice predeterminado y más común en PostgreSQL es el B-tree, una estructura de árbol balanceado que mantiene los valores en orden ordenado.
Un B-tree responde eficientemente a búsquedas de igualdad, consultas de rango y recuperación ordenada, lo que cubre la gran mayoría de las cláusulas WHERE y ORDER BY cotidianas.
PostgreSQL también incluye otros métodos de acceso creados para formas de datos que un B-tree maneja mal: GIN para valores que contienen muchos elementos como arrays y búsqueda de texto completo, GiST para consultas geométricas y de superposición de rangos, y BRIN para tablas muy grandes donde el orden físico de las filas ya se correlaciona con el valor de una columna.
Piensa en un índice de la misma manera que pensarías en el índice al final de un libro de texto: no contiene el contenido en sí, contiene punteros ordenados de vuelta a las páginas donde vive ese contenido.
Ese puntero, en el caso de PostgreSQL, es un identificador de tupla (TID), una dirección física de una versión de fila en el disco.
Un índice B-tree simple en una columna de correo electrónico se ve así en la práctica.
CREATE INDEX customers_email_idx ON app.customers (email);Después de esta declaración, PostgreSQL mantiene una estructura ordenada de valores de correo electrónico junto a la tabla, y una consulta que filtra por correo electrónico puede saltar casi directamente a las filas coincidentes en lugar de leer toda la tabla.
La selectividad es el concepto que determina si ese índice realmente ayuda: una consulta que coincide con una fila de un millón es altamente selectiva y se beneficia enormemente de un índice, mientras que una consulta que coincide con la mitad de la tabla gana poco porque la mayor parte de la tabla se tocaría de todos modos.
Mecánica e Interacciones
Cuando PostgreSQL recibe una consulta, el planificador no simplemente usa un índice porque técnicamente se aplica al predicado.
Estima el costo de cada plan viable, incluido un escaneo secuencial, un escaneo de índice y un escaneo de índice de mapa de bits, utilizando estadísticas de tabla y columna recopiladas por ANALYZE.
Para un predicado de baja selectividad, un escaneo secuencial puede ser en realidad más barato que un escaneo de índice, porque las búsquedas de acceso aleatorio a través de un índice cuestan más por fila de lo que el índice ahorra al omitir las filas no coincidentes.
Esta es la razón por la que forzar el uso de índices rara vez es el instinto correcto, y por qué las estadísticas obsoletas después de una carga masiva son una causa común de planes repentinamente malos.
Un escaneo de índice de mapa de bits es el punto intermedio del planificador: utiliza el índice para construir un mapa en memoria de las ubicaciones de filas coincidentes, ordena ese mapa por página física y luego lee la tabla en orden de página en lugar de saltar aleatoriamente.
Los índices compuestos y multicolumna agregan otra capa de matiz, ya que el orden de las columnas dentro del índice determina qué formas de consulta pueden usarlo.
Un índice en (account_id, created_at) sirve para consultas que filtran solo por account_id o por ambas columnas juntas, pero generalmente no puede servir a una consulta que filtra solo por created_at, porque la columna principal controla el orden de clasificación que el índice realmente almacena.
Cada índice también tiene un costo del lado de escritura que es fácil de subestimar.
INSERT INTO app.customers (email) VALUES ('new@example.com');
-- también actualiza customers_email_idx, además del índice de clave primariaCada inserción, actualización o eliminación debe mantener cada índice en esa tabla, no solo la clave primaria, por lo que una tabla con ocho índices paga ese costo ocho veces en cada escritura.
La optimización Heap-Only Tuple (HOT) de PostgreSQL puede evitar actualizar índices por completo cuando una actualización cambia solo columnas que ningún índice cubre y la nueva versión de la fila cabe en la misma página, que es por lo que mantener las columnas actualizadas con frecuencia fuera de índices innecesarios tiene un beneficio real y medible.
El mapa de visibilidad, una estructura compacta por tabla que rastrea qué páginas contienen solo filas visibles para todas las transacciones, es lo que permite que un escaneo solo de índice responda una consulta directamente desde el índice sin tocar la tabla, siempre que cada columna que la consulta necesita esté presente en el índice.
Consideraciones Avanzadas y Aplicaciones
A escala, la estrategia de índices se trata tanto de sustracción como de adición.
pg_stat_user_indexes expone idx_scan, un contador en ejecución de cuántas veces cada índice ha sido realmente utilizado, y un índice que permanece en cero escaneos durante semanas de tráfico de producción es un fuerte candidato para su eliminación.
CREATE INDEX CONCURRENTLY existe específicamente porque una construcción de índice normal toma un bloqueo que bloquea las escrituras durante su duración, y en una tabla grande y ocupada, ese bloqueo puede significar tiempo de inactividad real.
La variante concurrente construye el índice en segundo plano a costa de una construcción más lenta y la posibilidad de un índice INVALID si la construcción se interrumpe, que luego necesita ser eliminado y reintentado.
Elegir entre métodos de acceso es fundamentalmente una cuestión de cómo se ven tus predicados, no una cuestión de que un tipo sea generalmente mejor que otro.
| Método de Acceso | Fortaleza | Debilidad | Mejor Ajuste |
|---|---|---|---|
| B-tree | Igualdad, rango y recuperación ordenada; la opción segura por defecto | Sin ayuda para predicados de contención o superposición | La mayoría de los filtros OLTP, uniones y cláusulas ORDER BY |
| GIN | Búsquedas rápidas dentro de valores compuestos como arrays, jsonb y vectores de texto completo | Escrituras más lentas y más grandes que B-tree | Búsqueda de texto completo, contención de arrays, búsquedas de claves jsonb |
| GiST | Soporta predicados de vecino más cercano y superposición a través de un árbol flexible | Las búsquedas son aproximadas y luego refinan, generalmente más lentas que B-tree para igualdad simple | Datos geométricos, superposición de rangos, consultas espaciales PostGIS |
| BRIN | Tamaño de índice diminuto en relación con el tamaño de la tabla | Solo efectivo cuando el orden físico de las filas se correlaciona con el valor indexado | Tablas de solo adición, ordenadas por tiempo, de decenas de millones de filas o más |
Los escaneos solo de índice merecen especial atención como técnica de escalado, ya que agregar columnas no clave con INCLUDE puede permitir que una consulta se responda completamente desde el índice cuando el mapa de visibilidad está actualizado, eliminando una búsqueda de tabla por fila.
Los índices parciales llevan la selectividad más allá al indexar solo el subconjunto de filas que una carga de trabajo consulta realmente, como indexar solo WHERE deleted_at IS NULL, lo que mantiene el índice pequeño y rápido incluso a medida que la tabla completa crece con filas históricas que las consultas rara vez tocan.
La observabilidad en torno a los índices vale la pena incorporarla a las operaciones de rutina, ya que el seguimiento del crecimiento del tamaño del índice, la relación de inflado y los recuentos de escaneos con el tiempo detecta tanto los índices faltantes, visibles como consultas lentas, como los redundantes, visibles como sobrecarga de escritura sin beneficio de lectura correspondiente.
Conceptos Erróneos Comunes
- "Más índices siempre hacen las consultas más rápidas." Cada índice adicional agrega costo de tiempo de escritura y almacenamiento, y un índice que el planificador nunca elige usar es una sobrecarga pura sin beneficio compensatorio.
- "El planificador siempre usará un índice si existe en la columna filtrada." El planificador compara los costos estimados entre todos los planes viables, y un escaneo secuencial realmente gana para predicados de baja selectividad o tablas pequeñas.
- "Un índice compuesto en (a, b) también sirve para consultas que filtran solo en b." La columna principal define el orden de clasificación del índice, por lo que una consulta que omite la columna principal generalmente no puede usar ese índice de manera eficiente.
- "Los escaneos solo de índice siempre evitan tocar la tabla." Solo lo evitan cuando el mapa de visibilidad confirma que las páginas relevantes no tienen filas invisibles para la transacción actual, lo que requiere un vacuuming regular para mantenerse cierto.
- "Reconstruir un índice siempre requiere tiempo de inactividad."
REINDEX CONCURRENTLYyCREATE INDEX CONCURRENTLYexisten precisamente para evitar el bloqueo exclusivo que tomaría una reconstrucción simple.
Preguntas Frecuentes
¿Por qué PostgreSQL no usa mi índice aunque exista en la columna en la que estoy filtrando?
- El planificador estima que un escaneo secuencial o de mapa de bits es más barato para esta consulta específica y distribución de datos.
- Las estadísticas obsoletas después de una carga masiva pueden hacer que el planificador juzgue mal la selectividad.
- Ejecutar
ANALYZEen la tabla a menudo resuelve planes que parecen incorrectos después de grandes cambios de datos.
¿Cuál es el costo real de agregar un índice?
Cada inserción, actualización o eliminación que toca una columna indexada también debe actualizar ese índice, por lo que cada índice adicional agrega latencia de escritura incremental y uso de disco que escala con el tamaño de la tabla.
¿Qué es un escaneo de índice de mapa de bits y por qué lo usa el planificador?
Un escaneo de índice de mapa de bits construye un mapa en memoria de las ubicaciones de filas coincidentes desde el índice, y luego lee la tabla en orden de página física, lo que a menudo es más barato que un escaneo de índice simple cuando coinciden un número moderado de filas.
¿Importa el orden de las columnas en un índice multicolumna?
Sí, la columna principal define el orden de clasificación del índice, por lo que una consulta que filtra solo por una columna no principal generalmente no puede usar el índice de la misma manera que una consulta que filtra por la columna principal.
¿Cómo encuentro índices que son seguros de eliminar?
- Consulta
pg_stat_user_indexesy mira la columnaidx_scan. - Un índice que permanece en cero o cerca de cero escaneos durante una ventana de tiempo representativa rara vez justifica su costo de escritura.
- Confirma que la ventana de observación cubre trabajos periódicos, como informes de fin de mes, antes de eliminar nada.
¿Qué es un escaneo solo de índice y por qué es más rápido?
Un escaneo solo de índice responde una consulta usando solo el índice, omitiendo completamente la búsqueda de tabla, lo cual es posible cuando todas las columnas solicitadas están presentes en el índice y el mapa de visibilidad confirma que las páginas relevantes son completamente visibles.
¿Debo agregar un índice para cada columna en la que filtro?
No automáticamente, ya que una columna con baja selectividad, como un indicador booleano, rara vez se beneficia lo suficiente de un índice como para compensar su costo de escritura, y los índices multicolumna o parciales a menudo superan a una pila de índices de una sola columna.
¿Por qué usaría GIN o GiST en lugar de un B-tree?
- Un B-tree no puede responder eficientemente "¿contiene esta matriz este valor" o "¿se superponen estos dos rangos?".
- GIN está diseñado para búsquedas de contención dentro de valores compuestos como arrays, jsonb y vectores de búsqueda de texto completo.
- GiST se generaliza a consultas de vecino más cercano y de superposición, que es por lo que PostGIS lo utiliza para datos espaciales.
¿Qué sacrifica BRIN para obtener un tamaño de índice tan pequeño?
BRIN almacena rangos de resumen por bloque en lugar de una estructura ordenada completa, por lo que es drásticamente más pequeño que un B-tree, pero solo es útil cuando el orden físico de las filas de una tabla ya se correlaciona con la columna indexada, como una tabla de solo adición ordenada por tiempo de inserción.
¿Por qué CREATE INDEX a veces bloquea las escrituras durante mucho tiempo?
Un CREATE INDEX simple toma un bloqueo que bloquea escrituras concurrentes en la tabla durante la duración de la construcción, que es por lo que CREATE INDEX CONCURRENTLY existe como una alternativa más lenta pero no bloqueante para tablas de producción.
¿Qué sucede si una construcción de índice CONCURRENTLY falla a mitad de camino?
El índice se deja en un estado INVALID, lo que significa que consume espacio y sobrecarga de escritura, pero nunca es utilizado por el planificador, por lo que debe ser eliminado y reconstruido.
¿Es un índice único lo mismo que una restricción única?
Una restricción única está respaldada por un índice único internamente, por lo que funcionalmente imponen la misma regla, aunque una restricción también aparece explícitamente en el catálogo de restricciones de la tabla para fines de documentación y herramientas.
¿Cómo se relaciona el mapa de visibilidad con la indexación en absoluto?
El mapa de visibilidad rastrea qué páginas de tabla contienen solo filas visibles para todas las transacciones, y es lo que permite que un escaneo solo de índice omita la tabla por completo, por lo que una tabla que rara vez se vacuumiza pierde gran parte del beneficio de los escaneos solo de índice.
Relacionados
- Fundamentos de Índices - ejemplos prácticos de creación e inspección de índices
- Índices B-tree - reglas de igualdad, rango y ordenación multicolumna en profundidad
- GIN y GiST - métodos de acceso para predicados de contención y superposición
- BRIN - índices de rango de bloques para tablas muy grandes y naturalmente ordenadas
- Escaneos Solo de Índice - cómo el mapa de visibilidad permite lecturas sin tabla
- Índices Parciales y de Cobertura - reduciendo índices al subconjunto de filas que importan
Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (línea principal estable 18, línea de mantenimiento 17 también soportada).