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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
