Tres técnicas prácticas en SQL: acelerar búsquedas de 2.5 segundos a 80 ms, cardinalidad de índices y detección de duplicados con LAGResumen temático · digest del 21 de noviembre
Bodegón cinematográfico con un cronómetro mecánico y tarjetas indexadas desplegadas en abanico
Resumen temático3 secciones≈ 6 min

Tres técnicas prácticas en SQL: acelerar búsquedas de 2.5 segundos a 80 ms, cardinalidad de índices y detección de duplicados con LAG

Cuando una consulta tarda dos o tres segundos o un reporte muestra duplicados, suele tentar una de dos salidas habituales: pagar por un servidor más grande o traer las filas al backend y procesarlas en un bucle. Ambas soluciones aguantan solo hasta que la tabla vuelva a crecer. Reunimos tres casos claros de SQL Portal donde el problema se resuelve dentro de la propia consulta entendiendo índices, cardinalidad y ventanas de filas.

Aceleramos la búsqueda de productos de 2.5 segundos a 80 ms sin cambiar de hardware

Los autores parten de una idea certera: la mayoría de los «problemas de rendimiento de la base de datos» son en realidad SQL mal escrito disfrazado de problema de infraestructura. Un catálogo products en un servidor potente tardaba 2.5 segundos por petición:

-- Consulta original (~2.5 s): SELECT *, LOWER() y OFFSET
SELECT *
FROM products p
WHERE p.category_id = $1
  AND p.active = TRUE
  AND p.price_cents BETWEEN $2 AND $3
  AND LOWER(p.name) LIKE LOWER('%' || $4 || '%')
ORDER BY p.created_at DESC
OFFSET $5 LIMIT $6;

Aquí chocaron cuatro trampas. SELECT * arrastra la columna pesada description. LOWER(p.name) LIKE '%...%' con comodín al inicio inutiliza el índice B-tree normal. Ordenar por created_at ocurre después del filtrado, y OFFSET obliga al motor a leer y descartar todas las filas previas en cada página.

Todo se arregla sin ampliar el servidor. Primero se crea un índice cubriente siguiendo la regla «igualdad → rango → orden» con INCLUDE (name); en PostgreSQL (desde la versión 11) esto habilita un Index-Only Scan sin tocar la tabla mientras VACUUM mantenga actualizado el mapa de visibilidad. Luego se activa pg_trgm con un índice GIN de trigramas sobre name para ILIKE (conviene limitar en la interfaz búsquedas de 1 o 2 caracteres). Por último, se sustituye OFFSET por paginación keyset (seek pagination) usando las coordenadas de la última fila:

-- Índice cubriente (CONCURRENTLY evita bloquear escrituras)
CREATE INDEX CONCURRENTLY idx_products_feed
  ON products (category_id, active, price_cents,
               created_at DESC, product_id DESC)
  INCLUDE (name);

-- Índice GIN de trigramas para búsqueda de subcadenas
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING gin (name gin_trgm_ops);

-- Consulta rápida con paginación keyset (~80 ms)
SELECT p.product_id, p.name, p.price_cents, p.created_at
FROM products p
WHERE p.category_id = $1
  AND p.active = TRUE
  AND p.price_cents BETWEEN $2 AND $3
  AND p.name ILIKE '%' || $4 || '%'
  AND (p.created_at, p.product_id) < ($6, $7)
ORDER BY p.created_at DESC, p.product_id DESC
LIMIT $5;

La respuesta cae de 2.5 segundos a 80 ms: más de 30 veces más rápida. Para verificar el plan conviene usar EXPLAIN (ANALYZE, BUFFERS). Comprar máquinas más grandes solo maquilla una mala arquitectura hasta el siguiente pico de carga.

Cardinalidad: por qué un índice sobre el género del usuario trabaja en vacío

En entrevistas técnicas suele aparecer esta pregunta trampa: «¿Qué está mal en este script?». El autor confiesa que primero buscó un error de sintaxis, cuando el problema estaba en los datos.

-- Sintaxis impecable, pero un índice trabaja en vacío
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50),
    Gender CHAR(1)
);
CREATE INDEX idx_name ON Employees(Name);
CREATE INDEX idx_gender ON Employees(Gender);

Todo depende de la cardinalidad (cardinality), es decir, la proporción de valores únicos en una columna. En una tabla Employees de un millón de filas la diferencia salta a la vista:

ColumnaValores únicosCardinalidadUtilidad del índice
ID1,000,000AltaMáxima
NameCientos de milesAltaÚtil
Department10–20MediaLimitada
Gender2–3Muy bajaCasi nula

Cada valor de Gender abarca un tercio o la mitad de la tabla, así que el índice no reduce la búsqueda. El planificador lo ignora y lanza un Full Table Scan (lectura completa de la tabla). Sin embargo, en cada INSERT, UPDATE y DELETE, la base gasta disco y CPU actualizando idx_gender.

Los campos de baja cardinalidad se indexan de otro modo: con un índice compuesto junto a un campo selectivo o con un índice parcial para un subconjunto pequeño de filas (por ejemplo, el 5% de pedidos abiertos):

-- Sirve para Department y Department + ID,
-- pero NO para buscar solo por ID (prefijo izquierdo B-tree)
CREATE INDEX idx_dept_emp ON Employees (Department, ID);

-- Índice parcial en PostgreSQL para un estado poco frecuente
CREATE INDEX idx_orders_open ON orders (created_at)
  WHERE active = TRUE;

Antes de lanzar cualquier CREATE INDEX, revisa la distribución de valores: un índice inútil solo frena la escritura.

Buscamos compras repetidas en 5 minutos con la función de ventana LAG

Un clásico de los sistemas de pago: la tabla CustomerPurchases (PurchaseId, CustomerId, StoreId, ProductId, PurchaseTimestamp). Hay que detectar casos donde un mismo cliente compró el mismo producto en la misma tienda otra vez en menos de cinco minutos, señal de duplicado o doble cobro.

No hace falta un pesado self-JOIN. Se resuelve en una sola pasada con un CTE (WITH ... AS, subconsulta con nombre) y la función de ventana LAG, que consulta la hora de la compra anterior dentro del grupo (PARTITION BY):

-- PostgreSQL: restamos fechas y comparamos con INTERVAL
WITH PurchasesWithLag AS (
  SELECT
    PurchaseId, CustomerId, StoreId, ProductId,
    PurchaseTimestamp,
    LAG(PurchaseTimestamp) OVER (
      PARTITION BY CustomerId, StoreId, ProductId
      ORDER BY PurchaseTimestamp
    ) AS PreviousPurchaseTimestamp
  FROM CustomerPurchases
)
SELECT
  PurchaseId, CustomerId, StoreId, ProductId,
  PreviousPurchaseTimestamp, PurchaseTimestamp
FROM PurchasesWithLag
WHERE PreviousPurchaseTimestamp IS NOT NULL
  AND PurchaseTimestamp - PreviousPurchaseTimestamp
      <= INTERVAL '5 minutes'
ORDER BY CustomerId, PurchaseTimestamp;

Importan dos detalles. En la primera compra del grupo no existe fila previa y LAG devuelve NULL; sin IS NOT NULL el filtro fallaría. Además, cuida el dialecto: en el ejemplo original para SQL Server se usó DATEDIFF (DATEDIFF(MINUTE, prev, curr) <= 5), pero DATEDIFF cuenta cruces de límite de minuto (12:00:59 y 12:01:01 dan 1), así que para medir 300 segundos exactos en T-SQL es más seguro DATEDIFF(SECOND, ...) <= 300, y en PostgreSQL INTERVAL '5 minutes'.

Quiero un análisis detalladoLos votos ayudan a elegir qué analizar a fondo. Este material tiene 0 votos.
Fuentes · 6