seanmuth commented on PR #65602:
URL: https://github.com/apache/airflow/pull/65602#issuecomment-4290178592
## 0114 dag_id updated_at
_(Note: the actual query produced here ends with a `WHERE ...
task_instance.dag_id IN ('list', 'of', 'dag_ids')` but the resulting query is
longer than the psql default 4096 char limit, so I've substituted `... IN
(select dag_id from dag)` to mimic the "worst-case" scenario where the query is
being run over a list of 1000s of dags, which is the scenario we found this
query from)_
Before:
```
=> EXPLAIN ANALYZE SELECT task_instance.rendered_map_index,
task_instance.task_display_name, 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.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
.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_run_1.state AS
state_1, dag_1.dag_display_name, dag_1.deadline, dag_1.dag_id AS dag_id_2,
dag_1.is_paused, dag_1.is_stale, 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.description,
dag_1.timetable_summary, dag_1.timetable_description, dag_1.asset_expression,
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.next_dagrun, dag_1.next_dagrun_data_interval_s
tart, 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.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,
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 upda
ted_at_2
FROM task_instance 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 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.updated_at >= '2026-04-21T07:00:00+00:00'::timestamptz
AND task_instance.updated_at < '2026-04-21T15:00:00+00:00'::timestamptz AND
task_instance.dag_id IN (select dag_id from dag);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=81632.46..923436.39 rows=68310 width=3169) (actual
time=2525.796..35686.290 rows=58040 loops=1)
Workers Planned: 2
Workers Launched: 0
-> Hash Join (cost=80632.46..915605.39 rows=28462 width=3169) (actual
time=2524.265..35569.585 rows=58040 loops=1)
Hash Cond: ((dag_run.dag_id)::text = (dag.dag_id)::text)
-> Hash Left Join (cost=80261.97..915160.13 rows=28462
width=3198) (actual time=2522.938..35534.218 rows=58040 loops=1)
Hash Cond: (task_instance.id = task_instance_note_1.ti_id)
-> Nested Loop Left Join (cost=80260.84..915084.28
rows=28462 width=2376) (actual time=2522.915..35503.392 rows=58040 loops=1)
-> Hash Left Join (cost=80260.54..913816.48
rows=28462 width=1865) (actual time=2522.895..35434.210 rows=58040 loops=1)
Hash Cond: ((dag_version_1.bundle_name)::text =
(dag_bundle_1.name)::text)
-> Nested Loop Left Join
(cost=80255.48..913631.16 rows=28462 width=1573) (actual
time=2522.871..35401.171 rows=58040 loops=1)
-> Nested Loop (cost=80255.05..907034.29
rows=28462 width=1446) (actual time=2522.853..35339.836 rows=58040 loops=1)
-> Parallel Hash Join
(cost=80254.48..166321.31 rows=237683 width=425) (actual
time=2068.636..3370.229 rows=1018592 loops=1)
Hash Cond:
(((dag_run.dag_id)::text = (dag_run_1.dag_id)::text) AND
((dag_run.run_id)::text = (dag_run_1.run_id)::text))
-> Parallel Seq Scan on
dag_run (cost=0.00..53982.79 rows=424579 width=68) (actual time=0.006..237.348
rows=1018592 loops=1)
-> Parallel Hash
(cost=53982.79..53982.79 rows=424579 width=357) (actual time=1453.041..1453.041
rows=1018592 loops=1)
Buckets: 32768 Batches:
64 Memory Usage: 5824kB
-> Parallel Seq Scan on
dag_run dag_run_1 (cost=0.00..53982.79 rows=424579 width=357) (actual
time=0.005..442.481 rows=1018592 loops=1)
-> Memoize (cost=0.57..3.22 rows=1
width=1060) (actual time=0.031..0.031 rows=0 loops=1018592)
Cache Key: dag_run.dag_id,
dag_run.run_id
Cache Mode: logical
Hits: 0 Misses: 1018592
Evictions: 965176 Overflows: 0 Memory Usage: 8193kB
-> Index Scan using ti_dag_run
on task_instance (cost=0.56..3.21 rows=1 width=1060) (actual time=0.029..0.030
rows=0 loops=1018592)
Index Cond:
(((dag_id)::text = (dag_run.dag_id)::text) AND ((run_id)::text =
(dag_run.run_id)::text))
Filter: ((updated_at >=
'2026-04-21 07:00:00+00'::timestamp with time zone) AND (updated_at <
'2026-04-21 15:00:00+00'::timestamp with time zone))
Rows Removed by Filter: 13
-> Memoize (cost=0.43..0.72 rows=1
width=127) (actual time=0.001..0.001 rows=1 loops=58040)
Cache Key:
task_instance.dag_version_id
Cache Mode: logical
Hits: 57006 Misses: 1034 Evictions:
0 Overflows: 0 Memory Usage: 247kB
-> Index Scan using dag_version_pkey
on dag_version dag_version_1 (cost=0.42..0.71 rows=1 width=127) (actual
time=0.015..0.015 rows=1 loops=1034)
Index Cond: (id =
task_instance.dag_version_id)
-> Hash (cost=5.03..5.03 rows=3 width=292)
(actual time=0.013..0.014 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on dag_bundle dag_bundle_1
(cost=0.00..5.03 rows=3 width=292) (actual time=0.004..0.010 rows=3 loops=1)
-> Memoize (cost=0.29..0.31 rows=1 width=511) (actual
time=0.000..0.000 rows=1 loops=58040)
Cache Key: dag_run_1.dag_id
Cache Mode: logical
Hits: 57131 Misses: 909 Evictions: 0
Overflows: 0 Memory Usage: 516kB
-> Index Scan using dag_pkey on dag dag_1
(cost=0.28..0.30 rows=1 width=511) (actual time=0.008..0.008 rows=1 loops=909)
Index Cond: ((dag_id)::text =
(dag_run_1.dag_id)::text)
-> Hash (cost=1.06..1.06 rows=6 width=822) (actual
time=0.016..0.016 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on task_instance_note task_instance_note_1
(cost=0.00..1.06 rows=6 width=822) (actual time=0.005..0.006 rows=6 loops=1)
-> Hash (cost=309.47..309.47 rows=4881 width=30) (actual
time=1.285..1.285 rows=2953 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 244kB
-> Index Only Scan using dag_pkey on dag (cost=0.28..309.47
rows=4881 width=30) (actual time=0.014..0.673 rows=2953 loops=1)
Heap Fetches: 552
Planning Time: 6.166 ms
Execution Time: 35692.160 ms
```
After:
```
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1008.87..234132.43 rows=65663 width=3190) (actual
time=1.395..616.512 rows=58040 loops=1)
-> Gather (cost=1008.58..231946.95 rows=65663 width=3219) (actual
time=1.385..537.908 rows=58040 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Left Join (cost=8.58..224380.65 rows=27360 width=3219)
(actual time=0.226..559.899 rows=19347 loops=3)
Hash Cond: (task_instance.id = task_instance_note_1.ti_id)
-> Nested Loop Left Join (cost=7.45..224307.70 rows=27360
width=2397) (actual time=0.202..550.350 rows=19347 loops=3)
-> Nested Loop (cost=7.16..223072.07 rows=27360
width=1886) (actual time=0.181..522.064 rows=19347 loops=3)
-> Hash Left Join (cost=6.61..199875.17
rows=27360 width=1568) (actual time=0.156..297.605 rows=19347 loops=3)
Hash Cond:
((dag_version_1.bundle_name)::text = (dag_bundle_1.name)::text)
-> Nested Loop Left Join
(cost=1.54..199696.82 rows=27360 width=1276) (actual time=0.120..287.439
rows=19347 loops=3)
-> Nested Loop
(cost=1.11..193158.23 rows=27360 width=1149) (actual time=0.102..267.731
rows=19347 loops=3)
-> Parallel Index Scan using
idx_cre_ti_updated_at_dag_id on task_instance (cost=0.56..117343.78 rows=27360
width=1081) (actual time=0.030..18.357 rows=19347 loops=3)
Index Cond: ((updated_at
>= '2026-04-21 07:00:00+00'::timestamp with time zone) AND (updated_at <
'2026-04-21 15:00:00+00'::timestamp with time zone))
-> Index Only Scan using
dag_run_dag_id_run_id_key on dag_run (cost=0.55..2.77 rows=1 width=68) (actual
time=0.012..0.012 rows=1 loops=58040)
Index Cond: ((dag_id =
(task_instance.dag_id)::text) AND (run_id = (task_instance.run_id)::text))
Heap Fetches: 22022
-> Memoize (cost=0.43..0.73 rows=1
width=127) (actual time=0.001..0.001 rows=1 loops=58040)
Cache Key:
task_instance.dag_version_id
Cache Mode: logical
Hits: 16810 Misses: 880
Evictions: 0 Overflows: 0 Memory Usage: 210kB
Worker 0: Hits: 19679 Misses:
910 Evictions: 0 Overflows: 0 Memory Usage: 217kB
Worker 1: Hits: 18876 Misses:
885 Evictions: 0 Overflows: 0 Memory Usage: 212kB
-> Index Scan using
dag_version_pkey on dag_version dag_version_1 (cost=0.42..0.72 rows=1
width=127) (actual time=0.004..0.004 rows=1 loops=2675)
Index Cond: (id =
task_instance.dag_version_id)
-> Hash (cost=5.03..5.03 rows=3
width=292) (actual time=0.020..0.020 rows=3 loops=3)
Buckets: 1024 Batches: 1 Memory
Usage: 9kB
-> Seq Scan on dag_bundle
dag_bundle_1 (cost=0.00..5.03 rows=3 width=292) (actual time=0.008..0.013
rows=3 loops=3)
-> Index Scan using dag_run_dag_id_run_id_key on
dag_run dag_run_1 (cost=0.55..0.85 rows=1 width=357) (actual time=0.011..0.011
rows=1 loops=58040)
Index Cond: (((dag_id)::text =
(dag_run.dag_id)::text) AND ((run_id)::text = (dag_run.run_id)::text))
-> Memoize (cost=0.29..0.31 rows=1 width=511) (actual
time=0.001..0.001 rows=1 loops=58040)
Cache Key: dag_run_1.dag_id
Cache Mode: logical
Hits: 16896 Misses: 794 Evictions: 0
Overflows: 0 Memory Usage: 454kB
Worker 0: Hits: 19772 Misses: 817 Evictions: 0
Overflows: 0 Memory Usage: 465kB
Worker 1: Hits: 18957 Misses: 804 Evictions: 0
Overflows: 0 Memory Usage: 459kB
-> Index Scan using dag_pkey on dag dag_1
(cost=0.28..0.30 rows=1 width=511) (actual time=0.006..0.006 rows=1 loops=2415)
Index Cond: ((dag_id)::text =
(dag_run_1.dag_id)::text)
-> Hash (cost=1.06..1.06 rows=6 width=822) (actual
time=0.012..0.013 rows=6 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on task_instance_note task_instance_note_1
(cost=0.00..1.06 rows=6 width=822) (actual time=0.008..0.008 rows=6 loops=3)
-> Memoize (cost=0.29..0.31 rows=1 width=30) (actual time=0.000..0.000
rows=1 loops=58040)
Cache Key: dag_run.dag_id
Cache Mode: logical
Hits: 57131 Misses: 909 Evictions: 0 Overflows: 0 Memory Usage:
141kB
-> Index Only Scan using dag_pkey on dag (cost=0.28..0.30 rows=1
width=30) (actual time=0.004..0.004 rows=1 loops=909)
Index Cond: (dag_id = (dag_run.dag_id)::text)
Heap Fetches: 595
Planning Time: 7.390 ms
Execution Time: 619.691 ms
```
--
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]
