lsm / neokai / 25646971668

82%

push

github

perf(daemon): index-targeted lookups for hot sdk_messages LiveQueries (#1855)

* perf(daemon): index-targeted lookups for hot sdk_messages LiveQueries

Three fixes on top of PR #1853:

1. getUserMessageByUuid: replace load-all-user-rows + JS scan with a single
   indexed lookup on (session_id, message_type, json_extract uuid). Used by
   the rewind handler on every checkpoint click.

2. SPACE_TASK_ACTIVITY_BY_TASK_SQL message_stats CTE: rewrite the GROUP BY
   join (which forced a full scan of idx_sdk_messages_session_id) as two
   correlated scalar subqueries over unique_session_ids so SQLite picks
   per-session index lookups instead.

3. SPACE_TASK_MESSAGES_BASE_CTE turnUserMessageId: replace the per-row
   correlated subquery (O(N x U)) with a MAX() OVER window function and a
   LEFT JOIN to user_row_starts. SQLite has no LAST_VALUE IGNORE NULLS so
   we carry rowPos as an integer surrogate and resolve back to the user
   message id via the join.

Benchmarked against ~/.neokai/data/daemon.db on 5 representative tasks
(3 runs each, warmup excluded):

| query        | task     | rows | before (ms) | after (ms) | speedup |
| ------------ | -------- | ---: | ----------: | ---------: | ------: |
| activity     | 9f7038f7 |    3 |       319.9 |        0.9 |  363x   |
| compact      | 9f7038f7 | 1537 |      1406.4 |      323.5 |   4.3x  |
| active-turn  | 9f7038f7 | 1421 |      1361.6 |      302.3 |   4.5x  |
| activity     | c1525ca4 |    2 |       289.5 |        0.7 |  413x   |
| compact      | c1525ca4 |  864 |      1981.6 |      418.6 |   4.7x  |
| active-turn  | c1525ca4 |  792 |      1878.0 |      423.7 |   4.4x  |
| activity     | 31ba3d09 |    1 |       292.6 |        0.7 |  418x   |
| compact      | 31ba3d09 |  728 |      1135.5 |      271.6 |   4.2x  |
| active-turn  | 31ba3d09 |  709 |      1098.1 |      256.4 |   4.3x  |
| activity     | 485f6fec |    1 |       280.8 |        0.8 |  351x   |
| compact      | 485f6fec |  812 |      1421.2 |   ... (continued)

8347 of 11648 branches covered (71.66%)

Branch coverage included in aggregate %.

37 of 37 new or added lines in 1 file covered. (100.0%)

65425 of 77828 relevant lines covered (84.06%)

289.73 hits per line