API - Improve public get_dag_runs endpoint

[pierrejeambrun]

On big installation the "get_dag_runs" endpoint to list all the dagruns in the UI is taking a long time to return a response.

The critical part of the code is the actual db query. Most likely due to the number of joins creating rows explosion. We need to optimize the query, most likely by double checking the eager loading options and verify that there is no row explosion (joinedload vs selectinload) cause by a wrong option. Also we can add load_only to limit the number of columns selected in each joins.

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

Lines 387 to 421 in 4e75919

	query = select(DagRun).options(*eager_load_dag_run_for_validation())
	if dag_id != "~":
	get_latest_version_of_dag(dag_bag, dag_id, session) # Check if the DAG exists.
	query = query.filter(DagRun.dag_id == dag_id).options()
	# Add join with DagVersion if dag_version filter is active
	if dag_version.value:
	query = query.join(DagVersion, DagRun.created_dag_version_id == DagVersion.id)
	dag_run_select, total_entries = paginated_select(
	statement=query,
	filters=[
	run_after,
	logical_date,
	start_date_range,
	end_date_range,
	update_at_range,
	duration_range,
	conf_contains,
	state,
	run_type,
	dag_version,
	readable_dag_runs_filter,
	run_id_pattern,
	triggering_user_name_pattern,
	dag_id_pattern,
	partition_key_pattern,
	],
	order_by=order_by,
	offset=offset,
	limit=limit,
	session=session,
	)
	dag_runs = session.scalars(dag_run_select)

Also needs investigation to check if an extra index could help.

Screen.Recording.2026-02-16.at.16.22.49.mov

Committer

• I acknowledge that I am a maintainer/committer of the Apache Airflow project.

[ObinnaIheanachor]

Hi! I’d like to take this on. I’ll start by reproducing the slow query locally and checking for row explosion from eager loading. I’ll propose a small change (e.g., selectinload/load_only) plus a regression test, and open a PR.

[kckoh]

This seems interesting to me. Let me also work on this!

[ObinnaIheanachor]

This seems interesting to me. Let me also work on this!

Thanks for the interest! I’ve already started investigating locally and plan to open a draft PR shortly. Happy to coordinate to avoid duplicated work. Let me know if you’ve already dug into a specific part and we can split efforts.

[kckoh]

Thanks for raising this. After populating 200,000+ DagRuns, I suspect the bottleneck is in the WHERE clause (dag_run.dag_id IN (...)). I plan to add appropriate indexes and use EXPLAIN to confirm they’re applied as expected. Let me know your approach, so that we don't do any duplicate work. @ObinnaIheanachor.

[ObinnaIheanachor]

Thanks, super helpful. I’m focusing on the ORM/query shape side: validate whether eager_load_dag_run_for_validation() is causing unnecessary eager loads / row explosion for the GET /dags/{dag_id}/dagRuns list endpoint, and add a regression test to lock in the optimised loading strategy (likely minimal options for list vs full eager loading for detail).

Your EXPLAIN/index work sounds complementary. If you can share the slow query + plan (before/after), we can decide whether indexing is still needed after the eager-loading change. Happy to coordinate so we land the most robust fix.

[ObinnaIheanachor]

I reproduced the slowdown with 200k DagRuns on a single DAG (/api/v2/dags/heavy_dagruns_perf/dagRuns?limit=100) and DB can fetch 100 DagRuns quickly. However pg_stat_activity shows long-running queries hitting task_instance and callback, suggesting the list endpoint triggers heavy relationship loading (likely via eager_load_dag_run_for_validation() / serialization).
I’m going to focus on removing row explosion / unnecessary eager loads + adding a regression test that asserts the endpoint doesn’t query TI/callback. If you focus on indexes/EXPLAIN for the dag_id IN (...) case (especially dag_id="~"), we’ll cover both angles without overlap.

[kckoh]

I also reproduced the 200k DagRuns on a single dag but i didn't get the slow query as pierrejeambrun got on the screen recording. The response was in a range between 300ms -1s. However, i looked into the query and it looks like :
SELECT dr.*, d.*, drn.* FROM dag_run dr LEFT JOIN dag d ON dr.dag_id = d.dag_id LEFT JOIN dag_run_note drn ON dr.id = drn.dag_run_id WHERE dr.dag_id IN (<list_of_example_dag_ids>) ORDER BY dr.run_after DESC, dr.id DESC LIMIT 50 OFFSET 0;

The DagRun data model has several indexes
Index("dag_id_state", dag_id, _state), Index("idx_dag_run_dag_id", dag_id), Index("idx_dag_run_run_after", run_after), Index( "idx_dag_run_running_dags", "state", "dag_id", postgresql_where=text("state='running'"), sqlite_where=text("state='running'"), ), # since mysql lacks filtered/partial indices, this creates a # duplicate index on mysql. Not the end of the world Index( "idx_dag_run_queued_dags", "state", "dag_id", postgresql_where=text("state='queued'"), sqlite_where=text("state='queued'"), ),
In the frontend run_after is being passed as the query param for oder_by and the id gets appended on the backend.

I propose that we could remove Index("idx_dag_run_run_after", run_after) and add Index("idx_dag_run_run_after_id", run_after, id) to cover the order by for run_after and the id.
Or, we could just add Index("idx_dag_run_run_after", run_after) and leave the existing index.

What do you propose @pierrejeambrun ?

[ObinnaIheanachor]

@kckoh I think it’s a separate concern from the issue in #62025 though. The slowdown there comes from N+1 queries during serialization (DagRun.dag_versions property), not the query plan itself.
The PR I just opened fixes that by:

• avoiding touching DagRun.dag_versions
• using a bounded eager loader for list endpoints

So I’d prefer not to modify or drop indexes in this PR.
If @pierrejeambrun still sees a slow query after this change, we can follow up with EXPLAIN and then consider a composite index (probably dag_id + run_after + id) without removing the existing one.