Task Instances API: end_date / start_date filters use COALESCE(..., now()) and hurt index usage on large task_instance tables

[manipatnam]

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

• I agree to follow this project's Code of Conduct

[tirkarthi]

Can you please share the explain/explain analyze output and the sql generated? Does querying with state which is indexed help here? The endpoint received performance improvements in 3.2.x .

Related :

#62027
#61212

cc: @pierrejeambrun

[manipatnam]

@tirkarthi

We are trying to make similar api calls

https://abc.com/api/v2/dags/~/dagRuns/~/taskInstances?limit=1000&end_date_gte=2026-05-05T19:49:33Z&end_date_lte=2026-05-05T22:00:00Z&order_by=end_date

Nested Loop Left Join  (cost=1001.75..928157.61 rows=4169 width=4729) (actual time=26086.261..35141.064 rows=756 loops=1)
  Buffers: shared hit=158577 read=610220
  I/O Timings: shared read=102185.075
  ->  Nested Loop Left Join  (cost=1001.46..927219.79 rows=4169 width=3395) (actual time=26086.226..35140.304 rows=756 loops=1)
        Buffers: shared hit=158556 read=610220
        I/O Timings: shared read=102185.075
        ->  Gather  (cost=1001.17..924361.05 rows=4169 width=2803) (actual time=26086.188..35139.498 rows=756 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=158535 read=610220
              I/O Timings: shared read=102185.075
              ->  Nested Loop  (cost=1.17..922944.15 rows=1737 width=2803) (actual time=26083.792..35134.847 rows=252 loops=3)
                    Buffers: shared hit=158535 read=610220
                    I/O Timings: shared read=102185.075
                    ->  Nested Loop  (cost=0.61..915634.80 rows=1737 width=2070) (actual time=26083.768..35131.990 rows=252 loops=3)
                          Buffers: shared hit=154548 read=610220
                          I/O Timings: shared read=102185.075
                          ->  Parallel Seq Scan on task_instance  (cost=0.06..905114.16 rows=1737 width=2007) (actual time=26083.697..35129.184 rows=252 loops=3)
                                Filter: ((COALESCE(end_date, now()) >= '2026-05-05 19:49:33+00'::timestamp with time zone) AND (COALESCE(end_date, now()) <= '2026-05-05 22:00:00+00'::timestamp with time zone) AND ((dag_id)::text = ANY ('{replication_catch_up_86400_seconds,ratings_fitch_mssql_download,bg_markit_bond_pricing_sftp_download,private_market_cash_transaction,pb_barclays_axe,airflow-create-conns-vars,bloomberg_portfolio_upload,sharepoint_legal_entity_sync,fund_admin_monthly_performance_mssql,populate_liquidity_dashboard,omegapoint_portfolio_upload,fund_admin_leveling_asc_elt,pb_baml_ingest,replication_sync_formpf_seconds,TPIL-website-data-email,bg_jpmchase_sftp_download,delticus_certificate_email_process,fund_admin_position_geneva_mtd_mssql,pb_alert_data_integrity,ce-ground-truth_snowflake_ingest,pb_morganstanley_ingest,primebroker_openpoint_priceserve_load,astro_db_cleanup,table_stale_checks,weekly_credit_card_tracker}'::text[])))
                                Rows Removed by Filter: 4729043
                                Buffers: shared hit=150587 read=610220
                                I/O Timings: shared read=102185.075
                          ->  Index Only Scan using dag_run_dag_id_run_id_key on dag_run  (cost=0.55..6.06 rows=1 width=63) (actual time=0.010..0.010 rows=1 loops=756)
                                Index Cond: ((dag_id = (task_instance.dag_id)::text) AND (run_id = (task_instance.run_id)::text))
                                Heap Fetches: 932
                                Buffers: shared hit=3961
                    ->  Index Scan using dag_run_dag_id_run_id_key on dag_run dag_run_1  (cost=0.55..4.21 rows=1 width=796) (actual time=0.011..0.011 rows=1 loops=756)
                          Index Cond: (((dag_id)::text = (dag_run.dag_id)::text) AND ((run_id)::text = (dag_run.run_id)::text))
                          Buffers: shared hit=3956
        ->  Memoize  (cost=0.30..3.82 rows=1 width=592) (actual time=0.000..0.000 rows=1 loops=756)
              Cache Key: task_instance.dag_version_id
              Cache Mode: logical
              Hits: 749  Misses: 7  Evictions: 0  Overflows: 0  Memory Usage: 2kB
              Buffers: shared hit=21
              ->  Index Scan using dag_version_pkey on dag_version dag_version_1  (cost=0.29..3.81 rows=1 width=592) (actual time=0.007..0.007 rows=1 loops=7)
                    Index Cond: (id = task_instance.dag_version_id)
                    Buffers: shared hit=21
  ->  Memoize  (cost=0.28..3.58 rows=1 width=1334) (actual time=0.000..0.000 rows=1 loops=756)
        Cache Key: dag_run_1.dag_id
        Cache Mode: logical
        Hits: 749  Misses: 7  Evictions: 0  Overflows: 0  Memory Usage: 4kB
        Buffers: shared hit=21
        ->  Index Scan using dag_pkey on dag dag_1  (cost=0.27..3.57 rows=1 width=1334) (actual time=0.009..0.009 rows=1 loops=7)
              Index Cond: ((dag_id)::text = (dag_run_1.dag_id)::text)
              Buffers: shared hit=21
Planning:
  Buffers: shared hit=370
Planning Time: 3.604 ms
Execution Time: 35141.278 ms