yuseok89 opened a new pull request, #77179:
URL: https://github.com/apache/airflow/pull/77179
<!-- SPDX-License-Identifier: Apache-2.0
https://www.apache.org/licenses/LICENSE-2.0 -->
<!--
Thank you for contributing!
Please provide above a brief description of the changes made in this pull
request.
Write a good git commit message following this guide:
https://chris.beams.io/posts/git-commit/
Please make sure that your code changes are covered with tests.
And in case of new features or big changes remember to adjust the
documentation.
For user-facing UI changes, please attach before/after screenshots (or a
short
screen recording) so reviewers can assess the visual impact.
Feel free to ping (in general) for the review if you do not see reaction for
a few days
(72 Hours is the minimum reaction time you can expect from volunteers) - we
sometimes miss notifications.
In case of an existing issue, reference it using one of the following:
* closes: #ISSUE
* related: #ISSUE
-->
#74481 added the `(dag_id, run_after)` index on `dag_run`. Lookups that
fetch a Dag's newest runs with `WHERE dag_id = ... ORDER BY run_after DESC
LIMIT n` can still skip it. Postgres can walk `idx_dag_run_run_after` instead
and filter on `dag_id`. When the Dag's newest runs are older than other Dags'
runs, that reads most of `dag_run`.
These lookups now filter with `dag_id BETWEEN x AND x` and order by `dag_id`
before `run_after`. Only `idx_dag_run_dag_id_run_after` returns rows in that
order, so Postgres used it in every case measured below. The rows returned are
unchanged.
The fixed queries are the rendered fields cleanup after each task run, the
consecutive failure auto-pause check, loop history and the run duration stats.
The list endpoints that sort one Dag's runs by `run_after` get the same
filter and a leading `dag_id` sort when `run_after` is the first sort key. That
covers the Grid, the Dag Runs page, the run switcher and the task instance list
on the Task page. Other sort keys keep the plain `dag_id = x` filter.
While measuring them, this form also turned out to cover a case the latest
run lookup from #75873 does not. With few Dags, that lookup can still read the
whole table for a Dag with no runs. It uses the same form now.
Measured on Postgres 16 with 5M runs written in `run_after` order, plus one
task instance per run for the task instance list. "Few Dags" sets `n_distinct`
of `dag_id` to 20.
| Lookup | Dag | Before | After |
|---|---|---|---|
| Rendered fields cleanup | 250k runs, none recent | 1.3 s | 0.1 ms |
| Consecutive failure check | 250k runs, none recent | 1.2 s | 0.02 ms |
| Loop history | 250k runs, none recent | 1.3 s | 0.02 ms |
| Run duration stats | 250k runs, none recent | 1.5 s | 0.07 ms |
| Grid runs | 250k runs, none recent | 1.3 s | 0.08 ms |
| Grid structure | 250k runs, none recent | 1.3 s | 0.02 ms |
| Dag runs list | 250k runs, none recent | 1.3 s | 0.6 ms |
| Task instances of one task | 250k runs, none recent | 0.86 s | 41 ms |
| Task instances of one task | 3 runs | 0.81 s | 0.06 ms |
| Latest run, few Dags | no runs | 2.3 s | 0.01 ms |
On SQLite and MySQL the plain lookups already used the `(dag_id, run_after)`
index for both forms. The list endpoints were not measured there.
New tests cover the leading `dag_id` sort and when it applies. The existing
tests for these lookups pass on SQLite and MySQL.
Still checking before marking this ready
- The same tests on Postgres
- The manual static checks
- The XCom list sorted by `run_after` has the same plan, but its request
takes 10 to 15 s with any sort because of the XCom read subquery and the count,
so it is left out here and needs a separate look
related: #74481
---
##### Was generative AI tooling used to co-author this PR?
<!--
If generative AI tooling has been used in the process of authoring this PR,
please
change below checkbox to `[X]` followed by the name of the tool, uncomment
the "Generated-by".
-->
- [x] Yes (please specify the tool below)
- Claude Code (Opus 5.5)
<!--
Generated-by: [Tool Name] following [the
guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions)
-->
---
* Read the **[Pull Request
Guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#pull-request-guidelines)**
for more information. Note: commit author/co-author name and email in commits
become permanently public when merged.
* For fundamental code changes, an Airflow Improvement Proposal
([AIP](https://cwiki.apache.org/confluence/display/AIRFLOW/Airflow+Improvement+Proposals))
is needed.
* When adding dependency, check compliance with the [ASF 3rd Party License
Policy](https://www.apache.org/legal/resolved.html#category-x).
* For significant user-facing changes create newsfragment:
`{pr_number}.significant.rst`, in
[airflow-core/newsfragments](https://github.com/apache/airflow/tree/main/airflow-core/newsfragments).
You can add this file in a follow-up commit after the PR is created so you
know the PR number.
--
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]