ephraimbuddy commented on code in PR #69518:
URL: https://github.com/apache/airflow/pull/69518#discussion_r4630108937
##########
airflow-core/src/airflow/dag_processing/collection.py:
##########
@@ -168,6 +168,70 @@ def _get_latest_runs_stmt_partitioned(dag_id: str) ->
Select:
return select(DagRun).where(DagRun.id ==
latest_run_id).options(_build_latest_run_load_options())
+def _get_latest_runs_stmt_batch(dag_ids: Collection[str]) -> Select:
+ """
+ Batch equivalent of :func:`_get_latest_runs_stmt` for several Dags in one
query.
+
+ Row selection matches the single-Dag statement per Dag: the run whose
logical_date
+ equals the Dag's max logical_date among automated runs; a Dag whose
automated runs all
+ lack a logical_date matches nothing, like the single-Dag statement.
+ """
+ max_logical_dates = (
+ select(DagRun.dag_id,
func.max(DagRun.logical_date).label("max_logical_date"))
+ .where(
+ DagRun.dag_id.in_(dag_ids),
+ DagRun.run_type.in_((DagRunType.BACKFILL_JOB,
DagRunType.SCHEDULED)),
+ )
+ .group_by(DagRun.dag_id)
+ .subquery()
+ )
+ return (
+ select(DagRun)
+ .join(
+ max_logical_dates,
+ (DagRun.dag_id == max_logical_dates.c.dag_id)
+ & (DagRun.logical_date == max_logical_dates.c.max_logical_date),
+ )
+ .options(_build_latest_run_load_options())
+ )
+
+
+def _get_latest_runs_stmt_partitioned_batch(dag_ids: Collection[str]) ->
Select:
+ """
+ Batch equivalent of :func:`_get_latest_runs_stmt_partitioned` for several
Dags in one query.
+
+ Row selection matches the single-Dag statement per Dag: the top run
ordered by
+ (partition_date IS NULL, partition_date DESC, run_after DESC) among
automated runs
+ with a partition_key.
+ """
+ ranked = (
+ select(
+ DagRun.id,
+ func.row_number()
Review Comment:
With two partitioned Dags and 10,000 runs each, PostgreSQL 14 sorted and
ranked all 20,000 rows, then discarded 19,998. The sort used 2.3 MB, while each
existing `LIMIT 1` lookup used 25 kB. Could we keep the per-Dag `ORDER BY …
LIMIT 1` inside the batched statement? That lets the database keep only the
best candidate for each Dag as it reads the history.
##########
airflow-core/src/airflow/dag_processing/collection.py:
##########
@@ -168,6 +168,70 @@ def _get_latest_runs_stmt_partitioned(dag_id: str) ->
Select:
return select(DagRun).where(DagRun.id ==
latest_run_id).options(_build_latest_run_load_options())
+def _get_latest_runs_stmt_batch(dag_ids: Collection[str]) -> Select:
+ """
+ Batch equivalent of :func:`_get_latest_runs_stmt` for several Dags in one
query.
+
+ Row selection matches the single-Dag statement per Dag: the run whose
logical_date
+ equals the Dag's max logical_date among automated runs; a Dag whose
automated runs all
+ lack a logical_date matches nothing, like the single-Dag statement.
+ """
+ max_logical_dates = (
+ select(DagRun.dag_id,
func.max(DagRun.logical_date).label("max_logical_date"))
+ .where(
+ DagRun.dag_id.in_(dag_ids),
+ DagRun.run_type.in_((DagRunType.BACKFILL_JOB,
DagRunType.SCHEDULED)),
+ )
+ .group_by(DagRun.dag_id)
Review Comment:
With 10,000 scheduled runs for each of two Dags, PostgreSQL 14 read all
20,000 rows to compute this grouped `MAX`. The existing lookups each stopped at
one index entry. Server execution time went from 0.09 ms combined to 5.60 ms.
Could we drive the query from the requested Dag IDs and use a correlated `ORDER
BY logical_date DESC LIMIT 1` for each ID, filtering out null logical dates?
That kept the single round trip and the early index stop in the same fixture.
##########
airflow-core/src/airflow/dag_processing/collection.py:
##########
@@ -168,6 +168,70 @@ def _get_latest_runs_stmt_partitioned(dag_id: str) ->
Select:
return select(DagRun).where(DagRun.id ==
latest_run_id).options(_build_latest_run_load_options())
+def _get_latest_runs_stmt_batch(dag_ids: Collection[str]) -> Select:
+ """
+ Batch equivalent of :func:`_get_latest_runs_stmt` for several Dags in one
query.
+
+ Row selection matches the single-Dag statement per Dag: the run whose
logical_date
+ equals the Dag's max logical_date among automated runs; a Dag whose
automated runs all
+ lack a logical_date matches nothing, like the single-Dag statement.
+ """
+ max_logical_dates = (
+ select(DagRun.dag_id,
func.max(DagRun.logical_date).label("max_logical_date"))
+ .where(
+ DagRun.dag_id.in_(dag_ids),
+ DagRun.run_type.in_((DagRunType.BACKFILL_JOB,
DagRunType.SCHEDULED)),
+ )
+ .group_by(DagRun.dag_id)
+ .subquery()
+ )
+ return (
+ select(DagRun)
+ .join(
+ max_logical_dates,
+ (DagRun.dag_id == max_logical_dates.c.dag_id)
+ & (DagRun.logical_date == max_logical_dates.c.max_logical_date),
+ )
+ .options(_build_latest_run_load_options())
+ )
+
+
+def _get_latest_runs_stmt_partitioned_batch(dag_ids: Collection[str]) ->
Select:
+ """
+ Batch equivalent of :func:`_get_latest_runs_stmt_partitioned` for several
Dags in one query.
+
+ Row selection matches the single-Dag statement per Dag: the top run
ordered by
+ (partition_date IS NULL, partition_date DESC, run_after DESC) among
automated runs
+ with a partition_key.
+ """
+ ranked = (
+ select(
+ DagRun.id,
+ func.row_number()
+ .over(
+ partition_by=DagRun.dag_id,
+ order_by=(
+ DagRun.partition_date.is_(None),
+ DagRun.partition_date.desc(),
+ DagRun.run_after.desc(),
Review Comment:
Two eligible runs can share `partition_date` and `run_after` while carrying
different partition keys. I reproduced a case where the single-Dag query
returned one key and the batched query returned the other. That makes the
timetable’s reference partition depend on which other Dags share the file.
Please add the same unique tie-breaker, such as `DagRun.id.desc()`, to both
orderings and cover this case in the parity test.
##########
airflow-core/tests/unit/dag_processing/test_collection.py:
##########
@@ -163,6 +167,152 @@ def
test_statement_latest_runs_loads_timetable_fields(dag_maker, session):
assert latest.partition_date is None
[email protected]_test
+def test_run_info_calculate_many_matches_calculate_per_dag(dag_maker, session):
+ """Batched run-info resolution returns the same result per Dag as per-dag
resolution."""
+ lazy_dags = {}
+ for i in range(2):
+ with dag_maker(f"cm-plain-{i}", schedule="@daily", session=session) as
dag:
+ pass
+ dag_maker.sync_dagbag_to_db()
+ if i == 0: # one dag with a run, one without
+ dag_maker.create_dagrun(
+ run_id="cm-run",
+ logical_date=tz.datetime(2025, 1, 1),
+ run_type=DagRunType.SCHEDULED,
+ run_after=tz.datetime(2025, 1, 1),
+ session=session,
+ )
+ lazy_dags[dag.dag_id] = LazyDeserializedDAG.from_dag(dag)
+ with dag_maker("cm-partitioned", schedule=PartitionedAtRuntime(),
session=session) as dag:
Review Comment:
`PartitionedAtRuntime` has `can_be_scheduled = False`, so both resolvers
skip this Dag even though the fixture creates a run for it. The test would
still pass if `calculate_many` dropped partitioned Dags entirely. Could we use
two `CronPartitionTimetable` Dags alongside the plain Dag and assert the
expected latest run for each? Two partitioned Dags are needed to reach the
batched statement through the resolver.
--
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]