Optimization: Convert remaining PostgreSQL queries to tuple cursors

Current Behavior

Two PostgreSQL pagination queries still use OR-based composite cursors that prevent the query planner from using composite index bounds. Pages degrade linearly as the cursor moves deeper because PostgreSQL scans and filters all preceding/following index entries instead of seeking directly.

1. getHistoryScheduledTasksQuery

File: common/persistence/sql/sqlplugin/postgresql/execution.go

Current OR-based cursor:

  AND ((visibility_timestamp >= $3 AND task_id >= $4) OR visibility_timestamp > $5)                                                                                                                                                       

The Go call site in RangeSelectFromHistoryScheduledTasks also passes InclusiveMinVisibilityTimestamp twice to fill both $3 and $5.

2. getHistoryNodesReverseQuery

File: common/persistence/sql/sqlplugin/postgresql/events.go

Current OR-based cursor:

  AND ((node_id = $5 AND txn_id < $6) OR node_id < $7)

Suggested Changes

1. getHistoryScheduledTasksQuery

From:

  AND ((visibility_timestamp >= $3 AND task_id >= $4) OR visibility_timestamp > $5)                                                                                                                                                       
  AND visibility_timestamp < $6
  ORDER BY visibility_timestamp,task_id LIMIT $7                                                                                                                                                                                          

To:

  AND (visibility_timestamp, task_id) >= ($3, $4)
  AND visibility_timestamp < $5
  ORDER BY visibility_timestamp,task_id LIMIT $6

Remove the duplicate InclusiveMinVisibilityTimestamp from the argument slice in RangeSelectFromHistoryScheduledTasks.

2. getHistoryNodesReverseQuery

From:

  AND node_id >= $4 AND ((node_id = $5 AND txn_id < $6) OR node_id < $7)
  ORDER BY shard_id, tree_id, branch_id DESC, node_id DESC, txn_id DESC LIMIT $8

To:

  AND node_id >= $4 AND (node_id, txn_id) < ($5, $6)
  ORDER BY shard_id, tree_id, branch_id DESC, node_id DESC, txn_id DESC LIMIT $7

Remove the duplicate parameter from the argument slice in RangeSelectFromHistoryNode for the reverse path.

Why

Row-value comparisons are semantically equivalent to the OR form but PostgreSQL recognizes them as composite index bounds. This allows the planner to seek directly to the cursor position instead of scanning the full index range on every page.

The reverse query affects GetWorkflowExecutionHistory with reverse order and workflow reset operations. The scheduled tasks query affects the scheduled task category pagination.

Checklist

• getHistoryScheduledTasksQuery in execution.go
• getHistoryNodesReverseQuery in events.go

Start in common/persistence/sql/sqlplugin/postgresql/execution.go at getHistoryScheduledTasksQuery and RangeSelectFromHistoryScheduledTasks, then inspect events.go at getHistoryNodesReverseQuery and RangeSelectFromHistoryNode. Verify the PostgreSQL row-value cursor predicates and parameter numbering for both pagination paths. Done means both OR-based cursors and duplicate arguments are removed while ordering, limits, and pagination behavior remain correct.