manucorujo commented on PR #76003: URL: https://github.com/apache/airflow/pull/76003#issuecomment-6799975488
We see this in production on Airflow 3.4.0, and the lateral form fixes it on our data. **Setup:** PostgreSQL 18.6, `shared_buffers` 128 MB, `work_mem` 4 MB. `dag_run` has 982k rows for 34 active Dags. One Dag holds 445k of them, and 84% of all rows are older than 30 days. **Current query (`row_number()` from #74401):** 8.4 s in `EXPLAIN (ANALYZE, BUFFERS)`. The plan is a parallel seq scan of `dag_run` and an external merge sort (116 MB of temp files) for 442 returned rows. `GET /ui/dags?dag_runs_limit=14&limit=50` takes 7.5 to 8.7 s warm and 14 to 17 s cold at our gateway. **Lateral form (hand-written SQL of the same shape as this PR, not the PR code):** 26 ms. The plan is 34 index scans on `idx_dag_run_dag_id_run_after_desc` with an incremental sort. #74401 expected about 2 s for an all-hot page of 619k rows. A small metadata DB is much slower than that, because the sort goes to disk. I can post the two plans if they help the review. *Drafted with Claude Code, checked 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]
