19. SQL, modelado de datos e índices

-- 1) NULL no es igual a NULL: el resultado es NULL, que NO es TRUE
SELECT NULL = NULL          AS igual,          -- NULL
       NULL <> NULL         AS distinto,       -- NULL
       NULL IS NULL         AS es_nulo,        -- true
       NULL IS NOT DISTINCT FROM NULL AS seguro;  -- true  <- comparación "null-safe"

-- 2) Aritmética y concatenación: NULL contamina toda la expresión
SELECT 100 + NULL           AS suma,           -- NULL
       'hola ' || NULL      AS concat,         -- NULL
       concat('hola ', NULL) AS concat_fn,     -- 'hola '  <- concat() ignora nulos
       coalesce(NULL, NULL, 'valor por defecto') AS con_coalesce;

-- 3) La trampa de NOT IN. Esta consulta NUNCA devuelve filas:
SELECT 3 IN     (1, 2, NULL) AS dentro,        -- NULL (no false)
       3 NOT IN (1, 2, NULL) AS fuera;         -- NULL (no true)
-- Porque 3 NOT IN (1,2,NULL) equivale a 3<>1 AND 3<>2 AND 3<>NULL
--                                     = TRUE  AND TRUE  AND UNKNOWN = UNKNOWN

-- 4) Los agregados IGNORAN los nulos, salvo count(*)
-- tasks: 5 filas, de las cuales 2 tienen assignee_id NULL y 1 estimate_hours NULL
SELECT count(*)              AS filas,         -- 5  cuenta filas
       count(assignee_id)    AS asignadas,     -- 3  cuenta valores no nulos
       count(DISTINCT assignee_id) AS personas,-- 2
       avg(estimate_hours)   AS media,         -- media de 4 valores, no de 5
       sum(estimate_hours)   AS total;         -- NULL si TODAS fueran nulas

-- 5) sum() sobre cero filas devuelve NULL, no 0. Protégelo siempre:
SELECT coalesce(sum(estimate_hours), 0) AS total_seguro
FROM   tasks WHERE project_id = -1;            -- 0

-- 6) DISTINCT y GROUP BY sí tratan los nulos como iguales entre sí
SELECT DISTINCT assignee_id FROM tasks;        -- devuelve UNA fila con NULL

-- 7) UNIQUE permite varios NULL (son "distintos" para el índice)
--    Desde PostgreSQL 15 se puede cambiar: UNIQUE NULLS NOT DISTINCT

-- 8) La ordenación: en PostgreSQL los nulos van al final en ASC
SELECT title, due_at FROM tasks ORDER BY due_at ASC NULLS LAST;

-- Pregunta: proyectos del equipo 1 que tienen al menos una tarea urgente (priority = 1).

-- (a) Semi-join explícito con EXISTS: la forma más clara y la más eficiente.
SELECT p.id, p.name
FROM   projects p
WHERE  p.team_id = 1
  AND  EXISTS (SELECT 1 FROM tasks t WHERE t.project_id = p.id AND t.priority = 1);

-- (b) Con IN: equivalente aquí porque tasks.project_id es NOT NULL.
SELECT p.id, p.name
FROM   projects p
WHERE  p.team_id = 1
  AND  p.id IN (SELECT t.project_id FROM tasks t WHERE t.priority = 1);

-- (c) Con JOIN: cuidado, DUPLICA proyectos (uno por tarea urgente).
--     Hay que añadir DISTINCT, lo que obliga al motor a ordenar o a agrupar.
SELECT DISTINCT p.id, p.name
FROM   projects p
JOIN   tasks t ON t.project_id = p.id AND t.priority = 1
WHERE  p.team_id = 1;

-- (d) División relacional: proyectos que tienen tareas en TODOS los estados posibles.
SELECT p.id, p.name
FROM   projects p
WHERE  NOT EXISTS (
         SELECT 1
         FROM   unnest(enum_range(NULL::task_status)) AS s(estado)
         WHERE  NOT EXISTS (
                  SELECT 1 FROM tasks t
                  WHERE  t.project_id = p.id AND t.status = s.estado
                )
       );

-- Extensiones que usaremos
CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- búsqueda por similitud
CREATE EXTENSION IF NOT EXISTS citext;       -- texto sin distinguir mayúsculas

CREATE TABLE users (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email         citext NOT NULL UNIQUE,          -- citext: 'Ana@x.com' = 'ana@x.com'
  password_hash text   NOT NULL,
  full_name     text   NOT NULL,
  is_active     boolean NOT NULL DEFAULT true,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz,
  deleted_at    timestamptz,
  CONSTRAINT users_full_name_not_blank CHECK (length(btrim(full_name)) > 0)
);

CREATE TABLE teams (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name       text NOT NULL,
  slug       text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Entidad pivote CON atributos: no es un simple M:N
CREATE TABLE team_members (
  team_id   uuid NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  user_id   uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role      text NOT NULL DEFAULT 'member',
  joined_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (team_id, user_id),                       -- clave compuesta natural
  CONSTRAINT team_members_role_valid CHECK (role IN ('owner', 'admin', 'member', 'guest'))
);

CREATE TABLE projects (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id     uuid NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  name        text NOT NULL,
  description text,
  archived_at timestamptz,
  created_at  timestamptz NOT NULL DEFAULT now(),
  -- El nombre es único DENTRO del equipo, no globalmente
  CONSTRAINT projects_team_name_unique UNIQUE (team_id, name)
);

CREATE TYPE task_status AS ENUM ('open', 'in_progress', 'done', 'cancelled');

CREATE TABLE tasks (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  project_id   uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  assignee_id  uuid REFERENCES users(id) ON DELETE SET NULL,  -- si se va, la tarea queda huérfana
  title        varchar(180) NOT NULL,
  description  text,
  status       task_status NOT NULL DEFAULT 'open',
  priority     smallint NOT NULL DEFAULT 3,
  estimate_h   numeric(6,2),
  due_date     date,
  completed_at timestamptz,
  version      integer NOT NULL DEFAULT 1,          -- bloqueo optimista (capítulo 17)
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz,
  CONSTRAINT tasks_priority_range CHECK (priority BETWEEN 1 AND 5),
  -- Regla de negocio expresada en el esquema: si está hecha, tiene fecha de cierre
  CONSTRAINT tasks_done_needs_date CHECK (
    (status <> 'done') OR (completed_at IS NOT NULL)
  )
);

CREATE TABLE tags (
  id    serial PRIMARY KEY,
  name  citext NOT NULL UNIQUE,
  color char(7) NOT NULL DEFAULT '#5b8cff'
);

CREATE TABLE task_tags (
  task_id bigint NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  tag_id  integer NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, tag_id)
);

CREATE TABLE comments (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task_id    bigint NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  author_id  uuid   NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  parent_id  bigint REFERENCES comments(id) ON DELETE CASCADE,   -- auto-referencia: hilos
  body       text   NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- 1. INNER: solo tareas que tienen responsable
SELECT t.id, t.title, u.full_name
FROM   tasks t
JOIN   users u ON u.id = t.assignee_id;

-- 2. LEFT: todas las tareas, con responsable o sin él
SELECT t.id, t.title, COALESCE(u.full_name, 'Sin asignar') AS responsable
FROM   tasks t
LEFT JOIN users u ON u.id = t.assignee_id;

-- 3. ANTI-JOIN: usuarios que no tienen ninguna tarea abierta
SELECT u.id, u.email
FROM   users u
LEFT JOIN tasks t ON t.assignee_id = u.id AND t.status = 'open'
WHERE  t.id IS NULL;
-- OJO: la condición sobre t va en el ON, no en el WHERE.
-- Si la pones en el WHERE, el LEFT JOIN se degrada a INNER JOIN.

-- 4. SELF JOIN: comentarios con su comentario padre
SELECT c.id, c.body, p.body AS respuesta_a
FROM   comments c
LEFT JOIN comments p ON p.id = c.parent_id;

-- 5. LATERAL: las 3 tareas más recientes DE CADA proyecto.
--    LATERAL permite que la subconsulta referencie la fila exterior.
SELECT p.name, t.title, t.created_at
FROM   projects p
CROSS JOIN LATERAL (
  SELECT t.title, t.created_at
  FROM   tasks t
  WHERE  t.project_id = p.id
  ORDER  BY t.created_at DESC
  LIMIT  3
) t;

-- COUNT(*) cuenta filas; COUNT(col) ignora los NULL. No son lo mismo.
SELECT
  count(*)                                          AS total_tareas,
  count(assignee_id)                                AS con_responsable,
  count(*) - count(assignee_id)                     AS sin_responsable,
  count(DISTINCT assignee_id)                       AS personas_implicadas,
  -- FILTER es más legible que CASE WHEN dentro del agregado
  count(*) FILTER (WHERE status = 'done')           AS completadas,
  count(*) FILTER (WHERE due_date < current_date
                     AND status <> 'done')          AS vencidas,
  round(avg(estimate_h), 2)                         AS estimacion_media,
  -- avg() ignora los NULL: la media es sobre las tareas QUE TIENEN estimación
  sum(estimate_h)                                   AS horas_totales
FROM tasks
WHERE project_id = $1;

-- Resumen por proyecto, quedándonos solo con los proyectos grandes
SELECT   p.name,
         count(t.id)                              AS tareas,
         count(*) FILTER (WHERE t.status = 'done') AS hechas
FROM     projects p
LEFT JOIN tasks t ON t.project_id = p.id
GROUP BY p.id, p.name          -- agrupa por la clave primaria: p.name va "de regalo"
HAVING   count(t.id) >= 10     -- HAVING filtra GRUPOS
ORDER BY tareas DESC;

WITH RECURSIVE hilo AS (
  -- Caso base: comentarios raíz de la tarea
  SELECT c.id, c.parent_id, c.body, c.author_id, 0 AS nivel,
         ARRAY[c.created_at] AS camino
  FROM   comments c
  WHERE  c.task_id = $1 AND c.parent_id IS NULL

  UNION ALL

  -- Paso recursivo: hijos de lo ya encontrado
  SELECT c.id, c.parent_id, c.body, c.author_id, h.nivel + 1,
         h.camino || c.created_at
  FROM   comments c
  JOIN   hilo h ON c.parent_id = h.id
  WHERE  h.nivel < 10          -- cortafuegos: evita una recursión infinita si hay un ciclo
)
SELECT repeat('    ', nivel) || body AS comentario, nivel
FROM   hilo
ORDER  BY camino;              -- ordena manteniendo la estructura del hilo

-- 1. La tarea más reciente de cada proyecto (patrón "top-N por grupo")
SELECT *
FROM (
  SELECT t.*,
         row_number() OVER (PARTITION BY t.project_id ORDER BY t.created_at DESC) AS rn
  FROM   tasks t
) x
WHERE rn = 1;

-- Alternativa específica de PostgreSQL, más corta y a menudo más rápida:
SELECT DISTINCT ON (project_id) *
FROM   tasks
ORDER  BY project_id, created_at DESC;

-- 2. Ranking de personas por tareas completadas, con desempate
SELECT u.full_name,
       count(*) AS completadas,
       rank()       OVER (ORDER BY count(*) DESC) AS puesto,
       dense_rank() OVER (ORDER BY count(*) DESC) AS puesto_sin_huecos
FROM   tasks t
JOIN   users u ON u.id = t.assignee_id
WHERE  t.status = 'done'
GROUP  BY u.id, u.full_name;
-- rank() deja huecos tras un empate (1,1,3); dense_rank() no (1,1,2)

-- 3. Acumulado y comparación con el periodo anterior
SELECT dia,
       cerradas,
       sum(cerradas) OVER (ORDER BY dia)               AS acumulado,
       lag(cerradas) OVER (ORDER BY dia)               AS dia_anterior,
       cerradas - lag(cerradas, 1, 0) OVER (ORDER BY dia) AS variacion,
       round(avg(cerradas) OVER (ORDER BY dia
             ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS media_movil_7d
FROM (
  SELECT date_trunc('day', completed_at)::date AS dia, count(*) AS cerradas
  FROM   tasks
  WHERE  completed_at >= current_date - interval '90 days'
  GROUP  BY 1
) d
ORDER BY dia;

-- UPSERT: inserta o actualiza en una sola operación atómica.
-- Sin ON CONFLICT harían falta un SELECT y luego un INSERT o UPDATE,
-- con una condición de carrera entre ambos.
INSERT INTO tags (name, color)
VALUES ('urgente', '#ff5c7a')
ON CONFLICT (name) DO UPDATE
  SET color = EXCLUDED.color        -- EXCLUDED = la fila que se intentaba insertar
RETURNING id, name;

-- Ignorar duplicados sin error (inserción masiva idempotente)
INSERT INTO task_tags (task_id, tag_id)
SELECT $1, unnest($2::int[])
ON CONFLICT DO NOTHING;

-- UPDATE ... FROM: actualizar usando datos de otra tabla
UPDATE tasks t
SET    status = 'cancelled', updated_at = now()
FROM   projects p
WHERE  p.id = t.project_id
  AND  p.archived_at IS NOT NULL
  AND  t.status IN ('open', 'in_progress')
RETURNING t.id;                     -- devuelve lo modificado: útil para auditar

-- DELETE con subconsulta correlacionada
DELETE FROM comments c
WHERE NOT EXISTS (SELECT 1 FROM tasks t WHERE t.id = c.task_id);

CREATE INDEX idx_tasks_project_status_due
  ON tasks (project_id, status, due_date);

-- Consultas que APROVECHAN el índice:
--   WHERE project_id = ?                              ← prefijo (1 columna)
--   WHERE project_id = ? AND status = ?               ← prefijo (2 columnas)
--   WHERE project_id = ? AND status = ? AND due_date < ?   ← completo
--   WHERE project_id = ? ORDER BY status, due_date    ← orden servido por el índice
--
-- Consultas que NO lo aprovechan (o solo parcialmente):
--   WHERE status = ?                     ← salta la primera columna: no hay prefijo
--   WHERE due_date < ?                   ← idem

-- REGLA DE ORDENACIÓN DE COLUMNAS:
--   1) columnas de igualdad (=) primero, de mayor a menor selectividad
--   2) después una única columna de rango (<, >, BETWEEN)
--   3) al final, columnas que solo se usan para ORDER BY
-- Tras una columna de rango, las siguientes ya no filtran en el índice.

-- ÍNDICE PARCIAL: mucho más pequeño y rápido si solo consultas un subconjunto
CREATE INDEX idx_tasks_activas
  ON tasks (project_id, due_date)
  WHERE status IN ('open', 'in_progress');

-- ÍNDICE SOBRE EXPRESIÓN: necesario si consultas con una función
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- Ahora sí usa índice:  WHERE lower(email) = lower($1)

-- ÍNDICE ÚNICO PARCIAL: la solución al soft delete (capítulo 17).
-- Permite reutilizar un email si la fila anterior está borrada lógicamente.
CREATE UNIQUE INDEX uq_users_email_vivos
  ON users (email) WHERE deleted_at IS NULL;

-- INCLUDE: permite un "index only scan" sin ampliar la clave de búsqueda
CREATE INDEX idx_tasks_lista
  ON tasks (project_id, created_at DESC) INCLUDE (title, status);

-- EN PRODUCCIÓN, siempre CONCURRENTLY: no bloquea las escrituras.
-- No puede ejecutarse dentro de una transacción (importante para las migraciones).
CREATE INDEX CONCURRENTLY idx_comments_task ON comments (task_id);

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT t.id, t.title, u.full_name
FROM   tasks t
LEFT JOIN users u ON u.id = t.assignee_id
WHERE  t.project_id = '3f2a...'::uuid
  AND  t.status = 'open'
ORDER  BY t.due_date
LIMIT  50;

BEGIN;
-- Bloquea las filas seleccionadas hasta el final de la transacción.
-- Otras transacciones que hagan FOR UPDATE sobre ellas esperarán.
SELECT * FROM tasks WHERE id = $1 FOR UPDATE;
UPDATE tasks SET status = 'in_progress' WHERE id = $1;
COMMIT;

-- Patrón de cola de trabajos dentro de la base de datos:
-- SKIP LOCKED hace que cada worker tome trabajos DISTINTOS sin esperar.
BEGIN;
WITH siguiente AS (
  SELECT id FROM jobs
  WHERE  status = 'pending'
  ORDER  BY created_at
  FOR UPDATE SKIP LOCKED
  LIMIT  1
)
UPDATE jobs j SET status = 'processing', started_at = now()
FROM   siguiente s WHERE j.id = s.id
RETURNING j.*;
COMMIT;

-- Nivel 1: LIKE. Solo sirve con prefijo si quieres usar índice.
SELECT * FROM tasks WHERE title ILIKE 'informe%';

-- Nivel 2: trigramas. Tolera errores tipográficos y comodín inicial.
CREATE INDEX idx_tasks_title_trgm ON tasks USING gin (title gin_trgm_ops);
SELECT title, similarity(title, 'infrome mensal') AS parecido
FROM   tasks
WHERE  title % 'infrome mensal'        -- operador de similitud
ORDER  BY parecido DESC
LIMIT  10;

-- Nivel 3: búsqueda de texto completo con lematización en español.
ALTER TABLE tasks ADD COLUMN busqueda tsvector
  GENERATED ALWAYS AS (
    to_tsvector('spanish', coalesce(title,'') || ' ' || coalesce(description,''))
  ) STORED;
CREATE INDEX idx_tasks_busqueda ON tasks USING gin (busqueda);

SELECT id, title, ts_rank(busqueda, q) AS relevancia
FROM   tasks, plainto_tsquery('spanish', 'informes mensuales') q
WHERE  busqueda @@ q
ORDER  BY relevancia DESC
LIMIT  20;
-- 'informes mensuales' encuentra 'informe mensual': la lematización
-- reduce cada palabra a su raíz.

ALTER TABLE tasks ADD COLUMN metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

-- Operadores esenciales
SELECT metadata -> 'origen'        AS origen_json,   -- devuelve jsonb
       metadata ->> 'origen'       AS origen_texto,  -- devuelve text
       metadata #>> '{cliente,id}' AS cliente_id     -- ruta anidada
FROM tasks;

-- Índice GIN para consultas de contención
CREATE INDEX idx_tasks_metadata ON tasks USING gin (metadata);
SELECT * FROM tasks WHERE metadata @> '{"origen": "email"}';

-- Si SIEMPRE consultas por el mismo campo, un índice de expresión B-tree
-- es más pequeño y rápido que el GIN completo:
CREATE INDEX idx_tasks_origen ON tasks ((metadata ->> 'origen'));

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY projects_por_equipo ON projects
  USING (team_id = current_setting('app.team_id', true)::uuid);

-- En cada petición, la aplicación fija la variable de sesión
-- (con RequestContext de MikroORM, capítulo 14):
SET LOCAL app.team_id = '3f2a...';
-- A partir de aquí, cualquier consulta a projects solo ve las filas de ese equipo,
-- aunque el desarrollador olvide el WHERE.

-- COPY es un orden de magnitud más rápido que INSERT fila a fila
COPY tasks (project_id, title, status, created_at)
FROM '/tmp/tareas.csv' WITH (FORMAT csv, HEADER true);

-- Estrategia para una carga inicial grande:
-- 1. Crear la tabla SIN índices secundarios ni claves foráneas
-- 2. COPY de los datos
-- 3. Crear los índices (una sola pasada ordenada, mucho más rápido que
--    mantenerlos actualizados fila a fila)
-- 4. Añadir las claves foráneas
-- 5. ANALYZE para actualizar las estadísticas

-- Actualizaciones masivas: por lotes, no de golpe.
-- Un UPDATE de 10 millones de filas mantiene bloqueos y genera un WAL enorme.
UPDATE tasks SET status = 'cancelled'
WHERE  id IN (SELECT id FROM tasks WHERE status = 'open'
                AND created_at < '2020-01-01' LIMIT 10000);
-- Repetir hasta que no afecte a ninguna fila.

-- Pregunta previa que decide el diseño: ¿necesitas saber cómo estaba
-- un registro en una fecha pasada, o solo quién cambió qué y cuándo?

-- Opción A: bitácora de cambios (responde "quién y cuándo")
CREATE TABLE tasks_audit (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task_id    bigint NOT NULL,
  operacion  char(1) NOT NULL,            -- I, U, D
  cambios    jsonb   NOT NULL,            -- solo lo que cambió
  actor_id   uuid,
  ocurrido   timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION auditar_tasks() RETURNS trigger AS $$
BEGIN
  INSERT INTO tasks_audit (task_id, operacion, cambios, actor_id)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    left(TG_OP, 1),
    CASE WHEN TG_OP = 'UPDATE'
         -- solo los campos que realmente cambiaron
         THEN to_jsonb(NEW) - (SELECT array_agg(key)
                                 FROM jsonb_each(to_jsonb(NEW)) n
                                 JOIN jsonb_each(to_jsonb(OLD)) o USING (key)
                                WHERE n.value = o.value)::text[]
         ELSE to_jsonb(COALESCE(NEW, OLD)) END,
    current_setting('app.user_id', true)::uuid
  );
  RETURN COALESCE(NEW, OLD);
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auditar_tasks
AFTER INSERT OR UPDATE OR DELETE ON tasks
FOR EACH ROW EXECUTE FUNCTION auditar_tasks();

-- Opción B: versionado temporal (responde "cómo estaba el 3 de marzo").
-- Cada fila tiene un intervalo de validez y solo una está vigente.
CREATE TABLE precios (
  producto_id bigint NOT NULL,
  importe     numeric(10,2) NOT NULL,
  validez     tstzrange NOT NULL,
  EXCLUDE USING gist (producto_id WITH =, validez WITH &&)
  -- ↑ la restricción de exclusión impide que dos periodos se solapen
);
SELECT importe FROM precios
 WHERE producto_id = 1 AND validez @> '2026-03-03'::timestamptz;

-- 1. Las consultas que más tiempo total consumen (requiere pg_stat_statements).
--    Ordenar por tiempo TOTAL y no por tiempo medio: una consulta de 5 ms
--    ejecutada un millón de veces duele más que una de 2 s ejecutada una vez.
SELECT substr(query, 1, 80) AS consulta,
       calls,
       round(total_exec_time::numeric, 1)      AS ms_total,
       round(mean_exec_time::numeric, 2)       AS ms_medio,
       rows
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

-- 2. Índices que nunca se usan: coste puro en escrituras y espacio.
SELECT relname AS tabla, indexrelname AS indice,
       pg_size_pretty(pg_relation_size(indexrelid)) AS tamano
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
  AND  indexrelid NOT IN (SELECT conindid FROM pg_constraint)
ORDER  BY pg_relation_size(indexrelid) DESC;

-- 3. Tablas que necesitan índice: muchas lecturas secuenciales.
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       seq_tup_read / GREATEST(seq_scan, 1) AS filas_por_escaneo
FROM   pg_stat_user_tables
WHERE  seq_scan > 1000
ORDER  BY seq_tup_read DESC;

-- 4. Transacciones abiertas demasiado tiempo: el enemigo silencioso
--    que impide limpiar versiones muertas en toda la base de datos.
SELECT pid, state, now() - xact_start AS duracion,
       substr(query, 1, 60) AS consulta
FROM   pg_stat_activity
WHERE  state <> 'idle' AND xact_start < now() - interval '1 minute'
ORDER  BY xact_start;

-- 5. Bloqueos: quién espera a quién.
SELECT bloqueada.pid AS espera, bloqueante.pid AS bloquea,
       substr(bloqueada.query, 1, 50) AS consulta_en_espera
FROM   pg_stat_activity bloqueada
JOIN   pg_stat_activity bloqueante
       ON bloqueante.pid = ANY(pg_blocking_pids(bloqueada.pid))
WHERE  cardinality(pg_blocking_pids(bloqueada.pid)) > 0;

-- 6. Tamaño real de cada tabla, con sus índices.
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS solo_datos,
       n_live_tup AS filas_vivas, n_dead_tup AS filas_muertas
FROM   pg_stat_user_tables
ORDER  BY pg_total_relation_size(relid) DESC
LIMIT  15;