w0ut0 opened a new issue, #60895:
URL: https://github.com/apache/airflow/issues/60895
### 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?
- [ ] 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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
