Indexación en SQLExplained

31 of 31 rows

Concepto↕	Explicación↕	Consejo↕
Tipos de índices
⤢B-tree	Árbol equilibrado para igualdad, rangos y recorridos ordenados. El predeterminado en la mayoría de bases de datos.	Úsalo para =, <, >, BETWEEN, ORDER BY. Evítalo para búsqueda de texto completo.
⤢Clustered	Índice cuyo nivel hoja es la propia tabla. Las filas se guardan en orden de clave. Solo uno por tabla.	InnoDB agrupa por la clave primaria. Las tablas de SQL Server sin una son montones (heaps).
⤢Nonclustered	Estructura aparte que guarda claves más un puntero de vuelta a la tabla base. Puede haber muchos por tabla.	Sobre un montón guarda un ID de fila; sobre una tabla agrupada guarda la clave de agrupación.
⤢Hash	Tabla hash solo para búsquedas de igualdad. Coincidencia exacta en tiempo constante.	Úsalo solo para operaciones =; los rangos y la ordenación necesitan B-tree. El hash de tablas memory-optimized de SQL Server necesita un bucket count cercano al número de filas.
⤢GIN	Índice invertido generalizado. Mapea valores a filas. Pensado para arrays y texto completo.	Úsalo para jsonb, arrays, tsvector y búsqueda por trigramas. Más lento de construir que B-tree.
⤢GiST	Árbol de búsqueda generalizado. Extensible para tipos no escalares y consultas de solapamiento.	Úsalo para rangos geométricos, kNN y restricciones de exclusión. pg_trgm acelera LIKE.
⤢SP-GiST	GiST con partición espacial. Divide los datos en regiones sin solapamiento mediante árboles radix o quad.	Úsalo para puntos, prefijos IP y coincidencia por prefijo. No para consultas de solapamiento; usa GiST.
⤢BRIN	Índice de rangos de bloques. Guarda mín/máx por rango de bloques. Huella mínima.	Úsalo en tablas grandes ordenadas físicamente. Barato de construir, selectividad débil.
⤢Columnstore	Índice guardado como segmentos de columna en vez de filas. Alta compresión y ejecución por lotes.	Úsalo para recorridos analíticos con muchas filas y pocas columnas. Las búsquedas de una sola fila son lentas.
⤢Full-text	Índice invertido de palabras dedicado en MySQL y SQL Server. Se construye por columna de texto.	Usa MATCH AGAINST o CONTAINS. El texto completo de Postgres usa GIN sobre tsvector en su lugar.
⤢Bitmap	Índice de Oracle que guarda un mapa de bits por cada valor distinto. Cada bit marca una fila.	Úsalo en columnas de baja cardinalidad en almacenes de mucha lectura. Las escrituras concurrentes se serializan por mapa de bits.
⤢Descending	Columna de índice declarada DESC. Las claves de esa columna se guardan en orden inverso.	Necesario para ordenaciones mixtas como (a ASC, b DESC). Las ordenaciones DESC simples también pueden leer un índice normal al revés.
⤢Invisible	Índice de MySQL 8 que el optimizador ignora. Se sigue manteniendo en cada escritura.	Oculta un índice antes de eliminarlo para medir el impacto. Hazlo visible de nuevo sin reconstruirlo.
Diseño
⤢Composite	Índice de varias columnas. Las columnas iniciales deben coincidir con el orden de los filtros de la consulta.	Ordena primero la igualdad y luego el rango. Dos índices simples no sustituyen a uno compuesto.
⤢Partial	Índice sobre un predicado WHERE. SQL Server lo llama índice filtrado. Cubre solo las filas que coinciden.	Indexa solo las filas con active='t'. Reduce a la mitad el tamaño y el coste de escritura.
⤢Covering (INCLUDE)	B-tree con columnas extra que no son clave añadidas al final. Los index-only scans evitan acudir al heap.	Añade por INCLUDE las columnas que se consultan a menudo. Mantiene el índice ordenable.
⤢Unique	Índice que impide valores duplicados. Rechaza los inserts que colisionan.	Úsalo para claves naturales y restricciones uno a uno. UNIQUE NULL admite muchos NULL.
⤢Expression	Índice construido sobre una función de las columnas en lugar de las columnas mismas.	Indexa lower(email) o date_trunc('day', created_at). La consulta debe repetir la expresión exacta.
⤢Opclass	Clase de operador. Define cómo compara y almacena un índice el tipo de una columna.	Fija varchar_pattern_ops para LIKE 'abc%' sin plegado de mayúsculas. Los predeterminados sirven para igualdad y rangos.
⤢Index-only scan	Plan que responde una consulta solo con páginas del índice. El heap nunca se lee.	Necesita todas las columnas obtenidas en el índice o en la lista INCLUDE. Vacuum mantiene al día el mapa de visibilidad.
⤢Foreign keys	Las columnas de clave ajena no reciben índice automático en la tabla que referencia.	Indexa toda columna de FK por la que hagas join o delete. Los deletes en cascada recorren la tabla hija sin él.
Mantenimiento
⤢EXPLAIN	Inspector de planes. Muestra recorridos secuenciales, elecciones de índice y estimaciones de coste.	Usa EXPLAIN (ANALYZE, BUFFERS). Un Seq Scan en una tabla de 10M de filas indica un índice ausente.
⤢ANALYZE	Muestrea columnas de la tabla para construir estadísticas del planificador. Los histogramas guían la elección de índice.	Ejecútalo tras cargas masivas. Las estadísticas obsoletas provocan seq scans en columnas indexadas.
⤢Over-indexing	Cada índice añade sobrecoste de escritura. Los inserts, updates y deletes pagan por índice.	Elimina los índices sin uso. Sigue el uso en pg_stat_user_indexes.
⤢pg_stat_user_indexes	Vista de Postgres con el uso por índice. Cuenta scans y tuplas leídas desde el último reset de estadísticas.	Filtra por idx_scan = 0 para hallar índices sin uso. Reinicia los contadores con pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Construye un índice sin bloquear lecturas ni escrituras. Necesita dos recorridos de tabla.	No puede ejecutarse dentro de una transacción. Una construcción fallida deja un índice INVALID que eliminar.
⤢DROP INDEX	Elimina un índice y libera su almacenamiento. Toma un bloqueo exclusivo por defecto.	Usa DROP INDEX CONCURRENTLY en tablas concurridas. Consulta las estadísticas de uso antes de quitarlo.
⤢REINDEX	Reconstruye un índice hinchado. Recupera el espacio de las tuplas muertas.	Usa REINDEX CONCURRENTLY en producción. Sin él, los bloqueos impiden escrituras.
⤢Bloat	Tuplas muertas y huecos de página que se acumulan en tablas MVCC y sus índices.	Mídelo con pgstattuple. Vacuum con frecuencia; REINDEX cuando el espacio muerto domina.
⤢Fillfactor	Porcentaje de página empaquetado al escribir. El espacio libre absorbe updates futuros.	Bájalo a 70-90 en tablas con muchos updates. Las páginas llenas fuerzan divisiones de página.
⤢CLUSTER	Reescribe la tabla ordenada físicamente según un índice. Operación de una sola vez.	Combínalo con BRIN para bloques ordenados. El orden no se mantiene en escrituras futuras.