You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "pickfire (via GitHub)" <gi...@apache.org> on 2026/10/07 09:00:13 UTC

[PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

pickfire opened a new pull request, #74401:
URL: https://github.com/apache/airflow/pull/74401

   ## Summary
   
   `GET /ui/dags` takes minutes and times out at gateways on large, skewed `dag_run`
   tables, regressed by #67721. This PR replaces the per-Dag `UNION ALL` branches with
   one page-scoped `row_number()` window query — bounded regardless of data skew, with
   identical output.
   
   ## Problem
   
   Each union branch (`WHERE dag_id = :dag ORDER BY run_after DESC LIMIT 14`) plans as a
   backward scan of the global `idx_dag_run_run_after` with a Filter on `dag_id`. For a
   Dag whose last run is old, the scan walks the whole table before reaching its rows:
   
   ```
   Index Scan Backward using idx_dag_run_run_after on dag_run
     Filter: (dag_id = '...')
     Rows Removed by Filter: 3800261          -- 3.8M-row table, 1 row returned
   ```
   
   Why the planner picks this: at default `statistics_target`, cold `dag_id`s fall outside
   the MCV list and get the average-rows estimate, so the backward scan looks cheap under
   `LIMIT`. Small installs don't hit this (every `dag_id` fits the MCV list), which is
   likely why it's unreported. The union's fast plan is also bistable — we observed the
   identical statement flip 22.8ms ↔ 141s across a single `ANALYZE` on a hot page.
   
   Production symptom (DAG list page behind a 15s gateway):
   
   <!-- paste the 504 screenshot here after creating the PR -->
   
   ## Fix
   
   ```sql
   SELECT ... FROM (
       SELECT ..., row_number() OVER (
           PARTITION BY dag_id ORDER BY run_after DESC, id DESC) AS rn
       FROM dag_run
       WHERE dag_id IN (:page_dag_ids)     -- served by idx_dag_run_dag_id
   ) WHERE rn <= :dag_runs_limit
   ```
   
   `row_number()` keeps the union's exact-N-per-Dag semantics (incl. ties); all columns
   inline, no join-back. Portable ANSI window syntax — same as the pre-#67721 `rank()`
   query and existing usage in `common/db/assets.py`.
   
   ## Measurements
   
   `GET /ui/dags?limit=50&dag_runs_limit=14`: **31s → 0.2s** on the affected page,
   response byte-identical to the union.
   
   Repro setup:
   
   - PostgreSQL 15.14, full copy of a production metadata DB (3.8M `dag_run` rows, 1,247 Dags)
   - Default statistics; affected page 74% "cold" Dags (no run in 30d+ or never ran)
   - Also validated on hot and mid-size pages
   
   Trade-off: on an all-hot page (~350K rows to rank) the window query is 0.5–0.9s vs the
   union's 30ms — the pre-#67721 behavior envelope, far under any gateway timeout.
   
   Generated-by: Claude Code following https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions
   
   🤖 Generated with [Claude Code](https://claude.com/claude-code)


-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #74401:
URL: https://github.com/apache/airflow/pull/74401#issuecomment-6247695168

   ### Backport successfully created: v3-4-test
   
   Note: As of [Merging PRs targeted for Airflow 3.X](https://github.com/apache/airflow/blob/main/dev/README_AIRFLOW3_DEV.md#merging-prs-targeted-for-airflow-3x)
   the committer who merges the PR is responsible for backporting the PRs that are bug fixes (generally speaking) to the maintenance branches.
   
   In matter of doubt please ask in [#release-management](https://apache-airflow.slack.com/archives/C03G9H97MM2) Slack channel.
   
   <table>
           <tr>
           <th>Status</th>
           <th>Branch</th>
           <th>Result</th>
       </tr>
       <tr>
           <td>✅</td>
           <td>v3-4-test</td>
           <td><a href="https://github.com/apache/airflow/pull/74966"><img src="https://img.shields.io/badge/PR-74966-blue" alt="PR Link"></a></td>
       </tr>
   </table>


-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "boring-cyborg[bot] (via GitHub)" <gi...@apache.org>.
boring-cyborg[bot] commented on PR #74401:
URL: https://github.com/apache/airflow/pull/74401#issuecomment-6167331252

   Awesome work, congrats on your first merged pull request! You are invited to check our [Issue Tracker](https://github.com/apache/airflow/issues) for additional contributions.
   


-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun merged PR #74401:
URL: https://github.com/apache/airflow/pull/74401


-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "pickfire (via GitHub)" <gi...@apache.org>.
pickfire commented on code in PR #74401:
URL: https://github.com/apache/airflow/pull/74401#discussion_r4213771516


##########
airflow-core/newsfragments/74401.bugfix.rst:
##########
@@ -0,0 +1 @@
+Fix ``GET /ui/dags`` recent-runs query degradation on large, skewed ``dag_run`` tables: the per-Dag ``UNION ALL`` branches planned as unbounded backward ``run_after`` index scans for cold Dags; recent runs are now fetched by one page-scoped ``row_number()`` query.

Review Comment:
   Removed in e2e6239b90. Thanks!



##########
airflow-core/src/airflow/api_fastapi/core_api/routes/ui/dags.py:
##########
@@ -238,16 +242,31 @@ def get_dags(
                 DagRun.run_after,
                 DagRun.start_date,
                 DagRun.state,
+                func.row_number()
+                .over(
+                    partition_by=DagRun.dag_id,
+                    order_by=(DagRun.run_after.desc(), DagRun.id.desc()),

Review Comment:
   Added both in fa42a91e7c: `test_recent_dag_runs_returns_exactly_n_newest_in_order` (5 runs, limit 3 → the 3 newest, descending) and `test_recent_dag_runs_tie_breaks_on_id_desc` (two runs tied on run_after → the later-inserted one wins deterministically). Split into separate commits for easier review.



-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "pickfire (via GitHub)" <gi...@apache.org>.
pickfire commented on PR #74401:
URL: https://github.com/apache/airflow/pull/74401#issuecomment-6052367930

   Thanks @yuseok89! Yes — the per-branch shape plans great when the fixture's Dags are all actively running (they sit at the top of the run_after index). The skew case only shows up once a fleet accumulates months of history with Dags going dormant, which is presumably why it hadn't surfaced. Happy to add a benchmark note or fixture if useful.


-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "yuseok89 (via GitHub)" <gi...@apache.org>.
yuseok89 commented on PR #74401:
URL: https://github.com/apache/airflow/pull/74401#issuecomment-6052304462

   Thanks for the thorough analysis and fix! #67721 was benchmarked on evenly active Dags, and the cold-Dag skew is a good case to keep in mind.


-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "boring-cyborg[bot] (via GitHub)" <gi...@apache.org>.
boring-cyborg[bot] commented on PR #74401:
URL: https://github.com/apache/airflow/pull/74401#issuecomment-6034584658

   Congratulations on your first Pull Request and welcome to the Apache Airflow community! If you have any issues or are unsure about any anything please check our [Contributors' Guide](https://github.com/apache/airflow/blob/main/contributing-docs/README.rst)
   Here are some useful points:
   - Pay attention to the quality of your code (ruff, mypy and type annotations). Our [prek-hooks]( https://github.com/apache/airflow/blob/main/contributing-docs/08_static_code_checks.rst#prerequisites-for-prek-hooks) will help you with that.
   - In case of a new feature add useful documentation (in docstrings or in `docs/` directory). Adding a new operator? Check this short [guide](https://github.com/apache/airflow/blob/main/airflow-core/docs/howto/custom-operator.rst) Consider adding an example Dag that shows how users should use it.
   - Consider using [Breeze environment](https://github.com/apache/airflow/blob/main/dev/breeze/doc/README.rst) for testing locally, it's a heavy docker but it ships with a working Airflow and a lot of integrations.
   - Be patient and persistent. It might take some time to get a review or get the final approval from Committers.
   - Please follow [ASF Code of Conduct](https://www.apache.org/foundation/policies/conduct) for all communication including (but not limited to) comments on Pull Requests, Mailing list and Slack.
   - Be sure to read the [Airflow Coding style]( https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#coding-style-and-best-practices).
   - Always keep your Pull Requests rebased, otherwise your build might fail due to changes not related to your commits.
   Apache Airflow is a community-driven project and together we are making it better 🚀.
   In case of doubts contact the developers at:
   Mailing List: dev@airflow.apache.org
   Slack: https://s.apache.org/airflow-slack
   


-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "pickfire (via GitHub)" <gi...@apache.org>.
pickfire commented on PR #74401:
URL: https://github.com/apache/airflow/pull/74401#issuecomment-6072013655

   The `MySQL tests: core` failure is `test_in_process_execution_api_transport_lifecycle` (execution-API transport thread lifecycle — unrelated to this change), failing with the same `assert not thread.is_alive()` in both CI runs on this PR. Both Postgres core jobs pass the full API suite. Looks like a repeatable runner flake on that job rather than anything in dags.py — flagging so it doesn't look unaddressed. Happy to investigate separately if it reproduces on other PRs.


-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun commented on code in PR #74401:
URL: https://github.com/apache/airflow/pull/74401#discussion_r4208915986


##########
airflow-core/src/airflow/api_fastapi/core_api/routes/ui/dags.py:
##########
@@ -238,16 +242,31 @@ def get_dags(
                 DagRun.run_after,
                 DagRun.start_date,
                 DagRun.state,
+                func.row_number()
+                .over(
+                    partition_by=DagRun.dag_id,
+                    order_by=(DagRun.run_after.desc(), DagRun.id.desc()),

Review Comment:
   Could we add a test where one Dag has more than `dag_runs_limit` runs, asserting exactly the N newest come back in order? And ideally a tie case (two runs with equal `run_after`) to pin the new `id DESC` behavior, since that's the one spot the output differs from the old union.



-- 
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: [PR] Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables [airflow]

Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun commented on code in PR #74401:
URL: https://github.com/apache/airflow/pull/74401#discussion_r4208909942


##########
airflow-core/newsfragments/74401.bugfix.rst:
##########
@@ -0,0 +1 @@
+Fix ``GET /ui/dags`` recent-runs query degradation on large, skewed ``dag_run`` tables: the per-Dag ``UNION ALL`` branches planned as unbounded backward ``run_after`` index scans for cold Dags; recent runs are now fetched by one page-scoped ``row_number()`` query.

Review Comment:
   To remove



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