Por qué tu query con IN es lenta aunque el índice esté bien

Una tabla ordenada por (tenant_id, created_at, id) y un listado paginado encima. Pedís un tenant y la página sale en 0,14 segundos. Pedís dos y tarda 9. Mismo índice, misma query, mismo volumen. Lo único que cambió es que el filtro pasó de = a IN.

La solución que sigue tiene su precio, y conviene decirlo antes del truco: el SQL deja de ser un template fijo y pasa a generarse (una rama por tenant), o sea más código que mantener. Y el costo de la página crece lineal con la cantidad de tenants pedidos. Para nosotros valió la pena; puede que para vos no, y al final está el criterio para decidirlo.

Esta es la parte técnica de un caso más grande: optimizamos seis reportes lentos y la causa era siempre la misma. Acá va el capítulo del IN, que es el más lindo.

Por qué la versión con igualdad es rápida

Una página típica pide los últimos 25 eventos del tenant 1, ordenados por fecha. Si la tabla está físicamente ordenada por (tenant_id, created_at), todos los eventos de ese tenant están contiguos, y adentro de esa porción ya están ordenados por fecha. El motor hace entonces lo mejor que sabe hacer: se para al final de la porción, lee hacia atrás y frena cuando juntó 25 filas. En ClickHouse eso es la lectura en orden del índice (optimize_read_in_order); la idea existe en cualquier motor capaz de recorrer un índice en el orden que le pidieron.

Que el tenant tenga 97 millones de filas no importa, porque el costo es el de la página y no el de la historia.

Qué rompe el IN

Ahora pedí lo mismo para los tenants 1 y 2. La tabla sigue ordenada igual: primero todos los eventos del tenant 1, después todos los del 2. Los últimos 25 eventos “de los dos” ya no viven contiguos en ninguna parte: pueden ser 20 de uno y 5 del otro, o cualquier mezcla. El orden físico dejó de coincidir con el orden pedido, así que el motor ya no puede frenar temprano: lee el rango completo de ambos tenants, ordena todo eso, y recién ahí corta 25.

Es la guía telefónica ordenada por ciudad y apellido: los García de una ciudad están juntos; los García de dos ciudades son dos búsquedas, por más que la guía sea una sola.

En nuestro caso, esa diferencia era 0,14–0,6 segundos con igualdad contra 9–13 con IN. Y el caso multi no era exótico: cuando el usuario no filtraba tenant, el sistema mandaba todos los que su permiso le daba, así que la vista default era justamente la lenta.

Una rama por tenant

La salida es devolverle la igualdad al motor: una subquery por tenant, cada una con su propio corte, unidas con UNION ALL y un combinador liviano que arma la página global.

select *
from (
    -- cada rama fija su tenant por igualdad:
    -- el corte anticipado vuelve a funcionar
    select * from eventos
    where tenant_id = 1
    order by created_at desc
    limit 75            -- take + skip, ver abajo
    union all
    select * from eventos
    where tenant_id = 2
    order by created_at desc
    limit 75
)
order by created_at desc
limit 25 offset 50;     -- página 3: take 25, skip 50

Cada rama frena en su propia página, y el combinador ordena una unión de 150 filas, que es trivial. Dos detalles que no son opcionales:

• Cada rama lleva take + skip, no take. La página 3 global podría venir entera de un solo tenant, así que cada rama tiene que poder aportar hasta su fila 75. Con limit 25 por rama la página 3 sale mal, y no falla nada que te avise.
• No hace falta deduplicar entre ramas, siempre que la columna del IN parta los datos en conjuntos disjuntos: cada evento pertenece a un solo tenant. Si tu columna no cumple eso, este esquema pide más trabajo.

El costo total queda en la suma de las páginas por tenant: lineal en la cantidad de tenants, no en el volumen del rango. Pedir todas las marcas de un mes entero pasó de ~13 segundos a menos de 2, y el caso de un solo tenant quedó idéntico, porque es literalmente la misma query de antes.

El count mejora por otro camino

Contar no tiene corte anticipado posible: hay que mirar todo lo que matchea. Pero “todo lo que matchea” no es “toda la tabla”: al filtrar por los slices de los tenants pedidos, el count pasó de leer 2.180 millones de filas a leer solo las del pedido, y de 41–84 segundos a 0,3–2. Es menos vistoso que el truco de las ramas y en la práctica se nota igual de fuerte.

Cuando el orden pedido no es el del índice

Variante del mismo problema, de otra pantalla del mismo caso: el listado ordena por id desc, pero el índice ordena por fecha. Los ids crecen casi a la par del tiempo, y ese “casi” es lo que había que medir: el desfase real máximo entre id y created_at en toda la historia era de 132,8 segundos.

Con eso alcanzó para un plan de tres pasos: leer por índice una ventana de tiempo con un margen de 6 horas (163 veces el desfase máximo medido), y sobre ese superset chico aplicar el ORDER BY id LIMIT exacto. Como el margen sale de una medición y no de una corazonada, quedó un monitor que avisa si el desfase alguna vez crece más allá de lo que el margen cubre.

Dónde este truco no te salva

• Si la tabla no está ordenada con el tenant primero. No hay rama que valga: primero se arregla el almacenamiento, después la query.
• Si el IN trae cientos de valores. La suma lineal se vuelve un problema: con 4 tenants fue 1,9 segundos; con 400 hay que pensar otra cosa.
• Si arriba del tenant hay un filtro muy esparso. Un usuario con 7 eventos en el mes nunca llena el corte, y la rama termina recorriendo la porción completa: medido, 2,4 segundos. Mejor que el original igual, pero el techo existe.

Lo que queda abierto

El precio del principio sigue ahí: generar SQL es más código que reemplazar strings en un template, y alguien lo va a tener que mantener. Nosotros lo pagamos porque el caso multi-tenant era la vista default del backoffice, no la excepción. Si en tu pantalla es al revés, un IN lento un par de veces por día puede ser un precio justo por un template simple.

La forma de la query también es una decisión de diseño, y como todas se paga en algún lado. Lo que conviene evitar es tomarla sin darse cuenta.