lsm / HyperNeo / 31197913287

40%

push

github

perf(daemon): drop redundant sdk_messages indexes; use sdk_uuid column [#2335] (#2357)

* perf(daemon): drop redundant sdk_messages indexes; use sdk_uuid column [#2335]

The three uuid lookups (getMessageByStatusAndUuid, getUserMessageByUuid,
updateHyperNeoActionMessageByUuid) filtered on json_extract(sdk_message,'$.uuid'),
backed by the M113 expression index idx_sdk_messages_uuid_status. The M163
column index idx_sdk_messages_session_uuid (session_id, sdk_uuid) supersedes it,
so switch the lookups to filter on sdk_uuid directly — EXPLAIN confirms each
seeks the 2-column index — and drop the expression index. Also drop
idx_sdk_messages_session (session_id, timestamp), a strict prefix of
idx_sdk_messages_session_timestamp_id, updated on every insert into the
multi-million-row table for no query benefit.

getUserMessageByUuid's per-send_status probe loop + NULL fallback existed solely
to work around the old index's leading-column rule, so it collapses to a single
seek; the chronologically-earliest-match determinism rewind depends on is
preserved. For getMessageByStatusAndUuid the vestigial ORDER BY timestamp ASC is
dropped (sdk_uuid is unique per session, so there is at most one match) because
it steered the planner to the timestamp index instead of the sdk_uuid seek.

Migration 170 drops both indexes for existing databases; new databases never
receive them (createIndexes no longer creates them). EXPLAIN tests guard the
three lookups against regressing to a non-sargable form; a migration test
confirms the drops are idempotent and leave the column index intact.

* test(daemon): update live-query plan comment for dropped session index [#2335]

The top-level-window plan comment still offered the shorter
idx_sdk_messages_session as an index the planner "may pick", but migration 170
dropped it — only idx_sdk_messages_session_timestamp_id remains. Mirrors the
parallel comment already fixed in live-query-handlers.ts. Test-only; the regex
assertion is unchanged... (continued)

19417 of 55441 branches covered (35.02%)

Branch coverage included in aggregate %.

29137 of 65934 relevant lines covered (44.19%)

278.4 hits per line