Predicados sargables: como escribir un WHERE amigable con los indices

Un predicado es sargable (Search ARGument ABLE) cuando el planificador puede usar un indice sobre la columna para saltar directo a las filas que coinciden. Envuelve esa columna en una funcion — EXTRACT, lower(), date() — y el indice se apaga: la base evalua la expresion fila por fila, es decir, un Seq Scan. Veamos como evitarlo.

Que anula el indice

Un B-tree normal indexa los valores de la columna, no los valores de una funcion aplicada a ella. En cuanto el lado izquierdo de tu WHERE es f(columna), el indice sobre columna queda inservible.

-- NOT sargable: function on the column
SELECT id, email
FROM users
WHERE lower(email) = 'ann@shop.io';

-- NOT sargable: EXTRACT wraps created_at
SELECT id, amount
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2024;

La senal es simple: si no puedes dejar la columna desnuda a un lado de la comparacion, el predicado probablemente no es sargable. La misma trampa cubre created_at::date = '...', date(created_at), amount + 0 = 100 y coalesce(status, '') = 'paid'.

Gotcha: la aritmetica sobre la columna tambien mata el indice. WHERE salary * 12 > 100000 no es sargable, pero WHERE salary > 100000 / 12 si lo es. Lleva el calculo al lado de la constante.

Reescribir a rangos

El movimiento clave es sustituir una funcion sobre la columna por un rango sobre la propia columna. Un intervalo semiabierto >= ... AND < ... es exacto (sin error por el ultimo instante del dia) y usa el indice.

-- good: WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
SELECT id, amount
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01';

-- One specific day, half-open and index-friendly
SELECT id, amount
FROM orders
WHERE created_at >= '2024-03-15'
  AND created_at <  '2024-03-16';

Para busquedas sin distincion de mayusculas, no escribas lower(email) = ...; normaliza los datos de antemano o usa el tipo citext. Asi la igualdad simple sigue siendo sargable:

-- Equality stays sargable; index on email is usable
SELECT id, email
FROM users
WHERE email = 'ann@shop.io';

LIKE y el comodin inicial

LIKE con un prefijo anclado (texto y luego %) es sargable: un B-tree puede buscar por el comienzo de la cadena. Un % inicial convierte la busqueda en un recorrido completo.

-- Sargable: anchored prefix, uses a B-tree index
SELECT id, name
FROM users
WHERE name LIKE 'Ann%';

-- NOT sargable: leading wildcard forces a scan
SELECT id, name
FROM users
WHERE name LIKE '%nna%';

Un detalle de PostgreSQL: el LIKE por prefijo bajo una locale no predeterminada necesita un indice creado con text_pattern_ops. Para buscar subcadenas con % inicial, ningun B-tree normal ayuda: necesitas un indice GIN de trigramas (pg_trgm).

Indices de expresion: cuando si necesitas la funcion

A veces la funcion forma parte real de la pregunta. Entonces haz sargable el predicado indexando la propia expresion. PostgreSQL lo permite:

-- Index the expression, then this query is sargable again
CREATE INDEX idx_users_lower_email ON users (lower(email));

SELECT id, email
FROM users
WHERE lower(email) = 'ann@shop.io';

-- Functional index for a derived "year" lookup
CREATE INDEX idx_orders_created_year
ON orders (EXTRACT(YEAR FROM created_at));

El detalle: la expresion del indice y la del WHERE deben coincidir al pie de la letra. Otra opcion es una columna generada con un indice normal encima. No acumules indices funcionales donde basta un rango: un indice sobre lower(email) cuesta espacio y frena las escrituras.

MySQL y ClickHouse

En MySQL las reglas son identicas: WHERE DATE(created_at) = '2024-03-15' ignora el indice, asi que reescribelo como rango. Desde la 8.0.13 MySQL admite indices funcionales (CREATE INDEX ... ((lower(email)))); antes, una columna generada daba el mismo efecto.

En ClickHouse el "indice" es una clave primaria dispersa mas el orden de ordenacion. Una funcion sobre una columna de la clave estropea el descarte de granulos igual que en el resto, asi que filtra por la columna de clave desnuda, de preferencia por rango.

En resumen: deja la columna desnuda a un lado de la comparacion, cambia funciones por rangos, evita el % inicial y, donde la funcion sea imprescindible, indexa la expresion.