You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "pierrejeambrun (via GitHub)" <gi...@apache.org> on 2026/02/16 15:22:05 UTC

[I] API - Improve public get_dag_runs endpoint [airflow]

pierrejeambrun opened a new issue, #62025:
URL: https://github.com/apache/airflow/issues/62025

   ### Body
   
   On big installation the "get_dag_runs" 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 row explosion. We need to optimize the query, most likely by double checking the eager loading options and verify that there is no row explosion cause by a wrong option. Also we can add `load_only` to limit the number of columns joined.
   
   
   https://github.com/apache/airflow/blob/4e75919198be6b8e4d3ac52656a9b74e0e73b097/airflow-core/src/airflow/api_fastapi/core_api/routes/public/dag_run.py#L387-L421
   
   Also needs investigation to check if an extra index could help.
   
   ### Committer
   
   - [x] I acknowledge that I am a maintainer/committer of the Apache Airflow project.


-- 
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: commits-unsubscribe@airflow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "ObinnaIheanachor (via GitHub)" <gi...@apache.org>.
ObinnaIheanachor commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3925861993

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


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "vatsrahul1001 (via GitHub)" <gi...@apache.org>.
vatsrahul1001 closed issue #62025: API - Improve public get_dag_runs endpoint
URL: https://github.com/apache/airflow/issues/62025


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "ObinnaIheanachor (via GitHub)" <gi...@apache.org>.
ObinnaIheanachor commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3919484522

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


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "kckoh (via GitHub)" <gi...@apache.org>.
kckoh commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-4009612327

   @pierrejeambrun i made a PR. Can you take a look and test this? Thank you!


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "ObinnaIheanachor (via GitHub)" <gi...@apache.org>.
ObinnaIheanachor commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3920910190

   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.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "kckoh (via GitHub)" <gi...@apache.org>.
kckoh commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3925235289

   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 ?


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun closed issue #62025: API - Improve public get_dag_runs endpoint
URL: https://github.com/apache/airflow/issues/62025


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3946346491

   Answered directly on the linked PR.
   
   Removing an index is most probably not possible, if it's just the 'sort' in the UI that could leverage an additional index, then I think it's covered by the documentation on custom indexes in `/Users/pierrejeambrun/Documents/astronomer/git-repositories/airflow/airflow-core/docs/howto/performance.rst`.
   
   If you change the query, to sort on `id` only and not `run_after` do you see an improved response time? We can simply change the UI to default sorting to 'id' so it's faster. And people can chose other sort criteria via the UI, if it's slow they need to add custom indexes on the column they usually sort on.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "harishtheking (via GitHub)" <gi...@apache.org>.
harishtheking commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-4060255080

   Hi, I would like to work on this issue.
   Could it be assigned to me?


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "kckoh (via GitHub)" <gi...@apache.org>.
kckoh commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3949577985

   > If you change the query, to sort on id only and not run_after do you see an improved response time? We can simply change the UI to default sorting to 'id' so it's faster. 
   
   @pierrejeambrun thank you for the response. 
   Let me change the query with the id to see if there is any improved response time and i will let you know the results here. 
   


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-4063729264

   Per our policy we do not assign issues any more (see contributing docs) - but feel free to work on it without approval or assignment. Good, ready to review PR is where review will start


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "ObinnaIheanachor (via GitHub)" <gi...@apache.org>.
ObinnaIheanachor commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3909117492

   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.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "ObinnaIheanachor (via GitHub)" <gi...@apache.org>.
ObinnaIheanachor commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3922587448

   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.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "kckoh (via GitHub)" <gi...@apache.org>.
kckoh commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3918911740

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


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "kckoh (via GitHub)" <gi...@apache.org>.
kckoh commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3920765277

   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.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "kckoh (via GitHub)" <gi...@apache.org>.
kckoh commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-3976769665

   @pierrejeambrun  
   I tested changing the default sort to  `id` and confirmed the improvement.
                                                            
     The key difference is that `USE TEMP B-TREE FOR ORDER BY` is eliminated when sorting by `id` (rowid),
     since SQLite can satisfy the ORDER BY directly via the
     primary key without an additional in-memory sort.
   
     **Before** (default sort: `run_after`):
     |--SEARCH dag_run USING INDEX dag_id_state (dag_id=?)
     |--SEARCH dag_1 USING INDEX sqlite_autoindex_dag_1
     (dag_id=?) LEFT-JOIN
     |--SEARCH dag_run_note_1 USING INTEGER PRIMARY KEY
     (rowid=?) LEFT-JOIN
     `--USE TEMP B-TREE FOR ORDER BY
   
     **After** (default sort: `id`):
     |--SEARCH dag_run USING INDEX idx_dag_run_dag_id
     (dag_id=?)
     |--SEARCH dag_1 USING INDEX sqlite_autoindex_dag_1
     (dag_id=?) LEFT-JOIN
     `--SEARCH dag_run_note_1 USING INTEGER PRIMARY KEY
     (rowid=?) LEFT-JOIN
   
   The temporary B-tree sort is gone. Shall I make a separate PR for this UI default sort change? 


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] API - Improve public get_dag_runs endpoint [airflow]

Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun commented on issue #62025:
URL: https://github.com/apache/airflow/issues/62025#issuecomment-4006324870

   @kckoh yes please, feel free to open a PR so we can improve by increments, and I can test this in isolation :)


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org