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

[amit-mittal]

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.

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

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[amit-mittal]

@xBis7 @ashb looks like this code was added as part of #43941. What do you think about the above issue?

[ashb]

Yeah looks like an index would be good

[xBis7]

The scheduler check shouldn't take place if otel is disabled. That's an overlook on my side but it can be fixed with a config check.

I agree that an index will improve performance.

I'll have to take a look but I think that we are scanning entire tables for the values SHOULD_END and NEEDS_CONTINUANCE. We can create a partial index for these two values for postgres and a full index for the other db impl.

Also, this column name is confusing because it will be used even if otel is disabled.

The column could also be added based on a config check. But I'm not familiar with all the possible Airflow setups and my question is, could we find ourselves in a scenario that leads to incompatibility issues?

[amit-mittal]

Thanks Christos and Ash, for confirming! To summarize, I guess we need two fixes:

1. The SELECT ... WHERE span_status = SHOULD_END statements should have been checking if otel metrics or tracing are enabled before executing.
2. If otel is enabled, using index for the above queries would help, so we will create the appropriate indices in the respective DB implementations.

Is there an ETA for the fix and do you have any recommendations in the meantime for the instances that have been migrated to Airflow 3?

[xBis7]

Is there an ETA for the fix

I've got some other priorities right now but I should be able to pick this up next week.

do you have any recommendations in the meantime for the instances that have been migrated to Airflow 3?

@ashb is more suited to answer that. I'll leave it to him.

[ashb]

do you have any recommendations in the meantime for the instances that have been migrated to Airflow 3?

The easisest thing to do today is to manually add the index to your database -- it existing won't cause any problems and will speed things up