Skip to content

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

Closed

Description

@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

Activity

  1. added
    kind:bugThis is a clearly a bug
    needs-triagelabel for new issues that we didn't triage yet
    on May 4, 2026
  2. added
    area:APIAirflow's REST/HTTP API
    priority:highHigh priority bug that should be patched quickly but does not require immediate new release
    on May 4, 2026
  3. tirkarthi commented on May 5, 2026

    @tirkarthi
    Contributor

    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

  4. manipatnam commented on May 6, 2026

    @manipatnam
    ContributorAuthor

    @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
    
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    area:APIAirflow's REST/HTTP APIarea:performancekind:bugThis is a clearly a bugneeds-triagelabel for new issues that we didn't triage yetpriority:highHigh priority bug that should be patched quickly but does not require immediate new release

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions