Índices en PostgreSQL: B-tree, GIN y cuándo usarlos

1. Descripción general

PostgreSQL admite muchos tipos de índices, pero B-tree y GIN son los dos tipos que encuentras más frecuentemente en tu trabajo diario. Elegir el tipo de índice incorrecto hace que las consultas sean lentas o el índice consuma espacio sin proporcionar ningún beneficio.

Este artículo explica cómo funcionan B-tree y GIN a un nivel práctico, cómo crear índices correctamente, cómo leer EXPLAIN para saber si se está usando el índice, y una lista de verificación para elegir el tipo de índice apropiado. Después de leerlo, podrás añadir índices a las tablas Order, Product con confianza, sin necesidad de adivinar.

2. Índice B-tree: el predeterminado y más común

2.1. Cómo funciona B-tree

B-tree (árbol balanceado) almacena valores en orden, permitiendo búsquedas, comparaciones (<, >, BETWEEN) y ordenamiento (ORDER BY) muy rápido — complejidad O(log n). Este es el tipo de índice predeterminado cuando ejecutas CREATE INDEX sin especificar el método.

CREATE TABLE customer_order (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount NUMERIC(12,2)
);

CREATE INDEX idx_order_customer_id ON customer_order (customer_id);

La línea CREATE INDEX crea un B-tree en la columna customer_id. Cuando consultas WHERE customer_id = 123, PostgreSQL recorre el árbol en lugar de escanear toda la tabla (sequential scan).

2.2. Verificar si se está usando el índice

EXPLAIN ANALYZE
SELECT * FROM customer_order WHERE customer_id = 123;

Index Scan using idx_order_customer_id on customer_order
  Index Cond: (customer_id = 123)
  Planning Time: 0.123 ms
  Execution Time: 0.045 ms

Index Scan significa que el planner eligió usar el índice. Si ves Seq Scan a pesar de tener un índice, generalmente es porque la tabla es demasiado pequeña (el planner ve que escanear toda la tabla es más barato) o las estadísticas están desactualizadas — ejecuta ANALYZE customer_order; para actualizar.

2.3. Índice compuesto y orden de columnas

B-tree admite múltiples columnas, pero el orden de las columnas determina la eficiencia.

CREATE INDEX idx_order_customer_status
    ON customer_order (customer_id, status);

-- Usa el índice completamente
SELECT * FROM customer_order
WHERE customer_id = 123 AND status = 'PAID';

-- Solo usa la parte customer_id del índice
SELECT * FROM customer_order WHERE customer_id = 123;

-- NO puede usar este índice (falta customer_id)
SELECT * FROM customer_order WHERE status = 'PAID';

Principio: un índice compuesto es como una libreta de direcciones ordenada por (customer_id, status) — puedes buscar por customer_id primero, pero no puedes buscar directamente solo por status.

Nota: Coloca primero la columna con alta selectividad (muchos valores distintos) si las columnas se usan independientemente en las consultas. Si siempre consultas ambas columnas juntas, coloca primero la columna más selectiva para que el árbol reduzca el rango rápidamente.

3. Índice GIN: para datos con “múltiples valores en una fila”

3.1. Cuándo B-tree es insuficiente

B-tree no es eficiente con tipos de datos donde una fila contiene múltiples “elementos” que necesitan búsquedas independientes: array, JSON (jsonb), búsqueda de texto completo (tsvector). GIN (Índice Invertido Generalizado) fue creado para este caso — indexa cada elemento por separado, no el valor completo.

3.2. GIN para JSONB

CREATE TABLE product (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSONB NOT NULL
);

CREATE INDEX idx_product_attributes ON product USING GIN (attributes);

INSERT INTO product (name, attributes) VALUES
('Laptop Dell', '{"brand": "Dell", "ram_gb": 16, "tags": ["laptop", "office"]}'),
('Laptop HP',   '{"brand": "HP", "ram_gb": 8, "tags": ["laptop", "gaming"]}');

SELECT * FROM product WHERE attributes @> '{"brand": "Dell"}';
SELECT * FROM product WHERE attributes -> 'tags' @> '["gaming"]';

USING GIN (attributes) crea un índice sobre toda la columna JSONB. El operador @> (“contains”) es lo que GIN optimiza mejor — verifica si el JSON izquierdo contiene la estructura derecha, sin necesidad de volver a analizar todo el documento cada vez.

Bitmap Heap Scan on product
  Recheck Cond: (attributes @> '{"brand": "Dell"}'::jsonb)
  ->  Bitmap Index Scan on idx_product_attributes
        Index Cond: (attributes @> '{"brand": "Dell"}'::jsonb)

3.3. GIN para búsqueda de texto completo

ALTER TABLE product ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED;

CREATE INDEX idx_product_search ON product USING GIN (search_vector);

SELECT name FROM product
WHERE search_vector @@ to_tsquery('simple', 'laptop & dell');

La columna search_vector es una columna generada que produce automáticamente tsvector a partir de name. El índice GIN en esta columna ayuda a @@ (match) a ejecutarse rápidamente con datos de texto grandes, en lugar de LIKE '%laptop%' que debe escanear toda la tabla.

3.4. GIN para array

CREATE TABLE article (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200),
    tag_list TEXT[]
);

CREATE INDEX idx_article_tags ON article USING GIN (tag_list);

SELECT * FROM article WHERE tag_list @> ARRAY['spring-boot'];

Similar a JSONB, GIN indexa cada elemento en el array tag_list, por lo que consultas como “artículos que contienen etiqueta X” son mucho más rápidas que B-tree con tablas grandes.

4. Comparación y costo

Criterio	B-tree	GIN
Datos apropiados	scalar: números, strings, fechas	array, jsonb, tsvector
Operadores soportados	=, <, >, BETWEEN, ORDER BY	@>, ?, @@, &&
Velocidad de escritura (INSERT/UPDATE)	rápida	más lenta, especialmente UPDATE en JSONB grande
Tamaño del índice	más pequeño	más grande (almacena cada elemento)
Velocidad de construcción del índice	rápida	más lenta en tablas grandes

GIN es más costoso en operaciones de escritura porque cada INSERT/UPDATE debe actualizar múltiples entradas (una para cada elemento en JSON/array), no solo una como en B-tree.

-- Verifica el tamaño real del índice
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'product';

5. Pruebas para confirmar que se usa el índice (integración con Spring Boot)

Cuando trabajas con Spring Data JPA, generalmente quieres asegurar que el índice funciona correctamente antes de desplegar. Usa Testcontainers para probar contra PostgreSQL real.

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.1</version>
    <scope>test</scope>
</dependency>

@Testcontainers
@SpringBootTest
class ProductIndexTest {

    @Container
    static PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldUseGinIndexForJsonbQuery() {
        jdbcTemplate.execute("""
            INSERT INTO product (name, attributes)
            VALUES ('Laptop Dell', '{"brand": "Dell"}')
        """);

        String plan = jdbcTemplate.queryForList(
                "EXPLAIN SELECT * FROM product WHERE attributes @> '{\"brand\": \"Dell\"}'"
        ).stream()
         .map(row -> row.get("QUERY PLAN").toString())
         .reduce("", (a, b) -> a + b);

        assertThat(plan).containsIgnoringCase("idx_product_attributes");
    }
}

Esta prueba ejecuta EXPLAIN (sin necesidad de ANALYZE), agrupa las líneas de resultado en una cadena, y luego verifica si el plan menciona el nombre del índice esperado. Esta es la manera de confirmar que el índice realmente participa en el plan de consulta, no solo “existe en el esquema”.

Nota: EXPLAIN no ejecuta la consulta realmente, solo interroga al planner. Si quieres saber el tiempo de ejecución real, usa EXPLAIN ANALYZE — pero recuerda que ejecuta la declaración, evita usarlo con DELETE/UPDATE a menos que lo envuelvas en una transacción con rollback.

6. Checklist para elegir el tipo de índice

• Consultas que filtran columnas escalares (números, strings, fechas) con =, <, >, o requieren ORDER BY → B-tree.
• Consultas que filtran múltiples columnas simultáneamente con lógica “AND” → B-tree compuesto, coloca primero la columna con alta selectividad o la columna usada independientemente.
• Consultas sobre columnas JSONB usando @>, ?, ?&, ?| → GIN.
• Búsqueda de texto completo (@@ con tsvector) → GIN.
• Consultas sobre arrays usando @>, &&, <@ → GIN.
• Tabla con muchas escrituras, pocas lecturas, datos JSONB que cambian frecuentemente → considera no crear un índice GIN en toda la columna, solo en la expresión específica (expression index) que necesitas.

-- Índice de expresión: indexa solo una clave específica en JSONB, más ligero que GIN en toda la columna
CREATE INDEX idx_product_brand ON product ((attributes ->> 'brand'));

Este índice de expresión es en realidad un B-tree sobre el resultado de attributes ->> 'brand' (valor texto), apropiado cuando siempre consultas exactamente la misma clave fija, sin necesidad de la flexibilidad de GIN.

7. Errores comunes

• Crear índices en columnas con baja cardinalidad (por ejemplo, status solo tiene 3 valores en una tabla de millones de filas): el planner suele ignorar el índice, eligiendo Seq Scan porque es más barato. No es verdad que si existe un índice, se use.
• Olvidar ANALYZE después de insertar datos grandes: el planner se basa en estadísticas antiguas, estima mal el número de filas, elige un plan deficiente.
• Usar GIN en columnas con pocos cambios pero muchas escrituras pequeñas: el costo acumulado de actualizar el índice es mayor que el beneficio de las consultas — mide con EXPLAIN ANALYZE antes de decidir.
• B-tree compuesto con orden incorrecto de columnas: poner primero la columna menos usada hace que el índice sea casi inútil para las consultas reales.
• Crear demasiados índices en una tabla: cada INSERT/UPDATE debe actualizar todos los índices relacionados, ralentizando la ruta de escritura mientras no se usan todos esos índices.

8. Conclusión

B-tree es la opción predeterminada, adecuada para la mayoría de filtros y ordenamientos en datos escalares. GIN está reservado para datos con “múltiples valores” como JSONB, array, full-text — donde B-tree no puede representar eficientemente.

Antes de añadir un índice, siempre verifica con EXPLAIN ANALYZE si el planner realmente lo usa, y mide el costo de escritura si la tabla tiene una alta frecuencia de INSERT/UPDATE. Los índices no son “añadidos por seguridad” — cada índice es un compromiso entre velocidad de lectura y velocidad de escritura.