liwenjie200543 opened a new pull request, #73911: URL: https://github.com/apache/airflow/pull/73911
closes: #72393 ## Problem `DagModelOperation.find_orm_dags` eagerly loads five one-to-many collections on `DagModel` (`tags`, `schedule_asset_references`, `schedule_asset_alias_references`, `task_outlet_asset_references`, `dag_owner_links`) via `joinedload()` in a single statement. Joining several one-to-many collections in one query is the classic SQLAlchemy row-explosion anti-pattern: a DAG with 3 tags × 2 owner links returns 6 duplicate rows, each carrying the full wide `dag.*` column set. The multiplication grows with collections × rows-per-collection × DAGs per `update_dag_parsing_results_in_db` call, and showed up in production as >10s DagModel sync queries (see the issue for the `pg_stat_activity` evidence, including `wait_event: ClientWrite` — Postgres blocked sending the bloated result). ## What changed - The five collections now load with `selectinload()`, which issues one small secondary SELECT per collection instead of joining them into the main statement. Row-locking semantics are unchanged: `with_row_locks` still applies to the main `DagModel` select. - Added a regression test (`TestFindOrmDagsEagerLoading`) that asserts, via the SQL actually executed against the database (captured with a `before_cursor_execute` listener), that no statement joins the one-to-many tables, while the loaded tags and owner links remain correct. ## Verification - Standalone repro of the query shape: 1 DAG with 3 tags × 2 owner links ships 6 wide `dag.*` rows with `joinedload` vs 1 with `selectinload`; the gap scales linearly with tags/owners per DAG. - `ruff check` and `ruff format --check` pass with the repo configuration. - Full test suite runs in CI. ## Note This re-lands the approach of #72395 (closed on 2026-09-25 as part of the one-time open-PR-limit cull, with its review feedback addressed), adding the regression test from that review thread. Thanks @seanmuth for the original work; coordination comment posted on the issue. AI assistance: prepared with a coding agent (ZCode), reviewed and submitted by me. -- 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]
