n+1 queries in public task instances endpoint

[tirkarthi]

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

1. 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

[tirkarthi]

cc: @pierrejeambrun

[rawwar]

@pierrejeambrun , Shall I pick this up? Double checking if you have already started working on this.

[pierrejeambrun]

I'm working on that already.

If you want you can do the exact thing i'm doing for the public folder for the ui one. Take every single entities, and make sure to add query guards in test and resolve any N+1 queries problem we might have there.

[pierrejeambrun]

Some examples:

• Add number of queries guard for public hitls #57549
• Add number of queries guard in public import jobs endpoints #57559
• Add number of queries guard in public import errors list endpoints #57557

[rawwar]

I'll create an issue and track for ui endpoints in that.