Apache Airflow version
3.1.6
If "Other Airflow 3 version" selected, which one?
No response
What happened?
When trying to remove (a lot of) dag runs or task_instances, I bumped into the issue that these deletes completed almost never (or very slow). Similar in airflow db clean -t task_instances.
Query plan in postgres shows me that the trigger to task_reschedule takes most of the time
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
DELETE FROM task_instance where id = '019aaafc-5742-7e39-8425-e60423889d96';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.task_instance (cost=0.42..4.44 rows=0 width=0) (actual time=67533.896..67533.897 rows=0 loops=1)
Buffers: shared hit=6 dirtied=1
-> Index Scan using task_instance_pkey on public.task_instance (cost=0.42..4.44 rows=1 width=6) (actual time=0.017..0.020 rows=1 loops=1)
Output: ctid
Index Cond: (task_instance.id = '019aaafc-5742-7e39-8425-e60423889d96'::uuid)
Buffers: shared hit=4
Query Identifier: -3284382374466636880
Planning Time: 0.106 ms
Trigger RI_ConstraintTrigger_a_40684537 for constraint rtif_ti_fkey: time=0.246 calls=1
Trigger RI_ConstraintTrigger_a_40684559 for constraint xcom_task_instance_fkey: time=0.290 calls=1
Trigger RI_ConstraintTrigger_a_40684564 for constraint task_instance_history_ti_fkey: time=0.167 calls=1
Trigger RI_ConstraintTrigger_a_40684569 for constraint task_map_task_instance_fkey: time=0.170 calls=1
Trigger RI_ConstraintTrigger_a_40693819 for constraint task_reschedule_ti_fkey: time=55.026 calls=1
Trigger RI_ConstraintTrigger_a_40693828 for constraint task_instance_note_ti_fkey: time=0.122 calls=1
Trigger RI_ConstraintTrigger_a_40693874 for constraint hitl_detail_ti_fkey: time=0.045 calls=1
Execution Time: 67589.993 ms
What you think should happen instead?
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_task_reschedule_ti_id
ON task_reschedule(ti_id);
after the above index, deletion succeeds a lot faster
How to reproduce
Upgraded from airflow 2.10 to 3.1.6. I wanted to clean up task_instances database afterwards
Operating System
Airflow docker image on AKS
Versions of Apache Airflow Providers
apache-airflow-providers-amazon==9.19.0
apache-airflow-providers-celery==3.15.0
apache-airflow-providers-cncf-kubernetes==10.9.0
apache-airflow-providers-common-compat==1.10.1
apache-airflow-providers-common-io==1.6.4
apache-airflow-providers-common-messaging==2.0.1
apache-airflow-providers-common-sql==1.28.2
apache-airflow-providers-databricks==7.7.4
apache-airflow-providers-docker==4.5.1
apache-airflow-providers-elasticsearch==6.4.2
apache-airflow-providers-fab==3.1.1
apache-airflow-providers-ftp==3.14.0
apache-airflow-providers-git==0.2.0
apache-airflow-providers-google==19.3.0
apache-airflow-providers-grpc==3.9.1
apache-airflow-providers-hashicorp==4.4.1
apache-airflow-providers-http==5.6.2
apache-airflow-providers-imap==3.9.3
apache-airflow-providers-microsoft-azure==12.8.0
apache-airflow-providers-mysql==6.4.0
apache-airflow-providers-odbc==4.11.0
apache-airflow-providers-openlineage==2.7.3
apache-airflow-providers-oracle==4.2.0
apache-airflow-providers-postgres==6.5.1
apache-airflow-providers-redis==4.4.1
apache-airflow-providers-sendgrid==4.2.0
apache-airflow-providers-sftp==5.5.1
apache-airflow-providers-slack==9.6.1
apache-airflow-providers-smtp==2.3.1
apache-airflow-providers-snowflake==6.8.1
apache-airflow-providers-ssh==4.2.1
apache-airflow-providers-standard==1.9.1
Deployment
Official Apache Airflow Helm Chart
Deployment details
Official helm chart on AKS. Using Azure postgres flexible server with pgbouncer enabled (on Azure side, not in the chart).
Anything else?
No response
Are you willing to submit PR?
Code of Conduct
Apache Airflow version
3.1.6
If "Other Airflow 3 version" selected, which one?
No response
What happened?
When trying to remove (a lot of) dag runs or task_instances, I bumped into the issue that these deletes completed almost never (or very slow). Similar in
airflow db clean -t task_instances.Query plan in postgres shows me that the trigger to task_reschedule takes most of the time
What you think should happen instead?
after the above index, deletion succeeds a lot faster
How to reproduce
Upgraded from airflow 2.10 to 3.1.6. I wanted to clean up task_instances database afterwards
Operating System
Airflow docker image on AKS
Versions of Apache Airflow Providers
Deployment
Official Apache Airflow Helm Chart
Deployment details
Official helm chart on AKS. Using Azure postgres flexible server with pgbouncer enabled (on Azure side, not in the chart).
Anything else?
No response
Are you willing to submit PR?
Code of Conduct