[Bug]: /api/runs/list sorts every job submission of the listed runs #4468

Steps to reproduce

1. Have runs with many job submissions (retries, replicas, redeployments).
2. Call POST /api/runs/list.

Actual behaviour

On dstack Sky, slow /api/runs/list requests take 0.5-2.2 s. Most of it is loading jobs: the first jobs query takes 0.6-1.8 s and a second one ~100 ms, while the runs query takes 15-100 ms.

To return the latest job_submissions_limit submissions per job, the server numbers every submission of the listed runs with row_number() OVER (PARTITION BY run_id, replica_num, job_num ORDER BY submission_num DESC), selecting all columns, and keeps those numbered up to N:

So the database sorts every submission with all its columns, including job_spec_data, job_provisioning_data and job_runtime_data, to return one or a few per job. It does this twice, the second time for the latest submission with a termination reason:

The UI, dstack ps and Client.runs.list() pass job_submissions_limit=1. Callers that omit it, such as other API clients and CLI versions from before #3986, get 10:

Expected behaviour

The cost of loading jobs depends on the number of submissions returned, not on how many each job has accumulated.

dstack version

0.22.7

Server logs

n/a

Additional information

Proposed fix: one query that finds each job's latest submission numbers and fetches only the submissions within the last N:

SELECT jobs.*
FROM jobs
JOIN (
    SELECT run_id, replica_num, job_num,
           max(submission_num) AS latest,
           max(CASE WHEN termination_reason IS NOT NULL THEN submission_num END) AS latest_terminated
    FROM jobs
    WHERE run_id IN (:run_ids)
    GROUP BY run_id, replica_num, job_num
) l
  ON jobs.run_id = l.run_id AND jobs.replica_num = l.replica_num AND jobs.job_num = l.job_num
WHERE jobs.submission_num > l.latest - :n
   OR jobs.submission_num = l.latest_terminated

The subquery groups four narrow columns and needs no sort, and full rows are read only for the returned submissions. It replaces both queries, and runs on SQLite and Postgres. An index on (run_id, replica_num, job_num, submission_num) would let both parts read only what they need.

submission_num > latest - N is exact for N=1, which is what the UI and CLI use. For N>1 it can return fewer than N submissions, because submission numbers have gaps where no-capacity submissions were deleted:

That fits the API, which promises at most job_submissions_limit latest submissions per job, and only drops submissions older than deleted ones.

Getting exactly N per job needs per-job ranking, and every way to do it is either costly or complex: a window function sorting all submissions (today), a window over narrow columns joined back to jobs, a correlated count per submission, a LATERAL join with LIMIT N (Postgres-only, so SQLite needs another code path), or loading narrow rows for every submission into Python.