yuseok89 commented on PR #69619:
URL: https://github.com/apache/airflow/pull/69619#issuecomment-5391012376
Good call, you were right. I seeded 600 Dags, 850k runs, 3M task instances
(Postgres 14) and measured a 50-Dag page.
The bottleneck is the per-Dag `ORDER BY run_after DESC LIMIT 1`. There is no
`(dag_id, run_after)` index, so a Dag whose latest run is old makes the planner
walk `idx_dag_run_run_after` backwards across most of the table. For a page of
such Dags that comes out around 1050 ms.
```
before, one branch of the per-Dag lookup
Index Scan Backward using idx_dag_run_run_after on dag_run dag_run_11
(actual time=152.613..152.614 rows=1 loops=1)
Filter: ((dag_id)::text = 'stale_dag_0011'::text)
Rows Removed by Filter: 800043
Buffers: shared hit=801672
Execution Time: 1049.016 ms
```
The fix drops that lookup. The Dags list already carries each Dag's latest
run, so the endpoint now takes `dag_run_ids` instead of `dag_ids` and counts
them directly, around 0.5 ms for the same page. As a bonus the list and the
counts can no longer resolve different latest runs.
```
after
Index Scan using dag_run_pkey on dag_run (actual time=0.034..0.465 rows=50
loops=1)
Execution Time: 0.541 ms
```
Pushed.
--
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]
