Overview

Backports the four cNIGHT db-sync observation perf commits from PR #1365 (which landed on release/node-0.22.5) onto main. The companion #934 (multi-asset cache) is already on main as 17ad5e8b, so this PR contains only the perf delta.

Net effect: significantly lower postgres CPU / query latency during cNIGHT observation, no behavioural change.

Commits

• Increase performance of cNight db-sync queries — pre-queries coarse tx / tx_out / ma_tx_out id bounds for the block-range window, then constrains the four observation queries (registration / deregistration / asset-create / asset-spend) by primary-key range. Postgres can prune rows before the expensive joins. Introduces PagedQuery and QueryBounds.
• Add tx_in indexes as well — adds the tx.id bounding to the tx_in-keyed queries (spend / deregistration) so they benefit from the same pruning.
• refactor: use sqlx::query_as! macro for cNight observation queries — restores compile-time query verification against the db-sync schema via the .sqlx prepared cache.
• chore: replace sqlx::query_as! with runtime form in get_block_by_hash — needed because the cherry-pick landed in a branch whose .sqlx cache key changed after trailing-whitespace normalisation; runtime form avoids the compile-time DB check.
• fix: should be debug not warn — drops the leftover log::warn!("Bounds:…") from the perf commit down to debug!.

Supersedes / overlaps

• Overlaps Upstream node-0.22.5-rc.2 changes #1377 (feat/fiwcakym-upstream-node-0.22.5-rc.2 — Upstream node-0.22.5-rc.2 changes, 516 files, automated bulk upstream). Upstream node-0.22.5-rc.2 changes #1377 contains the same four perf commits as a subset. This PR is the targeted minimal backport so the perf wins can land independently of the bigger upstream sweep — once merged, the corresponding commits in Upstream node-0.22.5-rc.2 changes #1377 will become no-ops on rebase.
• Conflicts with feat: reduce data queried by 16x #1367 (ozgb-reduce-cnight-query-capacity — feat: reduce data queried by 16x). Both touch primitives/mainchain-follower/src/data_source/cnight_observation.rs. Whichever lands second will need a small rebase; feat: reduce data queried by 16x #1367 changes the tx_capacity * 64 overestimate factor, which is on a line near (but not the same as) the bounds-query block introduced here.

Resolution note

The cherry-pick of the Increase performance commit conflicted on data_source/cnight_observation.rs: main has the start_sub_query_timer metrics infrastructure (added after release/node-0.22.5 branched), so the new bounds queries were wrapped in matching cnight_get_low_bounds / cnight_get_high_bounds timers for consistency with the rest of that function.

🗹 TODO before merging

• Ready

📌 Submission Checklist

• All commits are signed off (git commit -s) for the DCO
• Changes are backward-compatible (or flagged if breaking)
• Pull request description explains why the change is needed
• Self-reviewed the diff
• I have included a change file (changes/changed/cnight-query-coarse-bounds.md)
• If the changes introduce a new feature, I have bumped the node minor version
• Update documentation (if relevant) — N/A, no public API change
• Updated AGENTS.md if build commands, architecture, or workflows changed — N/A
• No new todos introduced

🧪 Testing Evidence

Observed reduced db-sync query times on a live cNIGHT observation workload (originally measured against release/node-0.22.5 on PR #1365).

• Additional tests are provided (if possible)

🔱 Fork Strategy

• Node Runtime Update
• Node Client Update
• Other:
• N/A

Links

• Upstream merged into release: Conservative improvements to syncing #1365
• Originated: Add cache for multi asset id to avoid excessive joins #934 (multi-asset cache, already on main as 17ad5e8b)
• Overlapping bulk upstream: Upstream node-0.22.5-rc.2 changes #1377
• Adjacent (will conflict): feat: reduce data queried by 16x #1367

#1429