Dags list is not loading in the UI, espically when filtering with tags

[dor-bernstein]

Apache Airflow version

3.1.0

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

No response

What happened?

When clicking on the dags in the UI I get the first page of my dags as expected most of the time, sometimes it gets a timeout.
However when trying to filter on a specific tag the request gets timeout

What you think should happen instead?

the dags should be loaded

How to reproduce

Not sure, I suspect that it is because we have relatively a lot of entries in the serialized_dag table

Operating System

offical airflow docker image

Versions of Apache Airflow Providers

No response

Deployment

Official Apache Airflow Helm Chart

Deployment details

No response

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

[tirkarthi]

The tags filtering also uses the list dags endpoint passing the tags through query parameters. It doesn't seem to be using serialized dag model in the endpoint. Can you please add if you are seeing any slow queries in the backend? The total dags, dagruns and the amount of dags passing the filter will be also helpful.

airflow/airflow-core/src/airflow/api_fastapi/core_api/routes/public/dags.py

Line 83 in 1f976d0

def get_dags(

[dor-bernstein]

1. total dags - 571
2. dag runs - 84,118
3. dags passing the filter -32

I see some queries which are slow and are in this shape:

SELECT serialized_dag.data, serialized_dag.data_compressed, serialized_dag.id, serialized_dag.dag_id, serialized_dag.created_at, serialized_dag.last_updated, serialized_dag.dag_hash, serialized_dag.dag_version_id
FROM serialized_dag
WHERE serialized_dag.dag_version_id IN (SELECT task_instance.dag_version_id
FROM task_instance JOIN dag_run ON dag_run.dag_id = task_instance.dag_id AND dag_run.run_id = task_instance.run_id
WHERE dag_run.id IN (84225, 83924, 83642, 83081, 82660) AND serialized_dag.id != '01999979-0158-7c28-9714-c98c1b5891d1'::uuid)

The fix for this query actually is just moving the last predicate outside of the brackets like this:
SELECT serialized_dag.data, serialized_dag.data_compressed, serialized_dag.id, serialized_dag.dag_id, serialized_dag.created_at, serialized_dag.last_updated, serialized_dag.dag_hash, serialized_dag.dag_version_id
FROM serialized_dag
WHERE serialized_dag.dag_version_id IN (SELECT task_instance.dag_version_id
FROM task_instance JOIN dag_run ON dag_run.dag_id = task_instance.dag_id AND dag_run.run_id = task_instance.run_id
WHERE dag_run.id IN (84225, 83924, 83642, 83081, 82660))
AND serialized_dag.id != '01999979-0158-7c28-9714-c98c1b5891d1'::uuid

[dor-bernstein]

even without tags the dags list doesn't load some of the times. changed the title accordingly

[dor-bernstein]

@tirkarthi isn't this the path that is being used-

airflow/airflow-core/src/airflow/api_fastapi/core_api/routes/ui/dags.py

Line 84 in 1f976d0

def get_dags(

?

[hussein-awala]

The performance issue was already fixed by #55942 (your PR) and #56257.

It’s difficult to tell whether this is a side effect of serialized_dag query (which puts a lot of pressure on the DB) or a different issue. Would it be possible to build the main branch and test it in your deployment, or should we wait for 3.1.1?