You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "aksdevs (via GitHub)" <gi...@apache.org> on 2026/02/19 02:22:48 UTC
[PR] fix(api): Improve historical_metrics_data endpoint performance (#62021) [airflow]
aksdevs opened a new pull request, #62152:
URL: https://github.com/apache/airflow/pull/62152
Two critical optimizations to reduce database queries and enable query optimizer:
1. Combine DagRun queries from 2 to 1
- Before: 2 separate queries (one for run_type, one for state) with identical WHERE clauses
- After: 1 query grouped by (run_type, state), pivoted in Python to separate counts
- Impact: Eliminates 1 DB round trip from 4 to 3 total queries
2. Add explicit TaskInstance.dag_id filter before JOIN
- Before: TI query only filtered dag_id indirectly via JOIN with DagRun
- After: Added explicit TaskInstance.dag_id.in_(permitted_dag_ids) filter before JOIN
- Impact: Enables database optimizer to use ti_dag_run index (dag_id, run_id) before the join
- Critical: With millions of TaskInstances, this dramatically reduces query execution time
These optimizations specifically address the slow retrieval of historical metrics on large installations where TaskInstance counts are in the millions.
Updated test assertion from 4 to 3 queries to reflect the optimization.
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(api): Improve historical_metrics_data endpoint performance (#62021) [airflow]
Posted by "choo121600 (via GitHub)" <gi...@apache.org>.
choo121600 commented on PR #62152:
URL: https://github.com/apache/airflow/pull/62152#issuecomment-4052238861
@aksdevs This PR has been converted to **draft** because it does not yet meet our [Pull Request quality criteria](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#pull-request-quality-criteria).
**Issues found:**
- :x: **mypy (type checking)**: Failing: CI image checks / MyPy checks (mypy-airflow-core), CI image checks / MyPy checks (mypy-providers). Run `prek --stage manual mypy-airflow-core --all-files` && `prek --stage manual mypy-providers --all-files` locally to reproduce. You need `breeze ci-image build --python 3.10` for Docker-based mypy. See [mypy (type checking) docs](https://github.com/apache/airflow/blob/main/contributing-docs/08_static_code_checks.rst#mypy-checks).
- :x: **Build docs**: Failing: CI image checks / Build documentation (--docs-only). Run `breeze build-docs` locally to reproduce. See [Build docs docs](https://github.com/apache/airflow/blob/main/contributing-docs/16_documentation.rst).
- :x: **Provider tests**: Failing: provider distributions tests / Providers wheel tests, provider distributions tests / Providers sdist tests. Run provider tests with `breeze run pytest <provider-test-path> -xvs`. See [Provider tests docs](https://github.com/apache/airflow/blob/main/contributing-docs/12_provider_distributions.rst).
- :x: **Other failing CI checks**: Failing: Airflow CTL tests / airflow-ctl:P3.10 tests, Generate constraints / Generate constraints for 3.10 on linux/amd64, CI image checks / Build documentation (--spellcheck-only), CI image checks / Test Python API client. Run `prek run --from-ref main` locally to reproduce. See [static checks docs](https://github.com/apache/airflow/blob/main/contributing-docs/08_static_code_checks.rst).
> **Note:** Your branch is **663 commits behind `main`**. Some check failures may be caused by changes in the base branch rather than by your PR. Please rebase your branch and push again to get up-to-date CI results.
**What to do next:**
- The comment informs you what you need to do.
- Fix each issue, then mark the PR as "Ready for review" in the GitHub UI - but only after making sure that all the issues are fixed.
- Maintainers will then proceed with a normal review.
Converting a PR to draft is **not** a rejection — it is an invitation to bring the PR up to the project's standards so that maintainer review time is spent productively. If you have questions, feel free to ask on the [Airflow Slack](https://s.apache.org/airflow-slack).
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(api): Improve historical_metrics_data endpoint performance (#62021) [airflow]
Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun commented on PR #62152:
URL: https://github.com/apache/airflow/pull/62152#issuecomment-4006373779
@aksdevs I don't think those two PRs are mutually exclusive. They performance improvement can be combined from both of them.
The thing is for now I don't see any improvement performence wise with any of those PRs. So further benchmarking or guidance on how to observe the improvement on my side would be welcome.
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(api): Improve historical_metrics_data endpoint performance (#62021) [airflow]
Posted by "aksdevs (via GitHub)" <gi...@apache.org>.
aksdevs commented on code in PR #62152:
URL: https://github.com/apache/airflow/pull/62152#discussion_r2831418650
##########
airflow-core/src/airflow/api_fastapi/core_api/routes/ui/dashboard.py:
##########
@@ -58,48 +58,44 @@ def historical_metrics(
"""Return cluster activity historical metrics."""
current_time = timezone.utcnow()
permitted_dag_ids = cast("set[str]", readable_dags_filter.value)
- # DagRuns
- dag_run_types = session.execute(
- select(DagRun.run_type, func.count(DagRun.run_id))
- .where(
- func.coalesce(DagRun.start_date, current_time) >= start_date,
- func.coalesce(DagRun.end_date, current_time) <= func.coalesce(end_date, current_time),
- )
- .where(DagRun.dag_id.in_(permitted_dag_ids))
- .group_by(DagRun.run_type)
- ).all()
- dag_run_states = session.execute(
- select(DagRun.state, func.count(DagRun.run_id))
- .where(
- func.coalesce(DagRun.start_date, current_time) >= start_date,
- func.coalesce(DagRun.end_date, current_time) <= func.coalesce(end_date, current_time),
- )
+ dag_run_date_filter = (
+ func.coalesce(DagRun.start_date, current_time) >= start_date,
+ func.coalesce(DagRun.end_date, current_time) <= func.coalesce(end_date, current_time),
+ )
Review Comment:
@viiccwen I did changes locally which are similar to those of @iharsh02. Benchmark results below.
Seeding 10,000 dag_runs and 80,000 task_instances ... done in 4.3s
==========================================================================================
SCENARIO 1: Wide date range (96% selectivity)
Query window: 2024-01-01 to 2024-03-01, 200 DAGs, 30 iterations
==========================================================================================
OLD (3q, COALESCE) : avg=79.5ms min=46.9ms p50=75.7ms max=119.1ms
V1 (2q, SARGable) : avg=69.5ms min=44.9ms p50=67.8ms max=100.4ms
V2 (1q, SA CTE+UNION ALL) : avg=118.0ms min=68.5ms p50=110.9ms max=172.7ms
V3 (1q, raw SQL mat. CTE) : avg=97.7ms min=75.4ms p50=96.4ms max=162.9ms
V4 (2q, COALESCE combined) : avg=74.8ms min=45.3ms p50=74.2ms max=129.4ms
V5 (1q, raw COALESCE+CTE) : avg=115.1ms min=73.9ms p50=116.2ms max=161.4ms
V6 (1q, SA mat. CTE+UNION) : avg=114.0ms min=69.2ms p50=113.3ms max=169.3ms
OLD (3q, COALESCE) 0.0% vs OLD (79.5ms)
V1 (2q, SARGable) 12.5% vs OLD (69.5ms) <-- best
V2 (1q, SA CTE+UNION ALL) +-48.5% vs OLD (118.0ms)
V3 (1q, raw SQL mat. CTE) +-23.0% vs OLD (97.7ms)
V4 (2q, COALESCE combined) 5.9% vs OLD (74.8ms)
V5 (1q, raw COALESCE+CTE) +-44.8% vs OLD (115.1ms)
V6 (1q, SA mat. CTE+UNION) +-43.4% vs OLD (114.0ms)
==========================================================================================
SCENARIO 2: Narrow date range (~10% selectivity, 'last 7 days')
Query window: 2024-01-01 to 2024-01-08, 200 DAGs, 30 iterations
==========================================================================================
OLD (3q, COALESCE) : avg=72.9ms min=46.7ms p50=69.5ms max=107.2ms
V1 (2q, SARGable) : avg=77.7ms min=44.3ms p50=76.6ms max=114.1ms
V2 (1q, SA CTE+UNION ALL) : avg=92.4ms min=62.6ms p50=85.1ms max=152.6ms
V3 (1q, raw SQL mat. CTE) : avg=106.3ms min=73.8ms p50=100.2ms max=189.8ms
V4 (2q, COALESCE combined) : avg=65.1ms min=43.8ms p50=64.0ms max=106.5ms
V5 (1q, raw COALESCE+CTE) : avg=101.1ms min=54.7ms p50=100.3ms max=160.7ms
V6 (1q, SA mat. CTE+UNION) : avg=105.5ms min=80.2ms p50=100.8ms max=141.2ms
OLD (3q, COALESCE) 0.0% vs OLD (72.9ms)
V1 (2q, SARGable) + -6.5% vs OLD (77.7ms)
V2 (1q, SA CTE+UNION ALL) +-26.7% vs OLD (92.4ms)
V3 (1q, raw SQL mat. CTE) +-45.8% vs OLD (106.3ms)
V4 (2q, COALESCE combined) 10.7% vs OLD (65.1ms) <-- best
V5 (1q, raw COALESCE+CTE) +-38.6% vs OLD (101.1ms)
V6 (1q, SA mat. CTE+UNION) +-44.6% vs OLD (105.5ms)
==========================================================================================
SCENARIO 3: Narrow date + 20 DAGs (restrictive permissions)
Query window: 2024-01-01 to 2024-01-08, 20 DAGs, 30 iterations
==========================================================================================
OLD (3q, COALESCE) : avg=10.8ms min=7.0ms p50=10.6ms max=13.9ms
V1 (2q, SARGable) : avg=14.5ms min=7.4ms p50=15.6ms max=21.1ms
V2 (1q, SA CTE+UNION ALL) : avg=11.7ms min=6.3ms p50=10.1ms max=26.9ms
V3 (1q, raw SQL mat. CTE) : avg=9.2ms min=6.1ms p50=8.6ms max=23.2ms
V4 (2q, COALESCE combined) : avg=15.0ms min=7.9ms p50=15.0ms max=18.7ms
V5 (1q, raw COALESCE+CTE) : avg=12.6ms min=8.5ms p50=12.6ms max=18.3ms
V6 (1q, SA mat. CTE+UNION) : avg=15.7ms min=8.5ms p50=15.6ms max=24.8ms
OLD (3q, COALESCE) 0.0% vs OLD (10.8ms)
V1 (2q, SARGable) +-34.5% vs OLD (14.5ms)
V2 (1q, SA CTE+UNION ALL) + -8.2% vs OLD (11.7ms)
V3 (1q, raw SQL mat. CTE) 14.5% vs OLD (9.2ms) <-- best
V4 (2q, COALESCE combined) +-39.0% vs OLD (15.0ms)
V5 (1q, raw COALESCE+CTE) +-16.2% vs OLD (12.6ms)
V6 (1q, SA mat. CTE+UNION) +-45.4% vs OLD (15.7ms)
==========================================================================================
ADDING COVERING INDEXES
==========================================================================================
Done.
==========================================================================================
SCENARIO 2 + COVERING INDEXES: Narrow date (~10% selectivity)
Query window: 2024-01-01 to 2024-01-08, 200 DAGs, 30 iterations
==========================================================================================
OLD (3q, COALESCE) : avg=77.3ms min=44.3ms p50=77.5ms max=108.6ms
V1 (2q, SARGable) : avg=84.0ms min=45.8ms p50=92.0ms max=123.9ms
V2 (1q, SA CTE+UNION ALL) : avg=100.8ms min=73.5ms p50=94.9ms max=152.6ms
V3 (1q, raw SQL mat. CTE) : avg=114.4ms min=65.4ms p50=113.8ms max=178.2ms
V4 (2q, COALESCE combined) : avg=74.3ms min=45.0ms p50=78.2ms max=108.5ms
V5 (1q, raw COALESCE+CTE) : avg=107.9ms min=66.2ms p50=111.9ms max=149.5ms
V6 (1q, SA mat. CTE+UNION) : avg=106.9ms min=69.0ms p50=108.6ms max=163.5ms
OLD (3q, COALESCE) 0.0% vs OLD (77.3ms)
V1 (2q, SARGable) + -8.7% vs OLD (84.0ms)
V2 (1q, SA CTE+UNION ALL) +-30.4% vs OLD (100.8ms)
V3 (1q, raw SQL mat. CTE) +-48.0% vs OLD (114.4ms)
V4 (2q, COALESCE combined) 3.9% vs OLD (74.3ms) <-- best
V5 (1q, raw COALESCE+CTE) +-39.6% vs OLD (107.9ms)
V6 (1q, SA mat. CTE+UNION) +-38.3% vs OLD (106.9ms)
==========================================================================================
SCENARIO 3 + COVERING INDEXES: Narrow date + 20 DAGs
Query window: 2024-01-01 to 2024-01-08, 20 DAGs, 30 iterations
==========================================================================================
OLD (3q, COALESCE) : avg=15.5ms min=8.1ms p50=15.2ms max=29.4ms
V1 (2q, SARGable) : avg=12.2ms min=9.3ms p50=11.8ms max=16.1ms
V2 (1q, SA CTE+UNION ALL) : avg=12.5ms min=7.3ms p50=11.9ms max=22.3ms
V3 (1q, raw SQL mat. CTE) : avg=9.1ms min=6.1ms p50=9.1ms max=14.7ms
V4 (2q, COALESCE combined) : avg=12.0ms min=7.1ms p50=11.1ms max=20.6ms
V5 (1q, raw COALESCE+CTE) : avg=9.0ms min=5.0ms p50=8.9ms max=19.0ms
V6 (1q, SA mat. CTE+UNION) : avg=10.1ms min=7.5ms p50=9.8ms max=17.8ms
OLD (3q, COALESCE) 0.0% vs OLD (15.5ms)
V1 (2q, SARGable) 21.2% vs OLD (12.2ms)
V2 (1q, SA CTE+UNION ALL) 19.1% vs OLD (12.5ms)
V3 (1q, raw SQL mat. CTE) 41.2% vs OLD (9.1ms)
V4 (2q, COALESCE combined) 22.4% vs OLD (12.0ms)
V5 (1q, raw COALESCE+CTE) 42.0% vs OLD (9.0ms) <-- best
V6 (1q, SA mat. CTE+UNION) 34.7% vs OLD (10.1ms)
Closes #62021
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(api): Improve historical_metrics_data endpoint performance (#62021) [airflow]
Posted by "viiccwen (via GitHub)" <gi...@apache.org>.
viiccwen commented on code in PR #62152:
URL: https://github.com/apache/airflow/pull/62152#discussion_r2826890476
##########
airflow-core/src/airflow/api_fastapi/core_api/routes/ui/dashboard.py:
##########
@@ -58,48 +58,44 @@ def historical_metrics(
"""Return cluster activity historical metrics."""
current_time = timezone.utcnow()
permitted_dag_ids = cast("set[str]", readable_dags_filter.value)
- # DagRuns
- dag_run_types = session.execute(
- select(DagRun.run_type, func.count(DagRun.run_id))
- .where(
- func.coalesce(DagRun.start_date, current_time) >= start_date,
- func.coalesce(DagRun.end_date, current_time) <= func.coalesce(end_date, current_time),
- )
- .where(DagRun.dag_id.in_(permitted_dag_ids))
- .group_by(DagRun.run_type)
- ).all()
- dag_run_states = session.execute(
- select(DagRun.state, func.count(DagRun.run_id))
- .where(
- func.coalesce(DagRun.start_date, current_time) >= start_date,
- func.coalesce(DagRun.end_date, current_time) <= func.coalesce(end_date, current_time),
- )
+ dag_run_date_filter = (
+ func.coalesce(DagRun.start_date, current_time) >= start_date,
+ func.coalesce(DagRun.end_date, current_time) <= func.coalesce(end_date, current_time),
+ )
Review Comment:
IMO, it's a bottleneck. It makes the query non-SARGable and may cause a full table scan.
maybe we should replace `coalesce` with boolean logic (`_or` and `IS NULL` checks)
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(api): Improve historical_metrics_data endpoint performance (#62021) [airflow]
Posted by "aksdevs (via GitHub)" <gi...@apache.org>.
aksdevs commented on PR #62152:
URL: https://github.com/apache/airflow/pull/62152#issuecomment-3953353258
@pierrejeambrun From the analysis and the limited testing I’ve done so far, the sargable rewrite already shows measurable efficiency improvement.
Before I proceed further, I wanted to check: would you like me to continue investing effort in this direction (for example, deeper benchmarking, checking other possible contributors to the slowness, edge cases, etc.), or would you prefer to close this in favor of #62158?
We could rewrite the date filters to avoid wrapping columns in functions. For example, replace: func.coalesce(DagRun.start_date, current_time) >= start_date with: or_(DagRun.start_date >= start_date, DagRun.start_date.is_(None))
This lets the DB use indexes on start_date/end_date.
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(api): Improve historical_metrics_data endpoint performance (#62021) [airflow]
Posted by "aksdevs (via GitHub)" <gi...@apache.org>.
aksdevs commented on code in PR #62152:
URL: https://github.com/apache/airflow/pull/62152#discussion_r2831207325
##########
airflow-core/src/airflow/api_fastapi/core_api/routes/ui/dashboard.py:
##########
@@ -58,48 +58,44 @@ def historical_metrics(
"""Return cluster activity historical metrics."""
current_time = timezone.utcnow()
permitted_dag_ids = cast("set[str]", readable_dags_filter.value)
- # DagRuns
- dag_run_types = session.execute(
- select(DagRun.run_type, func.count(DagRun.run_id))
- .where(
- func.coalesce(DagRun.start_date, current_time) >= start_date,
- func.coalesce(DagRun.end_date, current_time) <= func.coalesce(end_date, current_time),
- )
- .where(DagRun.dag_id.in_(permitted_dag_ids))
- .group_by(DagRun.run_type)
- ).all()
- dag_run_states = session.execute(
- select(DagRun.state, func.count(DagRun.run_id))
- .where(
- func.coalesce(DagRun.start_date, current_time) >= start_date,
- func.coalesce(DagRun.end_date, current_time) <= func.coalesce(end_date, current_time),
- )
+ dag_run_date_filter = (
+ func.coalesce(DagRun.start_date, current_time) >= start_date,
+ func.coalesce(DagRun.end_date, current_time) <= func.coalesce(end_date, current_time),
+ )
Review Comment:
@viiccwen thanks, makes sense. I see @iharsh02 has already created merge request for the same. Apologies I missed the tag for the issue in the comment.
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(api): Improve historical_metrics_data endpoint performance (#62021) [airflow]
Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun closed pull request #62152: fix(api): Improve historical_metrics_data endpoint performance (#62021)
URL: https://github.com/apache/airflow/pull/62152
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org