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]

Reply via email to