Día 7 – Índices y optimización: mejorar el rendimiento de tus consultas

Página de inicio

Día 7 – Índices y optimización: mejorar el rendimiento de tus consultas

¡Llegamos al último día del curso! 🎉 Hoy aprenderás a hacer que tus consultas SQL sean rápidas y eficientes.

Con pocas filas (10, 100, 1000) cualquier consulta es rápida. Pero cuando tienes millones de registros, una consulta mal optimizada puede tardar minutos u horas. Una consulta bien optimizada con índices adecuados puede tardar milisegundos.

Los índices son como el índice de un libro: en lugar de leer todo el libro (tabla completa) para encontrar algo, vas directo a la página correcta.

Escenario del día

Imagina que tu base de datos ha crecido:

1 millón de usuarios
10 millones de pedidos
50 millones de detalles de pedidos

De repente:

Buscar un usuario por email tarda 30 segundos
Listar pedidos de un usuario tarda 2 minutos
Generar un informe mensual se queda colgado

Sin índices adecuados, PostgreSQL tiene que leer toda la tabla para cada consulta. Con índices, puede ir directo a los datos que necesita.

1. ¿Qué es un índice?

Un índice es una estructura de datos adicional que acelera las búsquedas en una tabla.

Analogía: Biblioteca

Sin índice: Para encontrar un libro sobre «SQL», tienes que revisar libro por libro toda la biblioteca.

Con índice: Miras el catálogo (índice), que te dice exactamente en qué estante está «SQL».

Cómo funcionan los índices

PostgreSQL crea una estructura en árbol (B-tree) que organiza los datos
Cuando buscas algo, recorre el árbol (muy rápido) en lugar de escanear toda la tabla
Trade-off: Los índices aceleran las lecturas pero ralentizan las escrituras (INSERT, UPDATE, DELETE) porque hay que actualizar también el índice

2. Ver índices existentes

Por defecto, PostgreSQL crea automáticamente índices en:

Primary Keys (id)
Unique constraints (email)

-- Ver todos los índices de una tabla
SELECT 
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'usuarios'
ORDER BY indexname;

Resultado:

indexname	indexdef
usuarios_pkey	CREATE UNIQUE INDEX usuarios_pkey ON public.usuarios USING btree (id)
usuarios_email_key	CREATE UNIQUE INDEX usuarios_email_key ON public.usuarios USING btree (email)

3. Crear índices

Sintaxis básica

CREATE INDEX nombre_indice ON tabla (columna);

Ejemplo: Índice en columna frecuentemente buscada

-- Crear índice en país (buscamos usuarios por país a menudo)
CREATE INDEX idx_usuarios_pais ON usuarios (pais);

Verificar que se creó:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'usuarios'
AND indexname = 'idx_usuarios_pais';

Índice en múltiples columnas (compuesto)

Si frecuentemente filtras por dos columnas a la vez:

-- Índice compuesto en país y fecha de registro
CREATE INDEX idx_usuarios_pais_fecha ON usuarios (pais, fecha_registro);

Importante: El orden importa. Este índice es útil para:

✅ WHERE pais = 'España' AND fecha_registro > '2024-01-01'
✅ WHERE pais = 'España' (usa solo la primera columna)
❌ WHERE fecha_registro > '2024-01-01' (no usa el índice porque pais no está)

Índice UNIQUE

-- Evitar emails duplicados con índice
CREATE UNIQUE INDEX idx_usuarios_email_unique ON usuarios (email);

PostgreSQL ya crea esto automáticamente si defines la columna como UNIQUE.

Índice parcial (condicional)

Útil para indexar solo un subconjunto de filas:

-- Índice solo para productos en stock
CREATE INDEX idx_productos_stock ON productos (nombre)
WHERE stock > 0;

Esto es más eficiente que indexar toda la tabla si solo te interesan productos disponibles.

Índice en expresiones

-- Índice para búsquedas case-insensitive
CREATE INDEX idx_usuarios_nombre_lower ON usuarios (LOWER(nombre));

Ahora puedes buscar sin importar mayúsculas:

SELECT * FROM usuarios WHERE LOWER(nombre) = 'ana garcia';

4. EXPLAIN – Analizar consultas

EXPLAIN te muestra cómo PostgreSQL ejecuta una consulta.

Sintaxis básica

EXPLAIN SELECT * FROM usuarios WHERE pais = 'España';

Resultado:

Seq Scan on usuarios  (cost=0.00..1.12 rows=2 width=328)
  Filter: ((pais)::text = 'España'::text)

Interpretación:

Seq Scan = Sequential Scan = Escaneo secuencial (lee toda la tabla)
cost: Estimación de costo (mayor = más lento)
rows: Número estimado de filas que devolverá
Filter: Condición aplicada

EXPLAIN ANALYZE – Con ejecución real

EXPLAIN ANALYZE SELECT * FROM usuarios WHERE pais = 'España';

Resultado:

Seq Scan on usuarios  (cost=0.00..1.12 rows=2 width=328) (actual time=0.012..0.018 rows=4 loops=1)
  Filter: ((pais)::text = 'España'::text)
  Rows Removed by Filter: 6
Planning Time: 0.091 ms
Execution Time: 0.034 ms

Interpretación adicional:

actual time: Tiempo real de ejecución
rows=4: Devolvió 4 filas realmente
Execution Time: Tiempo total (0.034 ms = muy rápido)

Comparar sin índice vs con índice

-- Sin índice: Seq Scan
EXPLAIN ANALYZE SELECT * FROM usuarios WHERE pais = 'España';

-- Crear índice
CREATE INDEX idx_usuarios_pais ON usuarios (pais);

-- Con índice: Index Scan o Bitmap Index Scan
EXPLAIN ANALYZE SELECT * FROM usuarios WHERE pais = 'España';

Con índice verás algo como:

Bitmap Heap Scan on usuarios  (cost=4.19..11.32 rows=4 width=328)
  Recheck Cond: ((pais)::text = 'España'::text)
  ->  Bitmap Index Scan on idx_usuarios_pais  (cost=0.00..4.19 rows=4 width=0)
        Index Cond: ((pais)::text = 'España'::text)

Index Scan = Usa el índice (mucho más rápido en tablas grandes).

5. Tipos de escaneo (Scan types)

Sequential Scan (Seq Scan)

Lee toda la tabla fila por fila
Lento en tablas grandes
Rápido si devuelves muchas filas (>20% de la tabla)

Index Scan

Usa el índice para ir directo a las filas
Muy rápido para pocas filas
Accede a filas de forma aleatoria (puede ser lento si devuelve muchas)

Bitmap Index Scan

PostgreSQL usa el índice para crear un «mapa de bits» de filas
Luego lee las filas en orden secuencial (más eficiente)
Bueno para consultas que devuelven muchas filas

Index Only Scan

El índice contiene todas las columnas necesarias
No necesita leer la tabla en absoluto
El más rápido

-- Ejemplo: El índice tiene 'email', y solo pedimos 'email'
CREATE INDEX idx_usuarios_email ON usuarios (email);

-- Index Only Scan (muy rápido)
EXPLAIN ANALYZE SELECT email FROM usuarios WHERE email LIKE 'ana%';

6. Cuándo usar índices

✅ SÍ crear índice en:

Primary Keys (automático)
Foreign Keys (muy recomendado)
Columnas en WHERE frecuentes (país, categoría, estado)
Columnas en JOIN (usuario_id, producto_id)
Columnas en ORDER BY (fecha, nombre)
Columnas UNIQUE (email, username)

❌ NO crear índice en:

Tablas muy pequeñas (<1000 filas) → Seq Scan es más rápido
Columnas con poca variación (género con solo M/F → 50% de las filas)
Columnas que cambias constantemente (contador de visitas)
Tablas con muchas escrituras (logs, métricas en tiempo real)

Regla general

Si una columna aparece frecuentemente en WHERE, JOIN u ORDER BY → Considera un índice
Si una tabla tiene muchas lecturas y pocas escrituras → Más índices
Si una tabla tiene muchas escrituras → Menos índices

7. Índices en nuestras tablas de ejemplo

Índices recomendados para la base de datos del curso

-- Foreign Keys (muy importante)
CREATE INDEX idx_pedidos_usuario_id ON pedidos (usuario_id);
CREATE INDEX idx_detalle_pedidos_pedido_id ON detalle_pedidos (pedido_id);
CREATE INDEX idx_detalle_pedidos_producto_id ON detalle_pedidos (producto_id);

-- Búsquedas frecuentes
CREATE INDEX idx_usuarios_pais ON usuarios (pais);
CREATE INDEX idx_productos_categoria ON productos (categoria);
CREATE INDEX idx_pedidos_fecha ON pedidos (fecha);

-- Índices compuestos útiles
CREATE INDEX idx_productos_categoria_precio ON productos (categoria, precio);
CREATE INDEX idx_usuarios_pais_fecha ON usuarios (pais, fecha_registro);

Verificar mejora con EXPLAIN

-- ANTES del índice
EXPLAIN ANALYZE 
SELECT * FROM pedidos WHERE usuario_id = 1;
-- Seq Scan: 0.05 ms

-- Crear índice
CREATE INDEX idx_pedidos_usuario_id ON pedidos (usuario_id);

-- DESPUÉS del índice
EXPLAIN ANALYZE 
SELECT * FROM pedidos WHERE usuario_id = 1;
-- Index Scan: 0.02 ms (2x más rápido)

En tablas grandes, la diferencia puede ser de segundos vs milisegundos.

8. Mantenimiento de índices

Ver tamaño de índices

-- Tamaño de todos los índices
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Índices no utilizados

-- Índices que nunca se usan (candidatos para eliminar)
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_toast%'
ORDER BY pg_relation_size(indexrelid) DESC;

Eliminar índices

-- Eliminar índice no usado
DROP INDEX IF EXISTS idx_usuarios_fecha_registro;

REINDEX – Reconstruir índices

Con el tiempo, los índices pueden fragmentarse. Reconstruirlos puede mejorar el rendimiento:

-- Reconstruir un índice específico
REINDEX INDEX idx_usuarios_pais;

-- Reconstruir todos los índices de una tabla
REINDEX TABLE usuarios;

-- Reconstruir todos los índices de la base de datos (cuidado, puede tardar)
REINDEX DATABASE aprendiendo_sql;

9. Otras técnicas de optimización

VACUUM – Limpiar espacio

PostgreSQL no borra físicamente las filas eliminadas inmediatamente. VACUUM las limpia:

-- Analizar y limpiar una tabla
VACUUM ANALYZE usuarios;

-- Limpiar toda la base de datos
VACUUM;

VACUUM FULL (más agresivo, bloquea la tabla):

VACUUM FULL usuarios;

ANALYZE – Actualizar estadísticas

PostgreSQL usa estadísticas para decidir qué plan de ejecución usar. Actualízalas después de grandes cambios:

-- Actualizar estadísticas de una tabla
ANALYZE usuarios;

-- Toda la base de datos
ANALYZE;

Configuración de PostgreSQL

Algunos parámetros importantes (editables en postgresql.conf):

-- Ver configuración actual
SHOW shared_buffers;      -- Memoria para cachear datos
SHOW work_mem;            -- Memoria para operaciones (ORDER BY, JOIN)
SHOW effective_cache_size;  -- Estimación de memoria disponible total

Ajustes típicos para mejor rendimiento (requieren reinicio):

shared_buffers = 256MB         # 25% de la RAM
work_mem = 16MB                # Para operaciones grandes
effective_cache_size = 1GB     # 50-75% de la RAM

10. Optimización de consultas comunes

Problema: Consulta lenta con JOINs

-- Lenta: Sin índices en foreign keys
EXPLAIN ANALYZE
SELECT 
    u.nombre,
    COUNT(p.id) AS total_pedidos,
    SUM(p.total) AS gasto_total
FROM usuarios u
LEFT JOIN pedidos p ON u.id = p.usuario_id
GROUP BY u.id, u.nombre;

Solución:

-- Crear índice en foreign key
CREATE INDEX idx_pedidos_usuario_id ON pedidos (usuario_id);

-- Ahora será más rápido

Problema: ORDER BY lento

-- Lenta: Sin índice en fecha
EXPLAIN ANALYZE
SELECT * FROM pedidos ORDER BY fecha DESC LIMIT 10;

Solución:

-- Índice en columna de ORDER BY
CREATE INDEX idx_pedidos_fecha_desc ON pedidos (fecha DESC);

El DESC en el índice hace que ORDER BY DESC sea aún más rápido.

Problema: Búsquedas con LIKE

-- Muy lenta: LIKE no usa índices normales
SELECT * FROM usuarios WHERE nombre LIKE '%Garcia%';

Soluciones:

Usar índice trigram (para búsquedas de texto):

-- Habilitar extensión
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Crear índice GIN
CREATE INDEX idx_usuarios_nombre_trgm ON usuarios USING gin (nombre gin_trgm_ops);

-- Ahora LIKE será más rápido
SELECT * FROM usuarios WHERE nombre LIKE '%Garcia%';

Usar FULL TEXT SEARCH (para búsquedas más complejas):

-- Crear columna de búsqueda
ALTER TABLE usuarios ADD COLUMN nombre_search tsvector;

-- Actualizar con datos
UPDATE usuarios SET nombre_search = to_tsvector('spanish', nombre);

-- Crear índice GIN
CREATE INDEX idx_usuarios_nombre_search ON usuarios USING gin (nombre_search);

-- Buscar
SELECT * FROM usuarios WHERE nombre_search @@ to_tsquery('spanish', 'Garcia');

Problema: Subconsultas lentas

-- Lenta: Subconsulta correlacionada
SELECT nombre, (
    SELECT COUNT(*) FROM pedidos WHERE usuario_id = usuarios.id
) AS total_pedidos
FROM usuarios;

Solución: JOIN en lugar de subconsulta

-- Más rápida: JOIN
SELECT 
    u.nombre,
    COUNT(p.id) AS total_pedidos
FROM usuarios u
LEFT JOIN pedidos p ON u.id = p.usuario_id
GROUP BY u.id, u.nombre;

Problema: IN con muchos valores

-- Puede ser lenta
SELECT * FROM productos WHERE id IN (1, 2, 3, ..., 1000);

Solución: JOIN con tabla temporal

-- Crear tabla temporal con IDs
CREATE TEMP TABLE ids_buscar (id INTEGER);
INSERT INTO ids_buscar VALUES (1), (2), (3), ..., (1000);

-- JOIN en lugar de IN
SELECT p.*
FROM productos p
INNER JOIN ids_buscar i ON p.id = i.id;

11. Casos prácticos de optimización

Escenario 1: Dashboard de ventas lento

Problema: Consulta que genera el dashboard tarda 10 segundos.

-- Consulta original (lenta)
SELECT 
    pr.categoria,
    COUNT(dp.id) AS ventas,
    SUM(dp.cantidad * dp.precio_unitario) AS ingresos
FROM productos pr
INNER JOIN detalle_pedidos dp ON pr.id = dp.producto_id
INNER JOIN pedidos p ON dp.pedido_id = p.id
WHERE p.fecha BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY pr.categoria;

EXPLAIN ANALYZE muestra:

Seq Scan en detalle_pedidos (10 millones de filas)
Sin índices en foreign keys

Solución:

-- Índices en foreign keys
CREATE INDEX idx_detalle_pedidos_pedido_id ON detalle_pedidos (pedido_id);
CREATE INDEX idx_detalle_pedidos_producto_id ON detalle_pedidos (producto_id);
CREATE INDEX idx_pedidos_fecha ON pedidos (fecha);

-- Ahora tarda 0.5 segundos (20x más rápido)

Escenario 2: Búsqueda de usuarios lenta

Problema: Buscar usuarios por email tarda mucho.

-- Sin índice: 5 segundos en 1M de usuarios
SELECT * FROM usuarios WHERE email = 'juan.martinez@email.com';

Solución:

-- Email ya debería tener UNIQUE (índice automático)
-- Si no lo tiene:
CREATE UNIQUE INDEX idx_usuarios_email ON usuarios (email);

-- Ahora: 0.001 segundos

Escenario 3: Informe mensual muy lento

Problema: Generar informe de ventas mensuales tarda 2 minutos.

-- Lenta: Múltiples JOINs sin optimizar
SELECT 
    DATE_TRUNC('month', p.fecha) AS mes,
    u.pais,
    pr.categoria,
    SUM(dp.cantidad) AS unidades,
    SUM(dp.cantidad * dp.precio_unitario) AS ingresos
FROM pedidos p
JOIN usuarios u ON p.usuario_id = u.id
JOIN detalle_pedidos dp ON p.id = dp.pedido_id
JOIN productos pr ON dp.producto_id = pr.id
WHERE p.fecha >= '2024-01-01'
GROUP BY mes, u.pais, pr.categoria
ORDER BY mes, ingresos DESC;

Solución:

-- 1. Índices necesarios
CREATE INDEX idx_pedidos_fecha ON pedidos (fecha);
CREATE INDEX idx_pedidos_usuario_id ON pedidos (usuario_id);
CREATE INDEX idx_detalle_pedidos_pedido_id ON detalle_pedidos (pedido_id);
CREATE INDEX idx_detalle_pedidos_producto_id ON detalle_pedidos (producto_id);
CREATE INDEX idx_usuarios_pais ON usuarios (pais);
CREATE INDEX idx_productos_categoria ON productos (categoria);

-- 2. VACUUM y ANALYZE
VACUUM ANALYZE pedidos;
VACUUM ANALYZE detalle_pedidos;
VACUUM ANALYZE usuarios;
VACUUM ANALYZE productos;

-- Ahora: 3 segundos (40x más rápido)

12. Herramientas de monitoreo

Ver consultas lentas

-- Habilitar logging de consultas lentas (requiere permisos)
-- En postgresql.conf o con ALTER SYSTEM:
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries > 1 segundo
SELECT pg_reload_conf();

-- Ver estadísticas de consultas
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Extensión pg_stat_statements

-- Habilitar extensión (si no está)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Ver consultas más lentas
SELECT 
    substring(query, 1, 50) AS query_short,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_time_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_time_ms,
    ROUND((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

13. Checklist de optimización

Cuando una consulta es lenta, sigue estos pasos:

Paso 1: Identifica el problema

EXPLAIN ANALYZE tu_consulta_lenta;

Busca:

Seq Scan en tablas grandes → Necesitas índice
Alto cost → Consulta ineficiente
Rows muy diferentes entre estimado y real → Necesitas ANALYZE

Paso 2: Crea índices necesarios

-- Foreign keys (casi siempre)
CREATE INDEX idx_tabla_fk ON tabla (columna_fk);

-- Columnas en WHERE
CREATE INDEX idx_tabla_where ON tabla (columna_where);

-- Columnas en JOIN
CREATE INDEX idx_tabla_join ON tabla (columna_join);

Paso 3: Actualiza estadísticas

ANALYZE tabla;

Paso 4: Reescribe la consulta si es necesario

Evita SELECT *
Usa JOINs en lugar de subconsultas correlacionadas
Limita resultados con LIMIT cuando sea posible
Considera materializar resultados en tabla temporal para consultas complejas

Paso 5: Verifica la mejora

EXPLAIN ANALYZE tu_consulta_optimizada;

Compara el tiempo de ejecución antes y después.

14. Errores comunes

Error 1: Crear índices en todo

-- ❌ MAL: Demasiados índices
CREATE INDEX idx1 ON productos (nombre);
CREATE INDEX idx2 ON productos (precio);
CREATE INDEX idx3 ON productos (stock);
CREATE INDEX idx4 ON productos (categoria);
-- ... 10 índices más

Problema: Cada índice ralentiza INSERT, UPDATE, DELETE. Solo crea índices que realmente necesitas.

Error 2: Índice en columna de baja cardinalidad

-- ❌ Poco útil: Solo hay 2 valores posibles
CREATE INDEX idx_productos_disponible ON productos (disponible);  -- TRUE/FALSE

Regla: Si una columna tiene menos del 10% de valores únicos, probablemente no vale la pena indexarla.

Error 3: No actualizar estadísticas

-- Insertas 1 millón de filas
INSERT INTO productos SELECT ...;

-- Consultas lentas porque PostgreSQL usa estadísticas viejas
-- ✅ Solución:
ANALYZE productos;

Error 4: Olvidar índices en foreign keys

-- Tablas relacionadas sin índice en FK = JOINs lentos
CREATE TABLE pedidos (
    usuario_id INTEGER REFERENCES usuarios(id)  -- Sin índice!
);

-- ✅ SIEMPRE crea índice en foreign keys:
CREATE INDEX idx_pedidos_usuario_id ON pedidos (usuario_id);

15. Ejercicios prácticos

Analiza la consulta que lista usuarios de España con EXPLAIN ANALYZE
Crea índices en todas las foreign keys de la base de datos
Compara rendimiento antes y después de crear un índice en productos.categoria
Identifica índices no usados en tu base de datos
Optimiza una consulta que une las 4 tablas (usuarios, pedidos, detalle_pedidos, productos)
Crea un índice compuesto en (categoria, precio) y verifica su uso
Ejecuta VACUUM ANALYZE en todas las tablas
Mide el tamaño de todos los índices de tu base de datos
Reescribe una consulta lenta usando CTE en lugar de subconsultas
Crea un índice parcial para productos en stock > 0

16. Soluciones

Solución Ejercicio 1

EXPLAIN ANALYZE
SELECT * FROM usuarios WHERE pais = 'España';

Observa si usa Seq Scan o Index Scan.

Solución Ejercicio 2

-- Índices en foreign keys
CREATE INDEX IF NOT EXISTS idx_pedidos_usuario_id ON pedidos (usuario_id);
CREATE INDEX IF NOT EXISTS idx_detalle_pedidos_pedido_id ON detalle_pedidos (pedido_id);
CREATE INDEX IF NOT EXISTS idx_detalle_pedidos_producto_id ON detalle_pedidos (producto_id);

Solución Ejercicio 3

-- Antes del índice
EXPLAIN ANALYZE
SELECT * FROM productos WHERE categoria = 'Electronica';
-- Nota el tiempo de ejecución

-- Crear índice
CREATE INDEX idx_productos_categoria ON productos (categoria);

-- Después del índice
EXPLAIN ANALYZE
SELECT * FROM productos WHERE categoria = 'Electronica';
-- Compara el tiempo

Solución Ejercicio 4

-- Índices no usados
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Solución Ejercicio 5

-- Consulta a optimizar
EXPLAIN ANALYZE
SELECT 
    u.nombre,
    p.fecha,
    pr.nombre AS producto,
    dp.cantidad
FROM usuarios u
JOIN pedidos p ON u.id = p.usuario_id
JOIN detalle_pedidos dp ON p.id = dp.pedido_id
JOIN productos pr ON dp.producto_id = pr.id
WHERE u.pais = 'España';

-- Índices necesarios (si no existen)
CREATE INDEX IF NOT EXISTS idx_usuarios_pais ON usuarios (pais);
CREATE INDEX IF NOT EXISTS idx_pedidos_usuario_id ON pedidos (usuario_id);
CREATE INDEX IF NOT EXISTS idx_detalle_pedidos_pedido_id ON detalle_pedidos (pedido_id);
CREATE INDEX IF NOT EXISTS idx_detalle_pedidos_producto_id ON detalle_pedidos (producto_id);

-- Verificar mejora
EXPLAIN ANALYZE
SELECT 
    u.nombre,
    p.fecha,
    pr.nombre AS producto,
    dp.cantidad
FROM usuarios u
JOIN pedidos p ON u.id = p.usuario_id
JOIN detalle_pedidos dp ON p.id = dp.pedido_id
JOIN productos pr ON dp.producto_id = pr.id
WHERE u.pais = 'España';

Solución Ejercicio 6

-- Crear índice compuesto
CREATE INDEX idx_productos_cat_precio ON productos (categoria, precio);

-- Probar con consulta que usa ambas columnas
EXPLAIN ANALYZE
SELECT nombre, precio
FROM productos
WHERE categoria = 'Electronica' AND precio > 500;
-- Debería usar el índice compuesto

Solución Ejercicio 7

-- Limpiar y actualizar estadísticas
VACUUM ANALYZE usuarios;
VACUUM ANALYZE productos;
VACUUM ANALYZE pedidos;
VACUUM ANALYZE detalle_pedidos;

Solución Ejercicio 8

-- Tamaño de índices por tabla
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Tamaño total de índices
SELECT 
    pg_size_pretty(SUM(pg_relation_size(indexrelid))) AS total_index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public';

Solución Ejercicio 9

-- Subconsulta lenta
SELECT 
    nombre,
    (SELECT COUNT(*) FROM pedidos WHERE usuario_id = usuarios.id) AS pedidos
FROM usuarios;

-- Reescrita con CTE (más rápida)
WITH conteo_pedidos AS (
    SELECT 
        usuario_id,
        COUNT(*) AS total_pedidos
    FROM pedidos
    GROUP BY usuario_id
)
SELECT 
    u.nombre,
    COALESCE(cp.total_pedidos, 0) AS pedidos
FROM usuarios u
LEFT JOIN conteo_pedidos cp ON u.id = cp.usuario_id;

Solución Ejercicio 10

-- Índice parcial solo para productos en stock
CREATE INDEX idx_productos_stock_disponible 
ON productos (nombre, categoria)
WHERE stock > 0;

-- Probar que se usa
EXPLAIN ANALYZE
SELECT nombre, categoria, stock
FROM productos
WHERE stock > 0 AND categoria = 'Electronica';

Resumen del Día 7 (y del curso completo)

¡Felicidades! 🎉 Has completado el Mini Tutorial de SQL en 7 Días.

Día 7 – Has aprendido:

✅ Qué son los índices y cómo funcionan
✅ Crear índices (simples, compuestos, parciales, de expresiones)
✅ EXPLAIN y EXPLAIN ANALYZE → Analizar planes de ejecución
✅ Tipos de escaneo (Seq Scan, Index Scan, Bitmap Scan, Index Only Scan)
✅ Cuándo usar índices y cuándo no
✅ Mantenimiento (VACUUM, ANALYZE, REINDEX)
✅ Optimización de consultas comunes
✅ Monitoreo de rendimiento

🎓 Lo que has aprendido en 7 días

Día 1 – Fundamentos

SELECT, FROM, WHERE, ORDER BY, LIMIT

Día 2 – Filtros avanzados

AND, OR, IN, BETWEEN, LIKE, NULL, DISTINCT

Día 3 – Agregaciones

COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING

Día 4 – JOINs

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN

Día 5 – Subconsultas y CTEs

Subconsultas, CTEs, Window Functions, Consultas recursivas

Día 6 – Modificación de datos

INSERT, UPDATE, DELETE, Transacciones, RETURNING

Día 7 – Optimización

Índices, EXPLAIN, Optimización de consultas, Mantenimiento

🚀 Siguientes pasos

Ahora que dominas SQL, puedes:

Practicar con datos reales → Importa datasets de Kaggle o datos públicos
Crear proyectos → API REST con base de datos, dashboard de analytics
Aprender SQL avanzado → Particionamiento, triggers, stored procedures
Explorar otros SGBD → MySQL, SQL Server, SQLite (sintaxis similar)
Integrar con código → Python (SQLAlchemy), Node.js (Sequelize), etc.

Recursos adicionales

Documentación PostgreSQL: https://www.postgresql.org/docs/
PostgreSQL Tutorial: https://www.postgresqltutorial.com/
SQL Practice: https://www.hackerrank.com/domains/sql
Use The Index, Luke: https://use-the-index-luke.com/ (guía de índices)

¡Felicidades!

Has completado el Mini Tutorial de SQL en 7 Días

Ahora tienes las habilidades fundamentales para:

✅ Consultar datos con confianza
✅ Generar informes y análisis
✅ Modificar datos de forma segura
✅ Optimizar el rendimiento de tu base de datos

SQL es una habilidad que te acompañará toda tu carrera profesional. Prácticamente cualquier aplicación moderna usa bases de datos, y SQL es el lenguaje universal para trabajar con ellas.

¡Gracias por completar este curso!

Página de inicio