n+1 queries in public task instances endpoint #57430
Description
Apache Airflow version
main (development)
If "Other Airflow 2/3 version" selected, which one?
No response
What happened?
The task instances list page makes API call to public endpoint /api/v2/dags/~/dagRuns/~/taskInstances?limit=15&offset=0&order_by=-start_date&order_by=-run_after . This seems to result in n+1 query calls to task_instance_note and dag_bundle tables.
What you think should happen instead?
No response
How to reproduce
- Go to task instances at http://localhost:8000/task_instances with sql logging on. It seems the task_instance_note and dag_bundle are loaded per task instance resulting in n+1 queries.
[2025-10-28T13:59:33.566893Z] {base.py:1577} INFO - [cached since 767.7s ago] {'pk_1': '019a2af3-7135-71e8-a44f-7ec81d3e1879'}
[2025-10-28T13:59:33.567870Z] {base.py:1577} INFO - SELECT task_instance_note.ti_id AS task_instance_note_ti_id, task_instance_note.user_id AS task_instance_note_user_id, task_instance_note.content AS task_instance_note_content, task_instance_note.created_at AS task_instance_note_created_at, task_instance_note.updated_at AS task_instance_note_updated_at
FROM task_instance_note
WHERE task_instance_note.ti_id = %(pk_1)s
[2025-10-28T13:59:33.567982Z] {base.py:1577} INFO - [cached since 767.7s ago] {'pk_1': '019a2af3-7052-7932-8735-1fa38916dbbc'}
[2025-10-28T13:59:33.569449Z] {base.py:1577} INFO - SELECT task_instance_note.ti_id AS task_instance_note_ti_id, task_instance_note.user_id AS task_instance_note_user_id, task_instance_note.content AS task_instance_note_content, task_instance_note.created_at AS task_instance_note_created_at, task_instance_note.updated_at AS task_instance_note_updated_at
FROM task_instance_note
WHERE task_instance_note.ti_id = %(pk_1)s
[2025-10-28T13:59:33.569576Z] {base.py:1577} INFO - [cached since 767.7s ago] {'pk_1': '019a2af3-6f5e-76f3-b0c1-fd91b2e20b31'}
[2025-10-28T13:59:33.570635Z] {base.py:1577} INFO - SELECT task_instance_note.ti_id AS task_instance_note_ti_id, task_instance_note.user_id AS task_instance_note_user_id, task_instance_note.content AS task_instance_note_content, task_instance_note.created_at AS task_instance_note_created_at, task_instance_note.updated_at AS task_instance_note_updated_at
FROM task_instance_note
WHERE task_instance_note.ti_id = %(pk_1)s
[2025-10-28T13:59:33.570765Z] {base.py:1577} INFO - [cached since 767.7s ago] {'pk_1': '019a2af3-6ea1-7ea9-8bdc-ca86c3e04736'}
[2025-10-28T13:59:33.572143Z] {base.py:1577} INFO - SELECT task_instance_note.ti_id AS task_instance_note_ti_id, task_instance_note.user_id AS task_instance_note_user_id, task_instance_note.content AS task_instance_note_content, task_instance_note.created_at AS task_instance_note_created_at, task_instance_note.updated_at AS task_instance_note_updated_at
FROM task_instance_note
WHERE task_instance_note.ti_id = %(pk_1)s
[2025-10-28T13:59:33.572301Z] {base.py:1577} INFO - [cached since 767.7s ago] {'pk_1': '019a2af3-6dda-7264-8eb6-fd07ae66eab6'}
[2025-10-28T13:59:33.573980Z] {base.py:1577} INFO - SELECT task_instance_note.ti_id AS task_instance_note_ti_id, task_instance_note.user_id AS task_instance_note_user_id, task_instance_note.content AS task_instance_note_content, task_instance_note.created_at AS task_instance_note_created_at, task_instance_note.updated_at AS task_instance_note_updated_at
FROM task_instance_note
WHERE task_instance_note.ti_id = %(pk_1)s
[2025-10-28T13:59:33.574239Z] {base.py:1577} INFO - [cached since 767.7s ago] {'pk_1': '019a2af3-6d20-7c06-8b93-5715c907734f'}
[2025-10-28T13:59:33.575615Z] {base.py:1577} INFO - SELECT task_instance_note.ti_id AS task_instance_note_ti_id, task_instance_note.user_id AS task_instance_note_user_id, task_instance_note.content AS task_instance_note_content, task_instance_note.created_at AS task_instance_note_created_at, task_instance_note.updated_at AS task_instance_note_updated_at
FROM task_instance_note
WHERE task_instance_note.ti_id = %(pk_1)s
[2025-10-28T13:59:33.575732Z] {base.py:1577} INFO - [cached since 767.7s ago] {'pk_1': '019a2af3-6c46-7992-b181-426efeb776ac'}
[2025-10-28T13:59:33.576709Z] {base.py:1577} INFO - SELECT task_instance_note.ti_id AS task_instance_note_ti_id, task_instance_note.user_id AS task_instance_note_user_id, task_instance_note.content AS task_instance_note_content, task_instance_note.created_at AS task_instance_note_created_at, task_instance_note.updated_at AS task_instance_note_updated_at
FROM task_instance_note
WHERE task_instance_note.ti_id = %(pk_1)s
[2025-10-28T13:59:33.576829Z] {base.py:1577} INFO - [cached since 767.8s ago] {'pk_1': '019a2af2-ddd3-7415-a117-8cb99a8a869a'}
[2025-10-28T13:59:33.578889Z] {base.py:2624} INFO - BEGIN (implicit)
[2025-10-28T13:59:33.579304Z] {base.py:1577} INFO - SELECT dag_bundle.name, dag_bundle.active, dag_bundle.version, dag_bundle.last_refreshed, dag_bundle.signed_url_template, dag_bundle.template_params
FROM dag_bundle
WHERE dag_bundle.name = %(name_1)s
[2025-10-28T13:59:33.579472Z] {base.py:1577} INFO - [cached since 767.7s ago] {'name_1': 'dags-folder-1'}
[2025-10-28T13:59:33.580606Z] {base.py:2630} INFO - COMMIT
[2025-10-28T13:59:33.582010Z] {base.py:2624} INFO - BEGIN (implicit)
[2025-10-28T13:59:33.582465Z] {base.py:1577} INFO - SELECT dag_bundle.name, dag_bundle.active, dag_bundle.version, dag_bundle.last_refreshed, dag_bundle.signed_url_template, dag_bundle.template_params
FROM dag_bundle
WHERE dag_bundle.name = %(name_1)s
[2025-10-28T13:59:33.582646Z] {base.py:1577} INFO - [cached since 767.7s ago] {'name_1': 'dags-folder-1'}
[2025-10-28T13:59:33.583843Z] {base.py:2630} INFO - COMMIT
[2025-10-28T13:59:33.585247Z] {base.py:2624} INFO - BEGIN (implicit)
[2025-10-28T13:59:33.585652Z] {base.py:1577} INFO - SELECT dag_bundle.name, dag_bundle.active, dag_bundle.version, dag_bundle.last_refreshed, dag_bundle.signed_url_template, dag_bundle.template_params
FROM dag_bundle
WHERE dag_bundle.name = %(name_1)s
[2025-10-28T13:59:33.585831Z] {base.py:1577} INFO - [cached since 767.7s ago] {'name_1': 'dags-folder-1'}
[2025-10-28T13:59:33.587085Z] {base.py:2630} INFO - COMMIT
[2025-10-28T13:59:33.588589Z] {base.py:2624} INFO - BEGIN (implicit)
[2025-10-28T13:59:33.589071Z] {base.py:1577} INFO - SELECT dag_bundle.name, dag_bundle.active, dag_bundle.version, dag_bundle.last_refreshed, dag_bundle.signed_url_template, dag_bundle.template_params
FROM dag_bundle
WHERE dag_bundle.name = %(name_1)s
Operating System
Ubuntu 20.04
Versions of Apache Airflow Providers
No response
Deployment
Virtualenv installation
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