task_instances page takes long time to load

[tirkarthi]

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. The database is MySQL 8.x series.

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_config 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, %(dag_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_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 (%(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, %(dag_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 parameters 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', 'dag_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_bash_decorator'}

Formatted sql with select dag_id from dag for dag_id list

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

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

[tirkarthi]

cc: @pierrejeambrun

[pierrejeambrun]

Thanks for that @tirkarthi, do you know if the bottleneck is coming from the db query or the application code in the backend ? When you say it takes around 1minute I assume you're talking about. the request to the backend taking 1 minute ? Does that particular request executed on the db takes 1 minute to load, like could you run the sql request against that particular db and see how long it takes?

[tirkarthi]

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)