A timeline endpoint needed fifty recent events plus optional labels for each user. The first version expressed everything as one LEFT JOIN. It returned the right rows, but on the plan I was running, the join lost the cheap reverse-order read and scanned far more history than the page required.

I eventually traced it to one setting: query_plan_read_in_order_through_join was disabled. The fix was to select the page first and enrich only those rows.

Before: JOIN before LIMIT

WITH tags_by_user AS
(
    SELECT user_id, groupArray(tag) AS tags
    FROM user_tags
    GROUP BY user_id
)
SELECT
    e.*,
    ifNull(t.tags, []) AS tags
FROM events AS e
LEFT JOIN tags_by_user AS t USING (user_id)
WHERE e.asset_id = {asset:String}
  AND (e.event_time, e.event_id) <
      ({cursor_time:DateTime}, {cursor_event_id:String})
ORDER BY e.event_time DESC, e.event_id DESC
LIMIT 50;

The labels were only decoration, but the query placed the join between the storage read and the final LIMIT. In the observed plan, ClickHouse read a broad event range, joined it, maintained the requested order, and only then returned fifty rows.

After: LIMIT before enrichment

Query one selects the page:

SELECT *
FROM events
WHERE asset_id = {asset:String}
  AND (event_time, event_id) <
      ({cursor_time:DateTime}, {cursor_event_id:String})
ORDER BY event_time DESC, event_id DESC
LIMIT 50;

The first page omits the cursor predicate. Each later page uses the previous page's last (event_time, event_id) pair. The tie-breaker must be stable and unique, and its parameter type must match the table.

Query two fetches labels only for users present on that page:

SELECT user_id, groupArray(tag) AS tags
FROM user_tags
WHERE user_id IN {page_users:Array(String)}
GROUP BY user_id;

The application merges the two small result sets by user_id. The large-table query can read in reverse primary-key order and stop after producing the page. The second query is bounded by at most the distinct users in those fifty rows.

BEFORE
Read matching event history in Default mode
  → LEFT JOIN tags
  → sort by event_time DESC, event_id DESC
  → LIMIT 50

AFTER
Read events in reverse primary-key order
  → LIMIT 50
  → fetch tags for users on that page
  → merge in memory

Why this plan lost the early stop

The event table is ordered by asset_id, time, and a tie-breaker. After the equality filter fixes asset_id, an ORDER BY event_time DESC, event_id DESC LIMIT 50 query can request an InReverseOrder read. Downstream cancellation stops the read once the limit has enough rows.

But the active session had query_plan_read_in_order_through_join = false. ClickHouse therefore stopped tracing the requested order when it reached the LEFT JOIN. The sort and limit stayed above the join, while the large table used a normal read.

Removing the join from the page query let the optimizer request InReverseOrder directly from storage again. One extra round trip was cheaper than reading and joining history the endpoint would never return.

Filtering is not enrichment

This rewrite is correct only when labels decorate an already selected page. If a label determines which events qualify, it must be applied before LIMIT. In that case I use a selective IN condition in the page query, then fetch the displayed labels separately.

The boundary is semantic: enrichment can happen after pagination; filtering cannot.

Verify the before and after

  • Run EXPLAIN PLAN actions = 1 and look for ReadType: InReverseOrder.
  • Check query_plan_read_in_order_through_join in the same user profile that runs the query.
  • Compare read_rows and read_bytes in system.query_log.

Rule of thumb

Select the bounded page before optional enrichment when the joined plan cannot preserve the early stop. Keep the join when ClickHouse proves it can do the same work in one ordered pipeline.