Investigate high query execution time for pulling workflows

[MasterPtato]

https://cockroachlabs.cloud/cluster/f2d12815-7aec-41ca-a80b-0c18495376c7/statement/false/12460495546316681669

Investigation

Main query

EXPLAIN ANALYZE SELECT workflow_id, workflow_name, create_ts, ray_id, input, wake_deadline_ts
FROM db_workflow.workflows AS w
WHERE
    -- Not already complete
    output IS NULL AND
    -- No assigned node (not running)
    worker_instance_id IS NULL AND
    -- Check for wake condition
    (
        -- Immediate
        wake_immediate OR
        -- After deadline
        (
            wake_deadline_ts IS NOT NULL AND
            1727300638946 > wake_deadline_ts - 5001
        ) OR
        -- Signal exists
        (
            SELECT true
            FROM db_workflow.signals AS s
            WHERE
                s.workflow_id = w.workflow_id AND
                s.signal_name = ANY(w.wake_signals) AND
                s.ack_ts IS NULL
            LIMIT 1
        ) OR
        -- Tagged signal exists
        (
            SELECT true
            FROM db_workflow.tagged_signals AS s
            WHERE
                s.signal_name = ANY(w.wake_signals) AND
                s.tags <@ w.tags AND
                s.ack_ts IS NULL
            LIMIT 1
        ) OR
        -- Sub workflow completed
        (
            SELECT true
            FROM db_workflow.workflows AS w2
            WHERE
                w2.workflow_id = w.wake_sub_workflow_id AND
                output IS NOT NULL
        )
    )
LIMIT 50;

CPU time on max3: 190ms

Alterations:

• only immediate and deadline: 262µs
• only tagged_signals: 31ms
• only immediate and tagged_signals: 107ms
• only sub_workflows: 430µs
• only immediate and sub_workflows: 1ms
• only signals: 76µs
• only immediate and signals: 77ms
• using UNION instead of OR: 30ms

[linear[bot]]

RVT-3824 Investigate high query execution time for pulling workflows