Inngest Spans Cross-Run Query Issue #4656

Describe the bug
Self-hosted dashboard run-data loading performs a cross-run spans self-join, causing a huge and unnecessary performance issue.

GetSpansByRunIDsAndName filters m to requested run IDs, but joins s only
by dynamic_span_id:

FROM spans AS s
JOIN spans AS m ON m.dynamic_span_id = s.dynamic_span_id
WHERE m.name = $1
  AND m.run_id IN (SELECT UNNEST($2::TEXT[]))

dynamic_span_id is reused across many runs. Loading one run therefore
aggregates spans from tens of thousands of unrelated runs.

To Reproduce
Steps to reproduce the behavior:

1. Run self-hosted Inngest with PostgreSQL persistence.
2. Generate enough runs with executor.step spans.
3. Open dashboard run data for one recent run.
4. Capture active PostgreSQL queries or run EXPLAIN (ANALYZE, BUFFERS) for
   GetSpansByRunIDsAndName.

Observed for one requested run:

• m found 21 matching executor.step rows.
• s scanned 2,493,477 rows.
• Join removed 51,154,688 row pairs.
• Query returned 1,208,329 rows and 1,143,085 groups.
• Execution time was 30.584 seconds.

Example dynamic_span_id reuse:

dynamic_span_id: 0f99d15a6cde3cc7
distinct run_id values: 57,775
matching spans: 116,096

Expected behavior
Run-data loading for selected run IDs should only retrieve spans belonging to
those run IDs.

The self-join should constrain both keys:

JOIN spans AS m
  ON m.dynamic_span_id = s.dynamic_span_id
 AND m.run_id = s.run_id

This still permits follow-up span updates within the same run, without
aggregating unrelated runs sharing a deterministic dynamic_span_id.

Screenshots / Stack trace dump
Relevant plan summary:

Index Scan using idx_spans_run_id on spans s
actual rows=2,493,477

Rows Removed by Join Filter: 51,154,688

Execution Time: 30,583.897 ms

System info

• Inngest version: v1.36.0
• Deployment: self-hosted Kubernetes
• Persistence: RDS PostgreSQL and Redis/Valkey
• OS: Linux container runtime

Additional context

v1.38.1 still contains this GetSpansByRunIDsAndName join shape.

Related, but distinct, dashboard performance issues:

• Runs view very slow on large data sets #3612
• Dashboard /runs list creates excessive DB load from per-row app/function lookups #4326