You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "tirkarthi (via GitHub)" <gi...@apache.org> on 2026/01/29 15:00:15 UTC

[I] task_instances page takes long time to load [airflow]

tirkarthi opened a new issue, #61212:
URL: https://github.com/apache/airflow/issues/61212

   ### Apache Airflow version
   
   3.1.6
   
   ### If "Other Airflow 3 version" selected, which one?
   
   _No response_
   
   ### What happened?
   
   In our non production instance there are close to 2 million task instances and 11k dags. It took around 1 minute to load the task instance page. On enabling `echo=True` below is the sql query. This is in my local instance but on our non production instance there are 11k dags passed to the where clause. The query seems to be complex with multiple joins to same table to avoid n+1 queries. This is on large instance and the other instance with few thousand task instances and local airflow instances are faster. When a filter matching fewer task instances like `state = deferred` is applied in UI then it is instant.
   
   ```
   diff --git a/airflow-core/src/airflow/settings.py b/airflow-core/src/airflow/settings.py
   index d93a1eb436..ae4a08c79b 100644
   --- a/airflow-core/src/airflow/settings.py
   +++ b/airflow-core/src/airflow/settings.py
   @@ -413,6 +413,7 @@ def configure_orm(disable_connection_pool=False, pool_class=None):
            connect_args=connect_args,
            **engine_args,
            future=True,
   +        echo=True
        )
        _configure_async_session()
        mask_secret(engine.url.password)
   ```
   
   
   ```
   [2026-01-29T14:47:05.456487Z] {base.py:1846} INFO - SELECT count(*) AS count_1 
   FROM (SELECT task_instance.id AS id, task_instance.task_id AS task_id, task_instance.dag_id AS dag_id, task_instance.run_id AS run_id, task_instance.map_index AS map_index, task_instance.start_date AS start_date, task_instance.end_date AS end_date, task_instance.duration AS duration, task_instance.state AS state, task_instance.try_number AS try_number, task_instance.max_tries AS max_tries, task_instance.hostname AS hostname, task_instance.unixname AS unixname, task_instance.pool AS pool, task_instance.pool_slots AS pool_slots, task_instance.queue AS queue, task_instance.priority_weight AS priority_weight, task_instance.operator AS operator, task_instance.custom_operator_name AS custom_operator_name, task_instance.queued_dttm AS queued_dttm, task_instance.scheduled_dttm AS scheduled_dttm, task_instance.queued_by_job_id AS queued_by_job_id, task_instance.last_heartbeat_at AS last_heartbeat_at, task_instance.pid AS pid, task_instance.executor AS executor, task_instance.executor_confi
 g AS executor_config, task_instance.updated_at AS updated_at, task_instance.rendered_map_index AS rendered_map_index, task_instance.context_carrier AS context_carrier, task_instance.span_status AS span_status, task_instance.external_executor_id AS external_executor_id, task_instance.trigger_id AS trigger_id, task_instance.trigger_timeout AS trigger_timeout, task_instance.next_method AS next_method, task_instance.next_kwargs AS next_kwargs, task_instance.task_display_name AS task_display_name, task_instance.dag_version_id AS dag_version_id 
   FROM task_instance INNER JOIN dag_run ON dag_run.dag_id = task_instance.dag_id AND dag_run.run_id = task_instance.run_id LEFT OUTER JOIN dag_version ON dag_version.id = task_instance.dag_version_id 
   WHERE task_instance.dag_id IN (%(dag_id_1_1)s, %(dag_id_1_2)s, %(dag_id_1_3)s, %(dag_id_1_4)s, %(dag_id_1_5)s, %(dag_id_1_6)s, %(dag_id_1_7)s, %(dag_id_1_8)s, %(dag_id_1_9)s, %(dag_id_1_10)s, %(dag_id_1_11)s, %(dag_id_1_12)s, %(dag_id_1_13)s, %(dag_id_1_14)s, %(dag_id_1_15)s, %(dag_id_1_16)s, %(dag_id_1_17)s, %(dag_id_1_18)s, %(dag_id_1_19)s, %(dag_id_1_20)s, %(dag_id_1_21)s, %(dag_id_1_22)s, %(dag_id_1_23)s, %(dag_id_1_24)s, %(dag_id_1_25)s, %(dag_id_1_26)s, %(dag_id_1_27)s, %(dag_id_1_28)s, %(dag_id_1_29)s, %(dag_id_1_30)s, %(dag_id_1_31)s, %(dag_id_1_32)s, %(dag_id_1_33)s, %(dag_id_1_34)s, %(dag_id_1_35)s, %(dag_id_1_36)s, %(dag_id_1_37)s, %(dag_id_1_38)s, %(dag_id_1_39)s, %(dag_id_1_40)s, %(dag_id_1_41)s, %(dag_id_1_42)s, %(dag_id_1_43)s, %(dag_id_1_44)s, %(dag_id_1_45)s, %(dag_id_1_46)s, %(dag_id_1_47)s, %(dag_id_1_48)s, %(dag_id_1_49)s, %(dag_id_1_50)s, %(dag_id_1_51)s, %(dag_id_1_52)s, %(dag_id_1_53)s, %(dag_id_1_54)s, %(dag_id_1_55)s, %(dag_id_1_56)s, %(dag_id_1_57)s, %(da
 g_id_1_58)s, %(dag_id_1_59)s, %(dag_id_1_60)s, %(dag_id_1_61)s, %(dag_id_1_62)s, %(dag_id_1_63)s, %(dag_id_1_64)s, %(dag_id_1_65)s, %(dag_id_1_66)s, %(dag_id_1_67)s, %(dag_id_1_68)s, %(dag_id_1_69)s, %(dag_id_1_70)s, %(dag_id_1_71)s, %(dag_id_1_72)s, %(dag_id_1_73)s, %(dag_id_1_74)s, %(dag_id_1_75)s, %(dag_id_1_76)s, %(dag_id_1_77)s, %(dag_id_1_78)s, %(dag_id_1_79)s, %(dag_id_1_80)s, %(dag_id_1_81)s, %(dag_id_1_82)s, %(dag_id_1_83)s, %(dag_id_1_84)s, %(dag_id_1_85)s, %(dag_id_1_86)s, %(dag_id_1_87)s, %(dag_id_1_88)s, %(dag_id_1_89)s, %(dag_id_1_90)s, %(dag_id_1_91)s, %(dag_id_1_92)s, %(dag_id_1_93)s, %(dag_id_1_94)s, %(dag_id_1_95)s, %(dag_id_1_96)s, %(dag_id_1_97)s, %(dag_id_1_98)s, %(dag_id_1_99)s, %(dag_id_1_100)s, %(dag_id_1_101)s, %(dag_id_1_102)s, %(dag_id_1_103)s, %(dag_id_1_104)s, %(dag_id_1_105)s, %(dag_id_1_106)s, %(dag_id_1_107)s, %(dag_id_1_108)s, %(dag_id_1_109)s, %(dag_id_1_110)s, %(dag_id_1_111)s, %(dag_id_1_112)s, %(dag_id_1_113)s, %(dag_id_1_114)s, %(dag_id_1_115)s,
  %(dag_id_1_116)s, %(dag_id_1_117)s, %(dag_id_1_118)s, %(dag_id_1_119)s, %(dag_id_1_120)s, %(dag_id_1_121)s, %(dag_id_1_122)s, %(dag_id_1_123)s, %(dag_id_1_124)s, %(dag_id_1_125)s, %(dag_id_1_126)s, %(dag_id_1_127)s, %(dag_id_1_128)s, %(dag_id_1_129)s, %(dag_id_1_130)s, %(dag_id_1_131)s, %(dag_id_1_132)s, %(dag_id_1_133)s, %(dag_id_1_134)s, %(dag_id_1_135)s, %(dag_id_1_136)s, %(dag_id_1_137)s, %(dag_id_1_138)s, %(dag_id_1_139)s, %(dag_id_1_140)s, %(dag_id_1_141)s, %(dag_id_1_142)s, %(dag_id_1_143)s, %(dag_id_1_144)s, %(dag_id_1_145)s, %(dag_id_1_146)s, %(dag_id_1_147)s, %(dag_id_1_148)s, %(dag_id_1_149)s, %(dag_id_1_150)s, %(dag_id_1_151)s, %(dag_id_1_152)s, %(dag_id_1_153)s, %(dag_id_1_154)s, %(dag_id_1_155)s, %(dag_id_1_156)s, %(dag_id_1_157)s, %(dag_id_1_158)s, %(dag_id_1_159)s, %(dag_id_1_160)s, %(dag_id_1_161)s)) AS anon_1
   [2026-01-29T14:47:05.456709Z] {base.py:1846} INFO - [generated in 0.00055s] {'dag_id_1_1': 'my_dag', 'dag_id_1_2': 'gh58560', 'dag_id_1_3': 'asset_consumes_unknown_never_scheduled', 'dag_id_1_4': 'gh49131', 'dag_id_1_5': 'example_kubernetes_executor', 'dag_id_1_6': 'gh60927', 'dag_id_1_7': 'dag_success', 'dag_id_1_8': 'asset_s3_bucket_producer_with_no_taskflow', 'dag_id_1_9': 'consumer', 'dag_id_1_10': 'latest_only', 'dag_id_1_11': 'dag_run_timeout_debug', 'dag_id_1_12': 'example_passing_params_via_test_command', 'dag_id_1_13': 'sample_version_dag', 'dag_id_1_14': 'example_setup_teardown', 'dag_id_1_15': 'log_query_1', 'dag_id_1_16': 'example_simplest_dag', 'dag_id_1_17': 'consume_1_or_2_with_asset_expressions', 'dag_id_1_18': 'example_custom_weight', 'dag_id_1_19': 'consume_1_or_both_2_and_3_with_asset_expressions', 'dag_id_1_20': 'catchup_test', 'dag_id_1_21': 'my_dag_trigger', 'dag_id_1_22': 'gh56427', 'dag_id_1_23': 'task_duration_1', 'dag_id_1_24': 'bash_simple', 'dag_id_1_25
 ': 'tutorial_taskflow_api', 'dag_id_1_26': 'producer3', 'dag_id_1_27': 'example_bash_operator', 'dag_id_1_28': 'example_task_group_decorator', 'dag_id_1_29': 'gh60069_src', 'dag_id_1_30': 'example_display_name', 'dag_id_1_31': 'example_branch_datetime_operator', 'dag_id_1_32': 'file_trigger', 'dag_id_1_33': 'example_producer_gh52932', 'dag_id_1_34': 'asset_alias_example_alias_producer_with_no_taskflow', 'dag_id_1_35': 'example_short_circuit_operator', 'dag_id_1_36': 'asset_s3_bucket_consumer', 'dag_id_1_37': 'example_time_delta_sensor_async', 'dag_id_1_38': 'example_trigger_target_dag', 'dag_id_1_39': 'test_asset', 'dag_id_1_40': 'gh49737', 'dag_id_1_41': 'example_consumer_gh52932', 'dag_id_1_42': 'example_external_task_marker_child', 'dag_id_1_43': 'asset_produces_1', 'dag_id_1_44': 'start_date_str', 'dag_id_1_45': 'check_once', 'dag_id_1_46': 'tutorial_taskflow_api_virtualenv', 'dag_id_1_47': 'triggerer_retry', 'dag_id_1_48': 'gh32757', 'dag_id_1_49': 'test_api_dag', 'dag_id_1_50'
 : 'log_groups' ... 61 parameters truncated ... 'dag_id_1_112': 'log_grouping', 'dag_id_1_113': 'asset_alias_example_alias_consumer', 'dag_id_1_114': 'asset_consumes_1_never_scheduled', 'dag_id_1_115': 'test_asset_and', 'dag_id_1_116': 'test_asset_single', 'dag_id_1_117': 'gh49714_1', 'dag_id_1_118': 'asset_consumes_1_and_2', 'dag_id_1_119': 'gh57792', 'dag_id_1_120': 'asset_with_extra_by_context', 'dag_id_1_121': 'exception_stacktracen', 'dag_id_1_122': 'read_asset_event_from_classic', 'dag_id_1_123': 'wrap_dag', 'dag_id_1_124': 'consumes_asset_decorator', 'dag_id_1_125': 'example_dynamic_task_mapping_with_no_taskflow_operators', 'dag_id_1_126': 'example_nested_branch_dag', 'dag_id_1_127': 'gh60069_target', 'dag_id_1_128': 'gh60594', 'dag_id_1_129': 'tutorial', 'dag_id_1_130': 'critical_log', 'dag_id_1_131': 'producer2', 'dag_id_1_132': 'asset_s3_bucket_producer', 'dag_id_1_133': 'example_setup_teardown_taskflow', 'dag_id_1_134': 'loaded_data', 'dag_id_1_135': 'task_duration', 'dag_
 id_1_136': 'example_xcom', 'dag_id_1_137': 'asset2_producer', 'dag_id_1_138': 'dummy_daily', 'dag_id_1_139': 'branching_issue', 'dag_id_1_140': 'gh49714', 'dag_id_1_141': 'example_sensor_decorator', 'dag_id_1_142': 'latest_only_with_trigger', 'dag_id_1_143': 'gh56218', 'dag_id_1_144': 'file_trigger_1', 'dag_id_1_145': 'example_external_task', 'dag_id_1_146': 'color_dag', 'dag_id_1_147': 'retry_defer_next_method', 'dag_id_1_148': 'asset_with_extra_by_yield', 'dag_id_1_149': 'asset_alias_example_alias_consumer_with_no_taskflow', 'dag_id_1_150': 'tutorial_objectstorage', 'dag_id_1_151': 'read_asset_event', 'dag_id_1_152': 'extracted_data', 'dag_id_1_153': 'tutorial_taskflow_templates', 'dag_id_1_154': 'task_duration_sensor', 'dag_id_1_155': 'asset1_producer', 'dag_id_1_156': 'example_workday_timetable', 'dag_id_1_157': 'example_short_circuit_decorator', 'dag_id_1_158': 'gh49696_1', 'dag_id_1_159': 'example_task_mapping_second_order', 'dag_id_1_160': 'example_branch_datetime_operator_3'
 , 'dag_id_1_161': 'example_bash_decorator'}
   [2026-01-29T14:47:05.485998Z] {base.py:1846} INFO - SELECT task_instance.id, task_instance.task_id, task_instance.dag_id, task_instance.run_id, task_instance.map_index, task_instance.start_date, task_instance.end_date, task_instance.duration, task_instance.state, task_instance.try_number, task_instance.max_tries, task_instance.hostname, task_instance.unixname, task_instance.pool, task_instance.pool_slots, task_instance.queue, task_instance.priority_weight, task_instance.operator, task_instance.custom_operator_name, task_instance.queued_dttm, task_instance.scheduled_dttm, task_instance.queued_by_job_id, task_instance.last_heartbeat_at, task_instance.pid, task_instance.executor, task_instance.executor_config, task_instance.updated_at, task_instance.rendered_map_index, task_instance.context_carrier, task_instance.span_status, task_instance.external_executor_id, task_instance.trigger_id, task_instance.trigger_timeout, task_instance.next_method, task_instance.next_kwargs, task_instance
 .task_display_name, task_instance.dag_version_id, dag_bundle_1.name, dag_bundle_1.active, dag_bundle_1.version, dag_bundle_1.last_refreshed, dag_bundle_1.signed_url_template, dag_bundle_1.template_params, dag_version_1.id AS id_1, dag_version_1.version_number, dag_version_1.dag_id AS dag_id_1, dag_version_1.bundle_name, dag_version_1.bundle_version, dag_version_1.created_at, dag_version_1.last_updated, dag_1.dag_id AS dag_id_2, dag_1.is_paused, dag_1.is_stale, dag_1.exceeds_max_non_backfill, dag_1.last_parsed_time, dag_1.last_parse_duration, dag_1.last_expired, dag_1.fileloc, dag_1.relative_fileloc, dag_1.bundle_name AS bundle_name_1, dag_1.bundle_version AS bundle_version_1, dag_1.owners, dag_1.dag_display_name, dag_1.description, dag_1.timetable_summary, dag_1.timetable_description, dag_1.timetable_type, dag_1.asset_expression, dag_1.deadline, dag_1.max_active_tasks, dag_1.max_active_runs, dag_1.max_consecutive_failed_dag_runs, dag_1.has_task_concurrency_limits, dag_1.has_import_e
 rrors, dag_1.fail_fast, dag_1.next_dagrun, dag_1.next_dagrun_data_interval_start, dag_1.next_dagrun_data_interval_end, dag_1.next_dagrun_create_after, dag_run_1.id AS id_2, dag_run_1.dag_id AS dag_id_3, dag_run_1.queued_at, dag_run_1.logical_date, dag_run_1.start_date AS start_date_1, dag_run_1.end_date AS end_date_1, dag_run_1.state AS state_1, dag_run_1.run_id AS run_id_1, dag_run_1.creating_job_id, dag_run_1.run_type, dag_run_1.triggered_by, dag_run_1.triggering_user_name, dag_run_1.conf, dag_run_1.data_interval_start, dag_run_1.data_interval_end, dag_run_1.run_after, dag_run_1.last_scheduling_decision, dag_run_1.log_template_id, dag_run_1.updated_at AS updated_at_1, dag_run_1.clear_number, dag_run_1.backfill_id, dag_run_1.bundle_version AS bundle_version_2, dag_run_1.scheduled_by_job_id, dag_run_1.context_carrier AS context_carrier_1, dag_run_1.span_status AS span_status_1, dag_run_1.created_dag_version_id, dag_run_1.partition_key, task_instance_note_1.ti_id, task_instance_note_
 1.user_id, task_instance_note_1.content, task_instance_note_1.created_at AS created_at_1, task_instance_note_1.updated_at AS updated_at_2 
   FROM task_instance INNER JOIN dag_run ON dag_run.dag_id = task_instance.dag_id AND dag_run.run_id = task_instance.run_id LEFT OUTER JOIN dag_version ON dag_version.id = task_instance.dag_version_id LEFT OUTER JOIN dag_version AS dag_version_1 ON dag_version_1.id = task_instance.dag_version_id LEFT OUTER JOIN dag_bundle AS dag_bundle_1 ON dag_version_1.bundle_name = dag_bundle_1.name INNER JOIN dag_run AS dag_run_1 ON dag_run_1.dag_id = task_instance.dag_id AND dag_run_1.run_id = task_instance.run_id LEFT OUTER JOIN dag AS dag_1 ON dag_run_1.dag_id = dag_1.dag_id LEFT OUTER JOIN task_instance_note AS task_instance_note_1 ON task_instance.id = task_instance_note_1.ti_id 
   WHERE task_instance.dag_id IN (%(dag_id_4_1)s, %(dag_id_4_2)s, %(dag_id_4_3)s, %(dag_id_4_4)s, %(dag_id_4_5)s, %(dag_id_4_6)s, %(dag_id_4_7)s, %(dag_id_4_8)s, %(dag_id_4_9)s, %(dag_id_4_10)s, %(dag_id_4_11)s, %(dag_id_4_12)s, %(dag_id_4_13)s, %(dag_id_4_14)s, %(dag_id_4_15)s, %(dag_id_4_16)s, %(dag_id_4_17)s, %(dag_id_4_18)s, %(dag_id_4_19)s, %(dag_id_4_20)s, %(dag_id_4_21)s, %(dag_id_4_22)s, %(dag_id_4_23)s, %(dag_id_4_24)s, %(dag_id_4_25)s, %(dag_id_4_26)s, %(dag_id_4_27)s, %(dag_id_4_28)s, %(dag_id_4_29)s, %(dag_id_4_30)s, %(dag_id_4_31)s, %(dag_id_4_32)s, %(dag_id_4_33)s, %(dag_id_4_34)s, %(dag_id_4_35)s, %(dag_id_4_36)s, %(dag_id_4_37)s, %(dag_id_4_38)s, %(dag_id_4_39)s, %(dag_id_4_40)s, %(dag_id_4_41)s, %(dag_id_4_42)s, %(dag_id_4_43)s, %(dag_id_4_44)s, %(dag_id_4_45)s, %(dag_id_4_46)s, %(dag_id_4_47)s, %(dag_id_4_48)s, %(dag_id_4_49)s, %(dag_id_4_50)s, %(dag_id_4_51)s, %(dag_id_4_52)s, %(dag_id_4_53)s, %(dag_id_4_54)s, %(dag_id_4_55)s, %(dag_id_4_56)s, %(dag_id_4_57)s, %(da
 g_id_4_58)s, %(dag_id_4_59)s, %(dag_id_4_60)s, %(dag_id_4_61)s, %(dag_id_4_62)s, %(dag_id_4_63)s, %(dag_id_4_64)s, %(dag_id_4_65)s, %(dag_id_4_66)s, %(dag_id_4_67)s, %(dag_id_4_68)s, %(dag_id_4_69)s, %(dag_id_4_70)s, %(dag_id_4_71)s, %(dag_id_4_72)s, %(dag_id_4_73)s, %(dag_id_4_74)s, %(dag_id_4_75)s, %(dag_id_4_76)s, %(dag_id_4_77)s, %(dag_id_4_78)s, %(dag_id_4_79)s, %(dag_id_4_80)s, %(dag_id_4_81)s, %(dag_id_4_82)s, %(dag_id_4_83)s, %(dag_id_4_84)s, %(dag_id_4_85)s, %(dag_id_4_86)s, %(dag_id_4_87)s, %(dag_id_4_88)s, %(dag_id_4_89)s, %(dag_id_4_90)s, %(dag_id_4_91)s, %(dag_id_4_92)s, %(dag_id_4_93)s, %(dag_id_4_94)s, %(dag_id_4_95)s, %(dag_id_4_96)s, %(dag_id_4_97)s, %(dag_id_4_98)s, %(dag_id_4_99)s, %(dag_id_4_100)s, %(dag_id_4_101)s, %(dag_id_4_102)s, %(dag_id_4_103)s, %(dag_id_4_104)s, %(dag_id_4_105)s, %(dag_id_4_106)s, %(dag_id_4_107)s, %(dag_id_4_108)s, %(dag_id_4_109)s, %(dag_id_4_110)s, %(dag_id_4_111)s, %(dag_id_4_112)s, %(dag_id_4_113)s, %(dag_id_4_114)s, %(dag_id_4_115)s,
  %(dag_id_4_116)s, %(dag_id_4_117)s, %(dag_id_4_118)s, %(dag_id_4_119)s, %(dag_id_4_120)s, %(dag_id_4_121)s, %(dag_id_4_122)s, %(dag_id_4_123)s, %(dag_id_4_124)s, %(dag_id_4_125)s, %(dag_id_4_126)s, %(dag_id_4_127)s, %(dag_id_4_128)s, %(dag_id_4_129)s, %(dag_id_4_130)s, %(dag_id_4_131)s, %(dag_id_4_132)s, %(dag_id_4_133)s, %(dag_id_4_134)s, %(dag_id_4_135)s, %(dag_id_4_136)s, %(dag_id_4_137)s, %(dag_id_4_138)s, %(dag_id_4_139)s, %(dag_id_4_140)s, %(dag_id_4_141)s, %(dag_id_4_142)s, %(dag_id_4_143)s, %(dag_id_4_144)s, %(dag_id_4_145)s, %(dag_id_4_146)s, %(dag_id_4_147)s, %(dag_id_4_148)s, %(dag_id_4_149)s, %(dag_id_4_150)s, %(dag_id_4_151)s, %(dag_id_4_152)s, %(dag_id_4_153)s, %(dag_id_4_154)s, %(dag_id_4_155)s, %(dag_id_4_156)s, %(dag_id_4_157)s, %(dag_id_4_158)s, %(dag_id_4_159)s, %(dag_id_4_160)s, %(dag_id_4_161)s) ORDER BY task_instance.start_date DESC, dag_run.run_after DESC, task_instance.id DESC 
    LIMIT %(param_1)s, %(param_2)s
   [2026-01-29T14:47:05.486188Z] {base.py:1846} INFO - [generated in 0.00053s] {'param_1': 0, 'param_2': 100, 'dag_id_4_1': 'my_dag', 'dag_id_4_2': 'gh58560', 'dag_id_4_3': 'asset_consumes_unknown_never_scheduled', 'dag_id_4_4': 'gh49131', 'dag_id_4_5': 'example_kubernetes_executor', 'dag_id_4_6': 'gh60927', 'dag_id_4_7': 'dag_success', 'dag_id_4_8': 'asset_s3_bucket_producer_with_no_taskflow', 'dag_id_4_9': 'consumer', 'dag_id_4_10': 'latest_only', 'dag_id_4_11': 'dag_run_timeout_debug', 'dag_id_4_12': 'example_passing_params_via_test_command', 'dag_id_4_13': 'sample_version_dag', 'dag_id_4_14': 'example_setup_teardown', 'dag_id_4_15': 'log_query_1', 'dag_id_4_16': 'example_simplest_dag', 'dag_id_4_17': 'consume_1_or_2_with_asset_expressions', 'dag_id_4_18': 'example_custom_weight', 'dag_id_4_19': 'consume_1_or_both_2_and_3_with_asset_expressions', 'dag_id_4_20': 'catchup_test', 'dag_id_4_21': 'my_dag_trigger', 'dag_id_4_22': 'gh56427', 'dag_id_4_23': 'task_duration_1', 'dag_id_4_24
 ': 'bash_simple', 'dag_id_4_25': 'tutorial_taskflow_api', 'dag_id_4_26': 'producer3', 'dag_id_4_27': 'example_bash_operator', 'dag_id_4_28': 'example_task_group_decorator', 'dag_id_4_29': 'gh60069_src', 'dag_id_4_30': 'example_display_name', 'dag_id_4_31': 'example_branch_datetime_operator', 'dag_id_4_32': 'file_trigger', 'dag_id_4_33': 'example_producer_gh52932', 'dag_id_4_34': 'asset_alias_example_alias_producer_with_no_taskflow', 'dag_id_4_35': 'example_short_circuit_operator', 'dag_id_4_36': 'asset_s3_bucket_consumer', 'dag_id_4_37': 'example_time_delta_sensor_async', 'dag_id_4_38': 'example_trigger_target_dag', 'dag_id_4_39': 'test_asset', 'dag_id_4_40': 'gh49737', 'dag_id_4_41': 'example_consumer_gh52932', 'dag_id_4_42': 'example_external_task_marker_child', 'dag_id_4_43': 'asset_produces_1', 'dag_id_4_44': 'start_date_str', 'dag_id_4_45': 'check_once', 'dag_id_4_46': 'tutorial_taskflow_api_virtualenv', 'dag_id_4_47': 'triggerer_retry', 'dag_id_4_48': 'gh32757' ... 63 paramete
 rs truncated ... 'dag_id_4_112': 'log_grouping', 'dag_id_4_113': 'asset_alias_example_alias_consumer', 'dag_id_4_114': 'asset_consumes_1_never_scheduled', 'dag_id_4_115': 'test_asset_and', 'dag_id_4_116': 'test_asset_single', 'dag_id_4_117': 'gh49714_1', 'dag_id_4_118': 'asset_consumes_1_and_2', 'dag_id_4_119': 'gh57792', 'dag_id_4_120': 'asset_with_extra_by_context', 'dag_id_4_121': 'exception_stacktracen', 'dag_id_4_122': 'read_asset_event_from_classic', 'dag_id_4_123': 'wrap_dag', 'dag_id_4_124': 'consumes_asset_decorator', 'dag_id_4_125': 'example_dynamic_task_mapping_with_no_taskflow_operators', 'dag_id_4_126': 'example_nested_branch_dag', 'dag_id_4_127': 'gh60069_target', 'dag_id_4_128': 'gh60594', 'dag_id_4_129': 'tutorial', 'dag_id_4_130': 'critical_log', 'dag_id_4_131': 'producer2', 'dag_id_4_132': 'asset_s3_bucket_producer', 'dag_id_4_133': 'example_setup_teardown_taskflow', 'dag_id_4_134': 'loaded_data', 'dag_id_4_135': 'task_duration', 'dag_id_4_136': 'example_xcom', 'da
 g_id_4_137': 'asset2_producer', 'dag_id_4_138': 'dummy_daily', 'dag_id_4_139': 'branching_issue', 'dag_id_4_140': 'gh49714', 'dag_id_4_141': 'example_sensor_decorator', 'dag_id_4_142': 'latest_only_with_trigger', 'dag_id_4_143': 'gh56218', 'dag_id_4_144': 'file_trigger_1', 'dag_id_4_145': 'example_external_task', 'dag_id_4_146': 'color_dag', 'dag_id_4_147': 'retry_defer_next_method', 'dag_id_4_148': 'asset_with_extra_by_yield', 'dag_id_4_149': 'asset_alias_example_alias_consumer_with_no_taskflow', 'dag_id_4_150': 'tutorial_objectstorage', 'dag_id_4_151': 'read_asset_event', 'dag_id_4_152': 'extracted_data', 'dag_id_4_153': 'tutorial_taskflow_templates', 'dag_id_4_154': 'task_duration_sensor', 'dag_id_4_155': 'asset1_producer', 'dag_id_4_156': 'example_workday_timetable', 'dag_id_4_157': 'example_short_circuit_decorator', 'dag_id_4_158': 'gh49696_1', 'dag_id_4_159': 'example_task_mapping_second_order', 'dag_id_4_160': 'example_branch_datetime_operator_3', 'dag_id_4_161': 'example_bas
 h_decorator'}
   ```
   
   Formatted sql with `select dag_id from dag` for dag_id list
   
   ```sql
   SELECT          task_instance.id,
                   task_instance.task_id,
                   task_instance.dag_id,
                   task_instance.run_id,
                   task_instance.map_index,
                   task_instance.start_date,
                   task_instance.end_date,
                   task_instance.duration,
                   task_instance.state,
                   task_instance.try_number,
                   task_instance.max_tries,
                   task_instance.hostname,
                   task_instance.unixname,
                   task_instance.pool,
                   task_instance.pool_slots,
                   task_instance.queue,
                   task_instance.priority_weight,
                   task_instance.operator,
                   task_instance.custom_operator_name,
                   task_instance.queued_dttm,
                   task_instance.scheduled_dttm,
                   task_instance.queued_by_job_id,
                   task_instance.last_heartbeat_at,
                   task_instance.pid,
                   task_instance.executor,
                   task_instance.executor_config,
                   task_instance.updated_at,
                   task_instance.rendered_map_index,
                   task_instance.context_carrier,
                   task_instance.span_status,
                   task_instance.external_executor_id,
                   task_instance.trigger_id,
                   task_instance.trigger_timeout,
                   task_instance.next_method,
                   task_instance.next_kwargs,
                   task_instance.task_display_name,
                   task_instance.dag_version_id,
                   dag_bundle_1.name,
                   dag_bundle_1.active,
                   dag_bundle_1.version,
                   dag_bundle_1.last_refreshed,
                   dag_bundle_1.signed_url_template,
                   dag_bundle_1.template_params,
                   dag_version_1.id AS id_1,
                   dag_version_1.version_number,
                   dag_version_1.dag_id AS dag_id_1,
                   dag_version_1.bundle_name,
                   dag_version_1.bundle_version,
                   dag_version_1.created_at,
                   dag_version_1.last_updated,
                   dag_1.dag_id AS dag_id_2,
                   dag_1.is_paused,
                   dag_1.is_stale,
                   dag_1.exceeds_max_non_backfill,
                   dag_1.last_parsed_time,
                   dag_1.last_parse_duration,
                   dag_1.last_expired,
                   dag_1.fileloc,
                   dag_1.relative_fileloc,
                   dag_1.bundle_name    AS bundle_name_1,
                   dag_1.bundle_version AS bundle_version_1,
                   dag_1.owners,
                   dag_1.dag_display_name,
                   dag_1.description,
                   dag_1.timetable_summary,
                   dag_1.timetable_description,
                   dag_1.timetable_type,
                   dag_1.asset_expression,
                   dag_1.deadline,
                   dag_1.max_active_tasks,
                   dag_1.max_active_runs,
                   dag_1.max_consecutive_failed_dag_runs,
                   dag_1.has_task_concurrency_limits,
                   dag_1.has_import_errors,
                   dag_1.fail_fast,
                   dag_1.next_dagrun,
                   dag_1.next_dagrun_data_interval_start,
                   dag_1.next_dagrun_data_interval_end,
                   dag_1.next_dagrun_create_after,
                   dag_run_1.id     AS id_2,
                   dag_run_1.dag_id AS dag_id_3,
                   dag_run_1.queued_at,
                   dag_run_1.logical_date,
                   dag_run_1.start_date AS start_date_1,
                   dag_run_1.end_date   AS end_date_1,
                   dag_run_1.state      AS state_1,
                   dag_run_1.run_id     AS run_id_1,
                   dag_run_1.creating_job_id,
                   dag_run_1.run_type,
                   dag_run_1.triggered_by,
                   dag_run_1.triggering_user_name,
                   dag_run_1.conf,
                   dag_run_1.data_interval_start,
                   dag_run_1.data_interval_end,
                   dag_run_1.run_after,
                   dag_run_1.last_scheduling_decision,
                   dag_run_1.log_template_id,
                   dag_run_1.updated_at AS updated_at_1,
                   dag_run_1.clear_number,
                   dag_run_1.backfill_id,
                   dag_run_1.bundle_version AS bundle_version_2,
                   dag_run_1.scheduled_by_job_id,
                   dag_run_1.context_carrier AS context_carrier_1,
                   dag_run_1.span_status     AS span_status_1,
                   dag_run_1.created_dag_version_id,
                   dag_run_1.partition_key,
                   task_instance_note_1.ti_id,
                   task_instance_note_1.user_id,
                   task_instance_note_1.content,
                   task_instance_note_1.created_at AS created_at_1,
                   task_instance_note_1.updated_at AS updated_at_2
   FROM            task_instance
   INNER JOIN      dag_run
   ON              dag_run.dag_id = task_instance.dag_id
   AND             dag_run.run_id = task_instance.run_id
   LEFT OUTER JOIN dag_version
   ON              dag_version.id = task_instance.dag_version_id
   LEFT OUTER JOIN dag_version AS dag_version_1
   ON              dag_version_1.id = task_instance.dag_version_id
   LEFT OUTER JOIN dag_bundle AS dag_bundle_1
   ON              dag_version_1.bundle_name = dag_bundle_1.name
   INNER JOIN      dag_run AS dag_run_1
   ON              dag_run_1.dag_id = task_instance.dag_id
   AND             dag_run_1.run_id = task_instance.run_id
   LEFT OUTER JOIN dag AS dag_1
   ON              dag_run_1.dag_id = dag_1.dag_id
   LEFT OUTER JOIN task_instance_note AS task_instance_note_1
   ON              task_instance.id = task_instance_note_1.ti_id
   WHERE           task_instance.dag_id IN (select dag_id from dag)
   ORDER BY        task_instance.start_date DESC,
                   dag_run.run_after DESC,
                   task_instance.id DESC
   LIMIT           1, 10;
   ```
   
   ### What you think should happen instead?
   
   _No response_
   
   ### How to reproduce
   
   1. Enable sql query logging with above option.
   2. Load task_instances page without filters in a large instance with close to a million task instances.
   
   ### Operating System
   
   Ubuntu 20.04
   
   ### Versions of Apache Airflow Providers
   
   _No response_
   
   ### Deployment
   
   Virtualenv installation
   
   ### Deployment details
   
   _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: commits-unsubscribe@airflow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] task_instances page takes long time to load [airflow]

Posted by "tirkarthi (via GitHub)" <gi...@apache.org>.
tirkarthi commented on issue #61212:
URL: https://github.com/apache/airflow/issues/61212#issuecomment-3818250159

   cc: @pierrejeambrun 


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] task_instances page takes long time to load [airflow]

Posted by "tirkarthi (via GitHub)" <gi...@apache.org>.
tirkarthi closed issue #61212: task_instances page takes long time to load
URL: https://github.com/apache/airflow/issues/61212


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] task_instances page takes long time to load [airflow]

Posted by "tirkarthi (via GitHub)" <gi...@apache.org>.
tirkarthi commented on issue #61212:
URL: https://github.com/apache/airflow/issues/61212#issuecomment-3818821057

   The sql executed on the db took around 1 minute to complete. When a filter like `state = "deferred"` that returns fewer task instances then it was instant. Another thing I noticed is that start_date is sorted by default and is using filesort but state is using an index as per explain.
   
   https://stackoverflow.com/questions/12148943/mysql-performance-slow-using-filesort
   
   ```
   mysql> explain select id from task_instance order by start_date, id desc limit 1;
   +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+----------------+
   | id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
   +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+----------------+
   |  1 | SIMPLE      | task_instance | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   29 |   100.00 | Using filesort |
   +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+----------------+
   1 row in set, 1 warning (0.00 sec)
   
   mysql> explain select id from task_instance order by state desc limit 1;
   +----+-------------+---------------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------+
   | id | select_type | table         | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                            |
   +----+-------------+---------------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------+
   |  1 | SIMPLE      | task_instance | NULL       | index | NULL          | ti_state | 83      | NULL |    1 |   100.00 | Backward index scan; Using index |
   +----+-------------+---------------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------+
   1 row in set, 1 warning (0.00 sec)
   ```
   
   ```
   mysql> show create table task_instance \G;
   *************************** 1. row ***************************
          Table: task_instance
   Create Table: CREATE TABLE `task_instance` (
     `id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
     `task_id` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
     `dag_id` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
     `run_id` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
     `map_index` int NOT NULL DEFAULT '-1',
     `start_date` timestamp(6) NULL DEFAULT NULL,
     `end_date` timestamp(6) NULL DEFAULT NULL,
     `duration` float DEFAULT NULL,
     `state` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `try_number` int NOT NULL,
     `max_tries` int NOT NULL DEFAULT '-1',
     `hostname` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
     `unixname` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
     `pool` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL,
     `pool_slots` int NOT NULL,
     `queue` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL,
     `priority_weight` int NOT NULL,
     `operator` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `custom_operator_name` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
     `queued_dttm` timestamp(6) NULL DEFAULT NULL,
     `scheduled_dttm` timestamp(6) NULL DEFAULT NULL,
     `queued_by_job_id` int DEFAULT NULL,
     `last_heartbeat_at` timestamp(6) NULL DEFAULT NULL,
     `pid` int DEFAULT NULL,
     `executor` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `executor_config` blob NOT NULL,
     `updated_at` timestamp(6) NULL DEFAULT NULL,
     `rendered_map_index` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `context_carrier` json DEFAULT NULL,
     `span_status` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'not_started',
     `external_executor_id` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
     `trigger_id` int DEFAULT NULL,
     `trigger_timeout` timestamp(6) NULL DEFAULT NULL,
     `next_method` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `next_kwargs` json DEFAULT NULL,
     `task_display_name` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `dag_version_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `task_instance_composite_key` (`dag_id`,`task_id`,`run_id`,`map_index`),
     KEY `task_instance_dag_version_id_fkey` (`dag_version_id`),
     KEY `ti_state_lkp` (`dag_id`,`task_id`,`run_id`,`state`),
     KEY `ti_trigger_id` (`trigger_id`),
     KEY `ti_pool` (`pool`,`state`,`priority_weight`),
     KEY `ti_dag_run` (`dag_id`,`run_id`),
     KEY `ti_dag_state` (`dag_id`,`state`),
     KEY `ti_heartbeat` (`last_heartbeat_at`),
     KEY `ti_state` (`state`),
     CONSTRAINT `task_instance_dag_run_fkey` FOREIGN KEY (`dag_id`, `run_id`) REFERENCES `dag_run` (`dag_id`, `run_id`) ON DELETE CASCADE,
     CONSTRAINT `task_instance_dag_version_id_fkey` FOREIGN KEY (`dag_version_id`) REFERENCES `dag_version` (`id`) ON DELETE RESTRICT,
     CONSTRAINT `task_instance_trigger_id_fkey` FOREIGN KEY (`trigger_id`) REFERENCES `trigger` (`id`) ON DELETE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
   1 row in set (0.00 sec)
   
   ```


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] task_instances page takes long time to load [airflow]

Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun commented on issue #61212:
URL: https://github.com/apache/airflow/issues/61212#issuecomment-3824622352

   Might be because of the non indexed start date. Additional indexes depending on airflow usage isn't handled by airflow itself but deferred to users. Doc here `airflow/airflow-core/docs/howto/performance.rst`
   
   Do you think adding such index here could solve the issue, or is that coming from the multiple joins? (If it's the join we could conditionally join depending on the filters applied, instead of always joining everything).
   
   Thanks for your investigations


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] task_instances page takes long time to load [airflow]

Posted by "pierrejeambrun (via GitHub)" <gi...@apache.org>.
pierrejeambrun commented on issue #61212:
URL: https://github.com/apache/airflow/issues/61212#issuecomment-3818717450

   Thanks for that @tirkarthi, do you know if the bottleneck is coming from the db query or the application code ?  When you say it takes around `1minute` I assume the request to the backend is taking 1 minute, or does that particular request executed on the db takes 1 minute to load ?


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] task_instances page takes long time to load [airflow]

Posted by "tirkarthi (via GitHub)" <gi...@apache.org>.
tirkarthi commented on issue #61212:
URL: https://github.com/apache/airflow/issues/61212#issuecomment-4669815671

   Closing in favour of https://github.com/apache/airflow/pull/62910 and https://github.com/apache/airflow/issues/62027


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org