jobs: crdb_internal.jobs and crdb_internal.system_jobs perform poorly

[stevendanna]

Describe the problem

As part of a larger project to reduce job system contention in the long term. The payload and progress of all jobs were moved from system.jobs to system.job_info.

While the eventual goal is that control interfaces would not require touching system.job_info, that is not true for the interim state we are currently in.

Most notably, crdb_internal.system_jobs and crdb_internal.jobs (which power commands such as SHOW JOBS and the Jobs page on the DB Console) require reading from system.job_info. And, in the worst case, requires 2 full scans of system.jobs_info which can be several GB in size in large clusters.

The long-term fix for this is two-fold:

1. Move data required to show basic job status out of the job's payload and progress and into a different table.
2. Re-write crdb_internal.system_jobs and crdb_internal.jobs as virtual views rather than virtual tables. A virtual view allows the optimizer to plan much more efficient queries and should be able to eliminate the full scans in many more cases.

Ongoing work towards this plan

• jobs: avoid CTEs in crdb_internal.system_jobs query #123848 Avoid CTE which pessimises query plan (folded into below PR as well)
• sql,jobs: convert crdb_internal.system_jobs to a view #119081

Related work-arounds and issues:

• jobs: add job_id index to crdb_internal.jobs virtual table  #118638 (backport still required)
• jobs: avoid crdb_internal.system_jobs in gc-jobs #108093
• upgrades: avoid crdb_internal.system_jobs in upgrade manager #108080
• bench,jobs: add more jobs rttanalysis tests #119574
• jobs: reduce round trips for control queries #119355

Jira issue: CRDB-38028