Funcionamiento y optimización de índices de bases de datos - Principios para acelerar consultas

22 min de lectura | 2025.12.02

Base de datosRendimientoPostgreSQLDiseño

La mayoría de los problemas de rendimiento en bases de datos pueden resolverse con un diseño adecuado de índices. Sin embargo, los índices no deben crearse “por si acaso”, sino diseñarse comprendiendo la estructura de datos y los patrones de consulta. En este artículo, explicaremos sistemáticamente desde la estructura interna de los índices hasta técnicas prácticas de optimización.

¿Qué es un índice?

Analogía con el índice de un libro

Un índice puede compararse con el “índice” al final de un libro.

flowchart LR
    subgraph NoIndex["Búsqueda sin índice"]
        A1["Escanear todas las páginas desde la primera"] --> A2["Complejidad temporal O(n)"]
    end

    subgraph WithIndex["Búsqueda con índice"]
        B1["Buscar palabra clave en el índice"] --> B2["Obtener número de página"] --> B3["Complejidad temporal O(log n)"]
    end

Funcionamiento básico del índice

-- Sin índice: Full Table Scan (escaneo completo)
SELECT * FROM users WHERE email = 'user@example.com';
-- Escanea 1 millón de filas

-- Con índice: Index Scan
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
-- Búsqueda rápida usando el índice

Tipos de índices

1. Índice B-Tree

La estructura de índice más común y el predeterminado en PostgreSQL.

flowchart TB
    R["[50]"] --> L["[25]"]
    R --> RI["[75]"]
    L --> LL["[10,20]"]
    L --> LR["[30,40]"]
    RI --> RL["[60,70]"]
    RI --> RR["[80,90]"]
    LL --> D1["Página de datos"]
    LR --> D2["Página de datos"]
    RL --> D3["Página de datos"]
    RR --> D4["Página de datos"]

Características:

  • Búsqueda de igualdad (=): O(log n)
  • Búsqueda por rango (<, >, BETWEEN): O(log n + m)
  • Acceso a datos ordenados: Eficiente
-- Operaciones adecuadas para B-Tree
SELECT * FROM orders WHERE created_at > '2025-12-01';  -- Búsqueda por rango
SELECT * FROM products ORDER BY price;                 -- Ordenamiento
SELECT * FROM users WHERE id = 12345;                  -- Búsqueda de igualdad

2. Índice Hash

Un índice especializado solo en búsquedas de igualdad.

flowchart LR
    Input["'user@example.com'"] --> Hash["hash() = 42"]

    subgraph Buckets["Array de buckets"]
        B0["[0] → NULL"]
        B1["[1] → NULL"]
        B42["[42] → (user@example.com, row_pointer)"]
        B99["[99] → NULL"]
    end

    Hash --> B42

Características:

  • Búsqueda de igualdad (=): O(1)
  • Búsqueda por rango: No disponible
  • Ordenamiento: No disponible
-- Índice Hash mejorado en PostgreSQL 10+
CREATE INDEX idx_users_email_hash ON users USING hash(email);

-- Operaciones adecuadas
SELECT * FROM users WHERE email = 'user@example.com';  -- ✓
-- Operaciones no adecuadas
SELECT * FROM users WHERE email LIKE 'user%';          -- ✗
SELECT * FROM users ORDER BY email;                    -- ✗

3. GIN (Generalized Inverted Index)

Índice adecuado para arrays, JSONB y búsqueda de texto completo.

-- Índice GIN para columna JSONB
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

-- Búsqueda eficiente
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- Columna de array
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
SELECT * FROM posts WHERE tags @> ARRAY['javascript', 'react'];

-- Búsqueda de texto completo
CREATE INDEX idx_articles_fts ON articles
USING gin(to_tsvector('japanese', title || ' ' || content));

4. GiST (Generalized Search Tree)

Índice adecuado para datos geoespaciales y tipos de rango.

-- Índice geoespacial PostGIS
CREATE INDEX idx_locations_geom ON locations USING gist(geom);

-- Búsqueda por rango
SELECT * FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(139.7, 35.6), 1000);  -- Dentro de 1km

-- Tipo de rango
CREATE INDEX idx_reservations_period ON reservations
USING gist(daterange(start_date, end_date));

Comparación de tipos de índices

ÍndiceBúsqueda igualdadBúsqueda rangoOrdenamientoUso
B-Tree◎◎◎General
Hash◎✗✗Solo igualdad
GIN◎△✗Arrays/JSONB/Texto completo
GiST○◎✗Geoespacial/Rangos
BRIN△◎△Grande escala/Series temporales

Índices compuestos

El orden de las columnas es importante

En los índices compuestos, el orden de las columnas afecta significativamente el rendimiento de las consultas.

-- Índice compuesto
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Consultas que usan este índice:
SELECT * FROM orders WHERE user_id = 123;                    -- ✓
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid'; -- ✓
SELECT * FROM orders WHERE user_id = 123 ORDER BY status;    -- ✓

-- Consultas que NO usan este índice:
SELECT * FROM orders WHERE status = 'paid';  -- ✗ Falta la columna inicial

flowchart TB
    Root["(user_id=100, status=active)"] --> Left["(user_id=50, status=*)"]
    Root --> Right["(user_id=150, status=*)"]
    Left --> LL["Hojas ordenadas"]
    Right --> RL["Hojas ordenadas"]

Flujo de búsqueda:

  1. Primero filtra por la columna inicial (user_id)
  2. Luego filtra por la siguiente columna (status)

Criterios para determinar el orden de columnas

-- Principio básico: Columnas con alta cardinalidad primero

-- Tabla users
-- user_id: 1 millón de valores distintos (alta cardinalidad)
-- status: 3 valores distintos (baja cardinalidad)
-- country: 200 valores distintos (cardinalidad media)

-- Recomendado: Alta cardinalidad → Baja cardinalidad
CREATE INDEX idx_users_composite ON users(user_id, country, status);

-- Sin embargo, también considera los patrones de consulta
-- Si WHERE status = 'active' es frecuente, puede valer poner status primero

Índice cubriente (cláusula INCLUDE)

-- Cláusula INCLUDE de PostgreSQL 11+
CREATE INDEX idx_orders_covering ON orders(user_id, status)
INCLUDE (total_amount, created_at);

-- Permite Index Only Scan
SELECT user_id, status, total_amount, created_at
FROM orders
WHERE user_id = 123 AND status = 'paid';

-- Se completa solo con el índice sin acceder a la tabla

Cómo leer el plan de ejecución

EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

Index Scan using idx_orders_user_status on orders
    (cost=0.43..8.45 rows=1 width=100)
    (actual time=0.025..0.026 rows=1 loops=1)
    Index Cond: ((user_id = 123) AND (status = 'paid'))
Planning Time: 0.150 ms
Execution Time: 0.050 ms

flowchart TB
    subgraph Cost["cost=0.43..8.45"]
        C1["0.43: Costo hasta devolver la primera fila<br/>(Costo de inicio)"]
        C2["8.45: Costo total para devolver todas las filas"]
    end

    subgraph Estimate["Valores estimados"]
        E1["rows=1: Número estimado de filas"]
        E2["width=100: Bytes estimados por fila"]
    end

    subgraph Actual["Valores reales"]
        A1["actual time=0.025..0.026: Tiempo real de ejecución (ms)"]
        A2["rows=1: Filas realmente devueltas"]
        A3["loops=1: Número de ejecuciones de esta operación"]
    end

Comparación de métodos de escaneo

-- Seq Scan (Escaneo secuencial): Escaneo completo
Seq Scan on orders (cost=0.00..18584.00 rows=100000 width=100)
  Filter: (status = 'paid')

-- Index Scan: Acceso a tabla usando índice
Index Scan using idx_orders_status on orders (cost=0.43..8.45 rows=1)
  Index Cond: (status = 'paid')

-- Index Only Scan: Se completa solo con el índice (más rápido)
Index Only Scan using idx_orders_covering on orders (cost=0.43..4.45 rows=1)
  Index Cond: (status = 'paid')

-- Bitmap Index Scan: Combinación de múltiples índices
Bitmap Heap Scan on orders (cost=5.00..100.00 rows=100)
  -> Bitmap Index Scan on idx_orders_status (cost=0.00..4.50 rows=100)
     Index Cond: (status = 'paid')

Mejores prácticas para el diseño de índices

1. Análisis de patrones de consulta

-- Verificar consultas frecuentes con pg_stat_statements
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

-- Identificar consultas lentas
SELECT query, mean_time, calls
FROM pg_stat_statements
WHERE mean_time > 100  -- Más de 100ms
ORDER BY mean_time DESC;

2. Monitoreo del uso de índices

-- Detectar índices no utilizados
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Detectar índices duplicados
SELECT
    a.indexrelid::regclass AS index1,
    b.indexrelid::regclass AS index2,
    a.indkey AS columns1,
    b.indkey AS columns2
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
    AND a.indexrelid < b.indexrelid
    AND a.indkey <@ b.indkey;

3. Índices parciales

Indexa solo datos que cumplen ciertas condiciones.

-- Indexar solo usuarios activos
CREATE INDEX idx_users_active ON users(email)
WHERE status = 'active';

-- Solo datos recientes
CREATE INDEX idx_orders_recent ON orders(created_at)
WHERE created_at > '2024-01-01';

-- Solo datos no NULL
CREATE INDEX idx_products_sku ON products(sku)
WHERE sku IS NOT NULL;

4. Índices de expresión

Indexa resultados de cálculos o funciones.

-- Búsqueda sin distinción de mayúsculas/minúsculas
CREATE INDEX idx_users_email_lower ON users(lower(email));
SELECT * FROM users WHERE lower(email) = 'user@example.com';

-- Parte de una fecha
CREATE INDEX idx_orders_year_month ON orders(
    date_trunc('month', created_at)
);
SELECT * FROM orders
WHERE date_trunc('month', created_at) = '2025-12-01';

-- Clave específica de JSONB
CREATE INDEX idx_users_settings_theme ON users((settings->>'theme'));
SELECT * FROM users WHERE settings->>'theme' = 'dark';

Antipatrones de índices

1. Exceso de índices

-- Antipatrón: Índice en todas las columnas
CREATE INDEX idx_users_id ON users(id);           -- Ya creado automáticamente para PK
CREATE INDEX idx_users_name ON users(name);       -- ¿Realmente necesario?
CREATE INDEX idx_users_email ON users(email);     -- ¿Realmente necesario?
CREATE INDEX idx_users_created ON users(created_at);
CREATE INDEX idx_users_updated ON users(updated_at);
CREATE INDEX idx_users_status ON users(status);

-- Problemas:
-- - INSERT/UPDATE/DELETE se vuelven más lentos
-- - Desperdicio de capacidad de almacenamiento
-- - Mayor carga en el procesamiento de VACUUM

2. Índices de baja selectividad

-- Antipatrón: Columna con pocos valores distintos
CREATE INDEX idx_users_gender ON users(gender);  -- Solo 3 tipos: masculino/femenino/otro

-- Problemas:
-- - Aún usando el índice, accede a muchas filas
-- - A menudo Seq Scan es más eficiente

-- Solución: Combinar con otras columnas
CREATE INDEX idx_users_gender_age ON users(gender, birth_date);

3. Funciones en cláusula WHERE

-- Antipatrón: El índice no se usa
SELECT * FROM users WHERE YEAR(created_at) = 2025;

-- Forma en que el índice se usa
SELECT * FROM users
WHERE created_at >= '2025-12-01'
  AND created_at < '2026-01-01';

-- O crear un índice de expresión
CREATE INDEX idx_users_created_year ON users(EXTRACT(YEAR FROM created_at));

4. Incompatibilidad de tipos

-- Antipatrón: Conversión de tipo implícita
-- user_id es tipo INTEGER
SELECT * FROM users WHERE user_id = '123';  -- Literal de cadena

-- Forma en que el índice se usa
SELECT * FROM users WHERE user_id = 123;    -- Literal entero

Ejemplo de ajuste de rendimiento

Caso de estudio: Búsqueda de pedidos en sitio de comercio electrónico

-- Consulta original (lenta)
SELECT o.*, u.name AS user_name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

-- Análisis del plan de ejecución
EXPLAIN ANALYZE ...
-- Seq Scan on orders: 10 segundos

-- Paso de optimización 1: Crear índice apropiado
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Paso de optimización 2: Índice parcial (solo pending)
CREATE INDEX idx_orders_pending ON orders(created_at DESC)
WHERE status = 'pending';

-- Paso de optimización 3: Índice cubriente
CREATE INDEX idx_orders_pending_covering ON orders(created_at DESC)
INCLUDE (user_id, total_amount)
WHERE status = 'pending';

-- Resultado: 10 segundos → 5ms

Mantenimiento de índices

REINDEX periódico

-- Reconstruir índice inflado
REINDEX INDEX idx_orders_status;

-- Todos los índices de una tabla
REINDEX TABLE orders;

-- PostgreSQL 12+: Opción CONCURRENTLY
REINDEX INDEX CONCURRENTLY idx_orders_status;

Verificar la inflación del índice

-- Verificar la tasa de inflación del índice
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Resumen

Los índices de bases de datos pueden lograr mejoras dramáticas en el rendimiento cuando se diseñan y gestionan adecuadamente.

Principios de diseño

  1. Prioridad a los patrones de consulta: Diseña basándote en consultas reales
  2. Índices compuestos: Considera la selectividad y patrones de consulta para el orden de columnas
  3. Índices parciales: Indexa solo los datos necesarios
  4. Evita el exceso de índices: Equilibra con el costo de escritura

Monitoreo y optimización

  1. EXPLAIN ANALYZE: Verificación periódica de planes de ejecución
  2. pg_stat_statements: Identificación de consultas lentas
  3. Índices no utilizados: Inventario periódico
  4. REINDEX: Reconstrucción de índices inflados

Los índices no son “diseñar y olvidar”, el monitoreo y la optimización continuos son importantes.

Enlaces de referencia

Artículos Recomendados

Noticias

Nuevas características de PostgreSQL 17 - Mejoras en rendimiento y usabilidad

PostgreSQLBase de datos
Tutoriales

Guia practica de Drizzle ORM - Aprovechando un ORM type-safe similar a SQL

Drizzle ORMTypeScript
Conceptos

Transacciones Distribuidas - Patrones de diseño para mantener la consistencia

DiseñoBase de datos
Tutoriales

Introduccion a PostgreSQL - Fundamentos de bases de datos relacionales

PostgreSQLBase de datos
Conceptos

Como Funciona la Cache - Clave para la Optimizacion del Rendimiento Web

RendimientoRedes
Conceptos

Como Funciona un CDN - Tecnologia para Acelerar la Entrega de Contenido

RedesRendimiento
← Volver a la lista