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]

  • [PR] Dag run newest first ind... yuseok89 (via GitHub) via commits

Reply via email to