Production evidence
Relaycast 8.1.2 removed the activity feeds temporary sort and reduced per-call D1 work from about 63.2M rows / 5.06s to about 440K rows / 30.5ms under the current one-hour production window. That is a major improvement, but the feed remains the top D1 reader.
A direct LIMIT 20 production probe still read 88,052 rows. EXPLAIN QUERY PLAN shows:
- SEARCH messages USING INDEX idx_messages_workspace
- SEARCH channels USING its primary-key index
- SEARCH agents USING its primary-key index
- SCAN dm_conversations LEFT-JOIN
The schema has idx_dm_conversations_workspace, while the feed joins dm_conversations on channel_id. The left join therefore rescans the DM table for each candidate message. A bounded messages subquery did not help (88,152 rows), confirming the join index is the remaining cost.
Proposed fix
- add an index on dm_conversations(channel_id), or a composite index whose leftmost column matches the join
- ship the D1 migration through the normal Relaycast / Relaycast Cloud release path
- add a conformance test that EXPLAIN uses the DM index and does not report SCAN dm_conversations
- compare rows_read and latency in production after deploy
This is a follow-up to the v8.1.2 activity-ordering fix; the original timeout incident is resolved, but this should remove the remaining dominant read amplification.
Production evidence
Relaycast 8.1.2 removed the activity feeds temporary sort and reduced per-call D1 work from about 63.2M rows / 5.06s to about 440K rows / 30.5ms under the current one-hour production window. That is a major improvement, but the feed remains the top D1 reader.
A direct LIMIT 20 production probe still read 88,052 rows. EXPLAIN QUERY PLAN shows:
The schema has idx_dm_conversations_workspace, while the feed joins dm_conversations on channel_id. The left join therefore rescans the DM table for each candidate message. A bounded messages subquery did not help (88,152 rows), confirming the join index is the remaining cost.
Proposed fix
This is a follow-up to the v8.1.2 activity-ordering fix; the original timeout incident is resolved, but this should remove the remaining dominant read amplification.