chigili opened a new pull request, #63166:
URL: https://github.com/apache/airflow/pull/63166
## Summary
Fixes the slow performance of the `historical_metrics_data` endpoint
on large Airflow installations by adding missing database indexes and
optimizing queries.
## Problem
The endpoint was performing full table scans on the `DagRun` table
because:
- No indexes existed on `start_date` and `end_date` columns
- Queries used `func.coalesce()` which prevented index usage even if
indexes existed
- Two separate queries fetched data that could be combined
On installations with millions of DagRuns, this caused significant
performance degradation.
## Solution
This PR implements three optimizations:
### 1. Database Migration
- Added indexes on `DagRun.start_date` and `DagRun.end_date`
- Migration: `0108_3_2_0_add_indexes_to_dagrun_date_columns.py`
### 2. Sargable Queries
- Replaced `func.coalesce(DagRun.start_date, current_time)` with
explicit `OR/IS NULL` logic
- Allows the database to use the new indexes effectively
- Maintains semantic correctness for NULL handling
### 3. Query Consolidation
- Combined two separate DagRun queries (run_type and state) into one
- Reduced total queries from **4 to 3**
## Performance Results
**Benchmark on 10,000 DagRuns:**
- Old approach: 0.0060s average
- New approach: 0.0024s average
- **Improvement: ~60% faster**
The benchmark script is included in
`dev/benchmark_historical_metrics.py` for verification.
## Testing
- ✅ All 12 existing dashboard tests pass
- ✅ Query count assertion updated from 4 to 3
- ✅ Static checks passed (ruff, bandit, pre-commit hooks)
- ✅ Benchmark demonstrates measurable improvement
This PR addresses all concerns by:
1. ✅ Adding the missing indexes via migration (the root cause fix)
2. ✅ Providing a runnable benchmark script with measurable results
3. ✅ Combining query optimizations for additional performance gains
closes: #62021
Please provide above a brief description of the changes made in this pull
request.
Write a good git commit message following this guide:
http://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.
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
-->
---
##### 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".
-->
- [ ] Yes (please specify the tool below)
<!--
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]
