Scheduler querying `dag_run` and `task_instance` tables filtering by `span_status`

### Apache Airflow version

3.0.2

### If "Other Airflow 2 version" selected, which one?

_No response_

### What happened?

After upgrading to Airflow v3, we are noticing that Airflow Scheduler is continuously querying `dag_run` and `task_instance` tables with `.... WHERE span_status = SHOULD_END`. Since there is no index created for `span_status`, it means all the rows in these two tables are being scanned continuously. And since both these tables contain historical runs, these are pretty big and causing performance issues.

I still have to dig deeper, if this is related, but "Task Instances" tab takes minutes to load.

### What you think should happen instead?

- We should create an index on "span_status" column.
- Also, this column name is confusing because it will be used even if `otel` is disabled.

### How to reproduce

Airflow Scheduler is continuously looping as seen from the [code](https://github.com/apache/airflow/blob/b860dd17b215145e238982db3d62f3b6dd0b0057/airflow-core/src/airflow/jobs/scheduler_job_runner.py#L1096).

### Operating System

Docker

### Versions of Apache Airflow Providers

_No response_

### Deployment

Other Docker-based deployment

### Deployment details

Running as docker container

### Anything else?

_No response_

### Are you willing to submit PR?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)

Start with the scheduler loop at airflow-core/src/airflow/jobs/scheduler_job_runner.py line 1096 and trace the queries against the dag_run and task_instance tables. Confirm the span_status query plans and scheduler impact, then determine whether indexing and clearer naming address the issue; done means the repeated queries no longer cause broad historical-table scans and the behavior is covered by relevant regression checks.

Written by the indexing model from the issue text.