github-actions[bot] opened a new pull request, #74966:
URL: https://github.com/apache/airflow/pull/74966
* Fix /ui/dags recent-runs query degradation on large, skewed dag_run tables
The per-Dag UNION ALL branches introduced in #67721 plan as backward index
scans of the global idx_dag_run_run_after with a Filter on dag_id. On a
large, skewed dag_run table this is catastrophic for "cold" Dags — those
whose last run is months old or that never ran: the scan must walk
essentially every row of every *other* Dag before reaching that branch's
rows (measured on a 3.8M-row metadata DB: 3,800,261 rows removed by filter,
~2.3s for a single cold branch, 30-60s per page — gateway 504s), because
at default statistics the planner misestimates cold dag_ids (they fall
outside the MCV list) and bets the backward scan hits LIMIT matches
quickly. Hot pages are affected too, bistably: the identical statement
was observed flipping 22.8ms <-> 141s across a single ANALYZE.
Replace the 50-branch union with one page-scoped window query:
row_number() OVER (PARTITION BY dag_id ORDER BY run_after DESC, id DESC)
over WHERE dag_id IN (page's dag ids), filtered to rn <= dag_runs_limit.
This restores the pre-#67721 access path (per-Dag probes of
idx_dag_run_dag_id + one sort + WindowAgg), bounded regardless of where
each Dag's runs sit in time or what the planner estimates, while keeping
#67721's exact-N-per-Dag semantics. On run_after ties the window's
(id DESC) tiebreak deterministically keeps the latest-inserted row,
where the old per-branch LIMIT picked arbitrarily; the outer ORDER BY
leaves tied rows in unspecified order, same as the union. All columns
are selected inline (no join-back). dag_runs_limit is now NonNegativeInt,
matching limit/offset.
Measured end-to-end on a dockerized copy of a 3.8M-row production
metadata DB (PostgreSQL 15.14, default statistics, 74%-cold first page):
GET /ui/dags?limit=50&dag_runs_limit=14: 31.2s -> 0.13-0.64s
same rows and order returned (291/291 id-set match, total_entries
unchanged); selection can differ from the union only on run_after ties,
where the new pick is the newer run
Trade-off: the window sorts all runs of the page's Dags, so a page of the
50 highest-run-count Dags (~619K rows) costs ~2.0s with a 58MB external
sort (vs the union's 0.25-12s bistable plan) — linear and deterministic
instead of the 30-60s+ cold-page catastrophe.
The window-function shape is portable (same ANSI syntax as the
pre-#67721 rank() query and the existing row_number usage in
common/db/assets.py), so the sqlite/mysql/mssql matrix is unaffected.
Co-Authored-By: Claude Code <[email protected]>
* Add tests for recent-runs limit and run_after tie-breaking
Per review: one test asserts a Dag with more runs than dag_runs_limit
returns exactly the N newest in descending run_after order; one test
pins the (run_after DESC, id DESC) tie-break — with two runs tied on
run_after, the later-inserted run (higher id) is the deterministic pick,
which is the one spot the window query's output differs from the old
per-branch union.
Co-Authored-By: Claude Code <[email protected]>
* Remove newsfragment per review
Co-Authored-By: Claude Code <[email protected]>
* Trim redundant comments in the recent-runs tests
Co-Authored-By: Claude Code <[email protected]>
* Keep dag_runs_limit as plain int
The NonNegativeInt validation is a contract change unrelated to the
regression this PR fixes — out of scope here.
Co-Authored-By: Claude Code <[email protected]>
* Drop the explanatory comment above the ranked query
Co-Authored-By: Claude Code <[email protected]>
---------
(cherry picked from commit 1f70ead81ef00ac8e0ddb338a862d4f0f226a78b)
Co-authored-by: Ivan Tham <[email protected]>
Co-authored-by: Claude Code <[email protected]>
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
