manipatnam opened a new issue, #66335:
URL: https://github.com/apache/airflow/issues/66335
### Under which category would you file this issue?
Providers
### Apache Airflow version
3.1.2
### What happened and how to reproduce it?
### What happened
The REST endpoint that lists task instances supports `start_date_*` /
`end_date_*` query parameters (`start_date_gte`, `start_date_lte`,
`end_date_gte`, `end_date_lte`, etc.). For **`start_date`** and **`end_date`**,
Airflow generates SQL predicates on **`COALESCE(task_instance.start_date OR
end_date, now())`** instead of comparing those columns directly.
That matches the intended semantics: when **`start_date`** or **`end_date`**
is **`NULL`** (for example still-running work), treating the effective
timestamp as **`now()`** lets those rows intersect a bounded time filter.
On deployments with **large **`task_instance`** tables**, Postgres often
chooses a **`Parallel Seq Scan`** (or similar full-table-style scan of most of
the heap) because **`WHERE COALESCE(column, now())`** is generally **hard to
satisfy with btree indexes** on **`start_date`** / **`end_date`**.
The observable effect is **very slow LIST responses** or **client
timeouts**, especially for **`GET`** calls that use **`dags/~`/`dagRuns/~`**
alongside **`end_date_gte`** / **`end_date_lte`**.
Relevant layers (conceptual pointers):
- `airflow-core/src/airflow/api_fastapi/common/parameters.py` —
`datetime_range_filter_factory`; for `filter_name` in `("start_date",
"end_date")` uses `func.coalesce(attr, func.now())`; `RangeFilter.to_orm()`
turns that into `WHERE` inequalities.
-
`airflow-core/src/airflow/api_fastapi/core_api/routes/public/task_instances.py`
— list handler injects those dependencies (e.g.
`Depends(datetime_range_filter_factory("end_date", TI))`).
---
### How to reproduce
**Prerequisites**
- PostgreSQL-backed metadata DB.
- **`task_instance`** wide enough that sequential scans dominate (typically
**many millions** of rows).
**Steps**
1. Invoke the REST list API with **`end_date`** bounds. Example (adapt base
URL, `/api/` vs `/public/`, auth, bundle path as applicable):
```
GET
/api/v2/dags/~/dagRuns/~/taskInstances?limit=1000&end_date_gte=2026-05-03T19:49:33Z&end_date_lte=2026-05-03T22:00:00Z&order_by=end_date
```
### What you think should happen instead?
_No response_
### Operating System
debian
### Deployment
Astronomer
### Apache Airflow Provider(s)
_No response_
### Versions of Apache Airflow Providers
_No response_
### Official Helm Chart version
Not Applicable
### Kubernetes Version
_No response_
### Helm Chart configuration
_No response_
### Docker Image customizations
_No response_
### Anything else?
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
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]
