CREATE INDEX name ON table (column); crea un índice que permite a PostgreSQL encontrar las filas que coinciden sin leer toda la tabla. El tipo por defecto es B-tree, que sirve para =, <, >, BETWEEN, IN, IS NULL y ORDER BY. Elige otro tipo con USING: gin, gist, spgist, brin o hash.
La primera línea del plan dice Index Scan using users_email_idx on users: PostgreSQL busca el valor en el índice y lee solo la fila que coincide. Quita la línea CREATE INDEX y vuelve a ejecutarlo para ver Seq Scan on users, que lee las 10.000 filas.
En una tabla de unas pocas decenas de filas, el planificador suele elegir un recorrido secuencial aunque exista un índice: leer unas pocas páginas es más barato. EXPLAIN ANALYZE explica cómo leer estos planes.
Elegir un tipo de índice
| Tipo | Úsalo para |
|---|---|
| B-tree (por defecto) | igualdad, rangos, ordenación, LIKE 'prefix%' (con la collation C o text_pattern_ops) |
| GIN | valores con muchas partes: jsonb (@>, ?), arrays (@>, &&), texto completo tsvector |
| GiST | geometría, rangos (&&, solapamientos), texto completo, vecino más cercano ORDER BY ... <-> |
| SP-GiST | datos que se dividen en regiones sin solapamiento: puntos, direcciones IP, prefijos de texto |
| BRIN | tablas muy grandes cuyos valores siguen el orden físico, como un created_at que solo crece |
| Hash | solo igualdad; un B-tree hace lo mismo y más, así que casi nunca se elige |
Un índice GIN sobre una columna jsonb hace que las consultas de contención usen el índice:
El plan muestra un Bitmap Index Scan sobre el índice GIN, que alimenta un Bitmap Heap Scan que lee las filas que coinciden. La página de JSONB explica sus operadores.
Indexar varias columnas
Un índice B-tree de varias columnas ordena por la primera columna, y luego por la segunda dentro de cada valor de la primera. Sirve para consultas que filtran por la columna inicial, o por la inicial y las siguientes. Un filtro solo por la segunda columna apenas gana nada, así que pon primero la columna que siempre filtras con =.
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;
INCLUDE (PostgreSQL 11+) guarda columnas extra en el índice sin ordenar por ellas: CREATE INDEX ON orders (customer_id) INCLUDE (total);. Una consulta que solo lee esas columnas puede responderse entonces solo con el índice, un Index Only Scan.
Índices únicos y parciales: aplicar una regla a algunas filas
CREATE UNIQUE INDEX rechaza valores duplicados. Un índice parcial añade un WHERE e indexa solo las filas que lo cumplen. Es más pequeño y puede hacer que una regla de unicidad se aplique solo a un subconjunto de filas:
Se aceptan las dos filas porque solo una de ellas no está borrada. Una tercera fila activa con el mismo email falla con duplicate key value violates unique constraint "accounts_email_live". Una consulta usa un índice parcial solo cuando su WHERE implica la condición del índice, aquí WHERE NOT deleted.
Indexar una expresión
Un índice puede guardar el resultado de una expresión. La consulta tiene que repetir la misma expresión para usarlo, y un índice sobre email no ayuda a WHERE lower(email) = ...:
El plan usa users_email_lower_idx porque el WHERE repite lower(email) exactamente. La expresión debe ser inmutable, así que now() o una función que dependa de un ajuste se rechazan.
Listar, crear y eliminar índices en una base de datos en uso
pg_indexes lista todos los índices con la sentencia que los volvería a crear:
La clave primaria y la restricción UNIQUE crearon sus propios índices, users_pkey y users_email_key. En psql, \d users muestra la misma lista bajo las columnas de la tabla.
En una tabla que recibe escrituras, crea y elimina índices con CONCURRENTLY para no bloquearlas. Estas formas no pueden ejecutarse dentro de un bloque de transacción. Una creación concurrente fallida deja un índice INVALID, que tienes que eliminar y volver a crear:
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx; -- rebuild a bloated index
Preguntas frecuentes
¿PostgreSQL crea índices automáticamente?
Solo para restricciones PRIMARY KEY, UNIQUE y de exclusión. Una columna de clave foránea no recibe índice, así que añádelo tú cuando hagas joins por ella o borres filas de la tabla referenciada.
¿Por qué PostgreSQL no usa mi índice?
Razones habituales: la tabla es pequeña y leerla entera es más barato; la consulta devuelve una parte grande de las filas; el WHERE envuelve la columna en una función o conversión que el índice no tiene; o las estadísticas están desactualizadas (ejecuta ANALYZE). EXPLAIN muestra qué plan se eligió.
¿Cuántos índices son demasiados?
No hay un número fijo. Cada índice acelera algunas lecturas y ralentiza cada INSERT y la mayoría de los UPDATEs de la tabla, y ocupa espacio en disco. pg_stat_user_indexes.idx_scan muestra cuántas veces se usa cada índice, así puedes eliminar los que siguen en cero.
¿CREATE INDEX bloquea la tabla?
Un CREATE INDEX normal bloquea INSERT, UPDATE y DELETE en la tabla hasta que termina, aunque las lecturas siguen. CREATE INDEX CONCURRENTLY permite escrituras durante la creación, tarda más y no puede ejecutarse dentro de un bloque de transacción.