tirkarthi opened a new issue, #57241:
URL: https://github.com/apache/airflow/issues/57241
### Apache Airflow version
main (development)
### If "Other Airflow 2/3 version" selected, which one?
_No response_
### What happened?
On loading the dags list page it seems the tags are not queried using joins
and a query is made during serialization of each dag to fetch the tags for a
dag. Though dag_id column itself is indexed in dag_tag table the tags could be
fetched as part of joins.
```sql
CREATE TABLE `dag_tag` (
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`dag_id` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
PRIMARY KEY (`name`,`dag_id`),
KEY `idx_dag_tag_dag_id` (`dag_id`),
CONSTRAINT `dag_tag_dag_id_fkey` FOREIGN KEY (`dag_id`) REFERENCES `dag`
(`dag_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```
```
[2025-10-25T12:00:41.297540Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.297834Z] {base.py:1577} INFO - [cached since 0.005695s
ago] ('dag_1',)
[2025-10-25T12:00:41.299771Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.299950Z] {base.py:1577} INFO - [cached since 0.007819s
ago] ('dag_10',)
[2025-10-25T12:00:41.301762Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.301936Z] {base.py:1577} INFO - [cached since 0.009806s
ago] ('dag_100',)
[2025-10-25T12:00:41.303630Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.303808Z] {base.py:1577} INFO - [cached since 0.01168s
ago] ('dag_101',)
[2025-10-25T12:00:41.305534Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.305713Z] {base.py:1577} INFO - [cached since 0.01358s
ago] ('dag_102',)
[2025-10-25T12:00:41.307352Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.307524Z] {base.py:1577} INFO - [cached since 0.0154s
ago] ('dag_103',)
[2025-10-25T12:00:41.309495Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.309698Z] {base.py:1577} INFO - [cached since 0.01756s
ago] ('dag_104',)
[2025-10-25T12:00:41.312336Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.312713Z] {base.py:1577} INFO - [cached since 0.02056s
ago] ('dag_105',)
[2025-10-25T12:00:41.315968Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.316178Z] {base.py:1577} INFO - [cached since 0.02404s
ago] ('dag_106',)
[2025-10-25T12:00:41.318186Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.318400Z] {base.py:1577} INFO - [cached since 0.02626s
ago] ('dag_107',)
[2025-10-25T12:00:41.320469Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.320677Z] {base.py:1577} INFO - [cached since 0.02854s
ago] ('dag_108',)
[2025-10-25T12:00:41.322693Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.322895Z] {base.py:1577} INFO - [cached since 0.03075s
ago] ('dag_109',)
[2025-10-25T12:00:41.324665Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
[2025-10-25T12:00:41.324910Z] {base.py:1577} INFO - [cached since 0.03276s
ago] ('dag_11',)
[2025-10-25T12:00:41.326889Z] {base.py:1577} INFO - SELECT dag_tag.name AS
dag_tag_name, dag_tag.dag_id AS dag_tag_dag_id
FROM dag_tag
WHERE %s = dag_tag.dag_id
```
### What you think should happen instead?
_No response_
### How to reproduce
1. Create 500 dags with each dag corresponding to a tag.
2. Load the dagslist page with echo as True to sqlalchemy engine args to
view the tags query being made per dag in api-server logs.
```
[sqlalchemy]
sql_alchemy_engine_args = {"echo": true}
```
```python
for index in range(500):
code = f"""
from datetime import datetime
from airflow.sdk import DAG
from airflow.providers.standard.operators.bash import BashOperator
with DAG(
dag_id="dag_{index}",
schedule="@continuous",
max_active_runs=1,
catchup=False,
tags=["tag_{index}"]
):
task1 = BashOperator(task_id="task1", bash_command="echo dag_{index}")
"""
with open(f"dag_{index}.py", "w") as f:
f.write(code)
```
### Operating System
Ubuntu 20.04
### 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
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
