Why filtering with IN makes ORDER BY LIMIT slow

Here is a pagination mystery. The table is sorted by (tenant_id, created_at, id), which is exactly the right index for the screen on top of it. One tenant: the page comes back in 0.14 seconds. Two tenants: 9 seconds. Nothing else changed. The filter just went from = to IN.

Before the trick, its price, because there is one: the SQL stops being a fixed template and has to be generated (one branch per tenant), which means more code to own. And page cost grows linearly with how many tenants you ask for. It was worth it for us; it may not be for you, and the criteria to decide are at the end.

This is the technical chapter of a bigger story: we fixed six slow reports and kept finding the same cause. The IN chapter happens to be the best one.

What makes the equality version fast

A typical page asks for the latest 25 events of tenant 1, sorted by date. When the table is physically sorted by (tenant_id, created_at), that tenant’s events sit in one contiguous slice, already date-ordered inside. So the engine gets to do the thing it does best: jump to the end of the slice, read backwards, stop the moment it has 25 rows. ClickHouse calls this reading in index order (optimize_read_in_order), and some version of it exists in every engine that can walk an index in the order you asked for.

Whether the tenant holds 97 million rows is irrelevant, because you pay for the page and not for the history.

What IN actually breaks

Now ask for tenants 1 and 2 together. The physical layout has not moved: every event of tenant 1 first, then every event of tenant 2. But the latest 25 events “across both” no longer live anywhere contiguous; they might be 20 and 5, or any other mix. Requested order and physical order have parted ways, so the engine loses its right to stop early. It reads the full range of both tenants, sorts the lot, and only then cuts 25 rows.

Think of a phone book sorted by city, then surname. The Garcías of one city sit together. The Garcías of two cities are two lookups, no matter that it is a single book.

For us the gap was 0.14–0.6 seconds with equality against 9–13 with IN. And multi-tenant was not the exotic path: with no tenant filter selected, the system sent every tenant the user was allowed to see, so the default view was precisely the slow one.

One branch per tenant

The fix hands equality back to the engine: one subquery per tenant, each with its own cut, glued with UNION ALL, plus a light combiner that assembles the global page.

select *
from (
    -- each branch pins its tenant with equality,
    -- so the early stop works again
    select * from events
    where tenant_id = 1
    order by created_at desc
    limit 75            -- take + skip, see below
    union all
    select * from events
    where tenant_id = 2
    order by created_at desc
    limit 75
)
order by created_at desc
limit 25 offset 50;     -- page 3: take 25, skip 50

Each branch stops at its own page, and the outer sort handles a union of 150 rows, which costs nothing. Two details you cannot skip:

• Every branch needs take + skip, not take. Global page 3 might come entirely from a single tenant, so each branch must be able to supply rows up to its own row 75. Give each branch limit 25 and page 3 silently comes out wrong.
• No dedup across branches is needed, as long as the IN column splits the data into disjoint sets: one event belongs to one tenant. If your column cannot promise that, this scheme needs more work.

Total cost lands at the sum of the per-tenant pages: linear in tenants, not in the volume of the range. Asking for every brand over a full month went from ~13 seconds to under 2, while the single-tenant case stayed exactly as fast, because it is literally the query it always was.

Counts improve by a different road

A count has no early stop to recover: counting means looking at everything that matches. But “everything that matches” is not “the whole table”. By reading only the slices of the requested tenants, the count went from scanning 2.18 billion rows to just the requested ones, and from 41–84 seconds down to 0.3–2. It is less flashy than the branch trick, and in practice it matters just as much.

When the requested order is not the index order

Same disease, different screen from the same case: the listing sorts by id desc, while the index sorts by date. Ids grow almost in step with time, and that “almost” is what had to be measured: the maximum drift between id and created_at across the whole history was 132.8 seconds.

That measurement bought a three-step plan: read a time window through the index with a 6-hour margin (163 times the maximum drift ever observed), then apply the exact ORDER BY id LIMIT over that small superset. Because the margin comes from a measurement rather than a hunch, a monitor now watches whether the drift ever outgrows what it covers.

Where this trick will not save you

• The table is not sorted with the tenant first. No branch will help: fix the storage first, the query after.
• The IN carries hundreds of values. The linear sum becomes a problem: 4 tenants cost 1.9 seconds; 400 calls for a different approach.
• A very sparse filter sits on top of the tenant. A user with 7 events in the month never fills the cut, and the branch degrades into walking the whole slice: 2.4 seconds, measured. Still beats the original, but the ceiling is real.

What stays open

The price from the opening never goes away: generating SQL is more code than substituting strings into a template, and somebody has to own it. We paid it because multi-tenant was the back office’s default view rather than its edge case. If your screen is the other way around, a slow IN a couple of times a day may be a fair price for a simple template.

The shape of a query is a design decision too, and like every design decision it gets paid for somewhere. What is worth avoiding is making it without noticing.