GOAL: find the id, kind with most occurrences, and then count occurrences per 20 second time group. I've added 4 variations on the same query here, a) recursive CTE, b) two separate queries, c) query using an alias for the summary, d) similar to (c) but removes the UNION ALL. Only query (b) makes use of the primary key for the time series query.

The recursive CTE scans 150.02k (where is the 0.02k from?) rows, multi step query scans 50k+8.19k rows. Aliased query scans 150k rows. Could recursive CTE support optimizing the recursion step when primary key fields are included in the where? Or is there a better way I could handle this query pattern?

For the real world use case, we are using these style queries (currently (b)) for performing analytics on opentelemetry traces, and logs. The multi query pattern has some additional benefits where we can infer primary key fields used by grouping them during the summary query, and then filtering them down in the timeseries query.

CREATE TABLE events (
    time DateTime,
    id String,
    kind String
)
ENGINE=MergeTree
ORDER BY (kind, id, toUnixTimestamp(time));

INSERT INTO events
    SELECT now() - toIntervalSecond(mod(number, 300)),
           concat('item ', randPoisson(10)),
           concat('kind ', randPoisson(10))
    FROM numbers(50000);



-- expressed as recursive cte
WITH RECURSIVE analytics AS (
    SELECT id,
           kind,
           count() AS c,
           CAST(NULL, 'Nullable(DateTime)') AS timestamp
    FROM events
    GROUP BY id, kind
    ORDER BY c DESC
    LIMIT 1
  UNION ALL
    SELECT id,
           kind,
           count() AS c,
           toStartOfInterval(time, toIntervalSecond(20)) AS timestamp
    FROM events AS t, analytics AS s
    WHERE s.id = t.id
      AND s.timestamp IS NULL
      AND s.kind = t.kind
    GROUP BY timestamp, id, kind
)
SELECT *
FROM analytics
ORDER BY timestamp DESC, c DESC

   ┌─id──────┬─kind────┬────c─┬───────────timestamp─┐
1. │ item 10 │ kind 10 │   21 │ 2024-05-18 13:26:40 │
2. │ item 10 │ kind 10 │  427 │ 2024-05-18 13:26:20 │
3. │ item 10 │ kind 10 │ 1931 │ 2024-05-18 13:26:00 │
4. │ item 10 │ kind 10 │ 2644 │ 2024-05-18 13:25:40 │
5. │ item 10 │ kind 10 │ 1158 │ 2024-05-18 13:25:20 │
6. │ item 10 │ kind 10 │  150 │ 2024-05-18 13:25:00 │
7. │ item 10 │ kind 10 │    9 │ 2024-05-18 13:24:40 │
8. │ item 10 │ kind 10 │ 6340 │                ᴺᵁᴸᴸ │
   └─────────┴─────────┴──────┴─────────────────────┘

8 rows in set. Elapsed: 0.026 sec. Processed 150.02 thousand rows, 5.06 MB (5.87 million rows/s., 198.05 MB/s.)
Peak memory usage: 503.98 KiB.

-- 150k rows processed, or 3 full table scans



-- expressed as two queries, one for summary, one for results
-- step 1, summary query
SELECT
    id,
    kind,
    count() AS c,
    CAST(NULL, 'Nullable(DateTime)') AS timestamp
FROM events
GROUP BY
    id,
    kind
ORDER BY c DESC
LIMIT 1

Query id: 41e30428-d229-4496-8be8-63cce5c84561

   ┌─id──────┬─kind────┬────c─┬─timestamp─┐
1. │ item 10 │ kind 10 │ 6340 │      ᴺᵁᴸᴸ │
   └─────────┴─────────┴──────┴───────────┘

1 row in set. Elapsed: 0.009 sec. Processed 50.00 thousand rows, 1.55 MB (5.81 million rows/s., 180.59 MB/s.)
Peak memory usage: 1.13 KiB.


-- step 2, timeseries query
SELECT
    id,
    kind,
    count() AS c,
    toStartOfInterval(time, toIntervalSecond(20)) AS timestamp
FROM events AS t
WHERE (id = 'item 10') AND (kind = 'kind 10')
GROUP BY
    timestamp,
    id,
    kind

Query id: 9d184301-8da9-49e8-aa1c-79daf1763abe

   ┌─id──────┬─kind────┬────c─┬───────────timestamp─┐
1. │ item 10 │ kind 10 │    9 │ 2024-05-18 13:24:40 │
2. │ item 10 │ kind 10 │ 1158 │ 2024-05-18 13:25:20 │
3. │ item 10 │ kind 10 │ 2644 │ 2024-05-18 13:25:40 │
4. │ item 10 │ kind 10 │  150 │ 2024-05-18 13:25:00 │
5. │ item 10 │ kind 10 │ 1931 │ 2024-05-18 13:26:00 │
6. │ item 10 │ kind 10 │  427 │ 2024-05-18 13:26:20 │
7. │ item 10 │ kind 10 │   21 │ 2024-05-18 13:26:40 │
   └─────────┴─────────┴──────┴─────────────────────┘

7 rows in set. Elapsed: 0.005 sec. Processed 8.19 thousand rows, 294.87 KB (1.72 million rows/s., 61.78 MB/s.)
Peak memory usage: 1.34 KiB.


-- expressed as single query using summary alias. with UNION ALL = 150k rows processed, without = 100k rows processed
WITH summary AS
    (
        SELECT
            id,
            kind,
            count() AS c,
            CAST(NULL, 'Nullable(DateTime)') AS ts
        FROM events
        GROUP BY
            id,
            kind
        ORDER BY c DESC
        LIMIT 1
    )
SELECT
    id,
    kind,
    count() AS c,
    toStartOfInterval(time, toIntervalSecond(20)) AS timestamp
FROM events AS t, summary AS s
WHERE (t.id = s.id) AND (s.ts IS NULL) AND (s.kind = t.kind)
GROUP BY
    timestamp,
    id,
    kind
UNION ALL
SELECT *
FROM summary

Query id: 7a630262-e310-4695-9af5-dab99f1c9834

   ┌─id──────┬─kind────┬────c─┬─timestamp─┐
1. │ item 10 │ kind 10 │ 6340 │      ᴺᵁᴸᴸ │
   └─────────┴─────────┴──────┴───────────┘
   ┌─id──────┬─kind────┬────c─┬───────────timestamp─┐
2. │ item 10 │ kind 10 │    9 │ 2024-05-18 13:24:40 │
3. │ item 10 │ kind 10 │ 1158 │ 2024-05-18 13:25:20 │
4. │ item 10 │ kind 10 │ 2644 │ 2024-05-18 13:25:40 │
5. │ item 10 │ kind 10 │  150 │ 2024-05-18 13:25:00 │
6. │ item 10 │ kind 10 │ 1931 │ 2024-05-18 13:26:00 │
7. │ item 10 │ kind 10 │  427 │ 2024-05-18 13:26:20 │
8. │ item 10 │ kind 10 │   21 │ 2024-05-18 13:26:40 │
   └─────────┴─────────┴──────┴─────────────────────┘

8 rows in set. Elapsed: 0.019 sec. Processed 150.00 thousand rows, 4.86 MB (7.86 million rows/s., 254.81 MB/s.)
Peak memory usage: 3.12 KiB.



-- 4th variation, use anyIf to get result of summary query without UNION ALL
WITH summary AS
    (
        SELECT
            id,
            kind,
            count() AS c,
            CAST(NULL, 'Nullable(DateTime)') AS ts
        FROM events
        GROUP BY
            id,
            kind
        ORDER BY c DESC
        LIMIT 1
    )
SELECT
    id,
    kind,
    count() AS c,
    toStartOfInterval(time, toIntervalSecond(20)) AS timestamp,
    anyIf(s.c, (s.id = t.id) AND (s.kind = t.kind))
FROM events AS t, summary AS s
WHERE (t.id = s.id) AND (s.ts IS NULL) AND (s.kind = t.kind)
GROUP BY
    timestamp,
    id,
    kind

Query id: ccbb0d55-b901-48ff-9e69-21bdd6b3ef14

   ┌─id──────┬─kind────┬────c─┬───────────timestamp─┬─anyIf(s.c, and(equals(s.id, id), equals(s.kind, kind)))─┐
1. │ item 10 │ kind 10 │  222 │ 2024-05-18 14:35:40 │                                                    6240 │
2. │ item 10 │ kind 10 │   12 │ 2024-05-18 14:37:20 │                                                    6240 │
3. │ item 10 │ kind 10 │ 1417 │ 2024-05-18 14:36:00 │                                                    6240 │
4. │ item 10 │ kind 10 │  275 │ 2024-05-18 14:37:00 │                                                    6240 │
5. │ item 10 │ kind 10 │ 2787 │ 2024-05-18 14:36:20 │                                                    6240 │
6. │ item 10 │ kind 10 │ 1517 │ 2024-05-18 14:36:40 │                                                    6240 │
7. │ item 10 │ kind 10 │   10 │ 2024-05-18 14:35:20 │                                                    6240 │
   └─────────┴─────────┴──────┴─────────────────────┴─────────────────────────────────────────────────────────┘

7 rows in set. Elapsed: 0.018 sec. Processed 100.00 thousand rows, 3.31 MB (5.48 million rows/s., 181.16 MB/s.)
Peak memory usage: 4.30 MiB.

Probably blocked by the same limitation like here #55058 (comment)

Check those approaches as well https://kb.altinity.com/altinity-kb-queries-and-syntax/top-n-and-remain/

Your particular query also can be done using "scalar" instead of cte.

WITH (
        SELECT (id, kind, count() AS c)
        FROM events
        GROUP BY
            id,
            kind
        ORDER BY c DESC
        LIMIT 1
    ) AS s
SELECT
    id,
    kind,
    count() AS c,
    toStartOfInterval(time, toIntervalSecond(20)) AS timestamp,
    s.3
FROM events AS t
WHERE (t.id = (s.1)) AND ((s.2) = t.kind)
GROUP BY
    timestamp,
    id,
    kind

Query id: 2161d7c6-24da-4890-b422-44902fb00a69

┌─id─────┬─kind───┬───c─┬───────────timestamp─┬─tupleElement(s, 3)─┐
│ item 9 │ kind 9 │ 442 │ 2024-05-22 02:16:40 │               6249 │
│ item 9 │ kind 9 │ 448 │ 2024-05-22 02:14:20 │               6249 │
│ item 9 │ kind 9 │ 403 │ 2024-05-22 02:15:00 │               6249 │
│ item 9 │ kind 9 │ 393 │ 2024-05-22 02:15:20 │               6249 │
│ item 9 │ kind 9 │ 409 │ 2024-05-22 02:14:00 │               6249 │
│ item 9 │ kind 9 │ 133 │ 2024-05-22 02:12:20 │               6249 │
│ item 9 │ kind 9 │ 415 │ 2024-05-22 02:12:40 │               6249 │
│ item 9 │ kind 9 │ 427 │ 2024-05-22 02:16:00 │               6249 │
│ item 9 │ kind 9 │ 294 │ 2024-05-22 02:17:20 │               6249 │
│ item 9 │ kind 9 │ 411 │ 2024-05-22 02:13:40 │               6249 │
│ item 9 │ kind 9 │ 410 │ 2024-05-22 02:15:40 │               6249 │
│ item 9 │ kind 9 │ 413 │ 2024-05-22 02:14:40 │               6249 │
│ item 9 │ kind 9 │ 411 │ 2024-05-22 02:17:00 │               6249 │
│ item 9 │ kind 9 │ 399 │ 2024-05-22 02:13:00 │               6249 │
│ item 9 │ kind 9 │ 396 │ 2024-05-22 02:16:20 │               6249 │
│ item 9 │ kind 9 │ 445 │ 2024-05-22 02:13:20 │               6249 │
└────────┴────────┴─────┴─────────────────────┴────────────────────┘