Help understand why DELETE is so slow

Hello All.

First timer here, long time user though. Thank you in advance.

Can someone help me understand why my simple DELETE query is so slow to run?

*System:*
We are running Citus with 4 workers with 256 shards (default), with
replication using pg_autoctl.

*PostgreSQL Version:*
xxxx=# select version();
version
-------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.10 (Debian 14.10-1.pgdg110+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

*Query with explain:*
xxxx=*# explain (analyze,buffers,timing)
delete from organization where _id=:orgid returning *;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual
time=328233.766..328233.767 rows=1 loops=1)
Task Count: 1
Tuple data received from nodes: 760 bytes
Tasks Shown: All
-> Task
Tuple data received from node: 760 bytes
Node: host=xxxx_db_master_01 port=5432 dbname=xxxx
-> Delete on organization_102008 organization (cost=0.28..2.51
rows=1 width=6) (actual time=0.049..0.050 rows=1 loops=1)
Buffers: shared hit=6
-> Index Scan using organization__id_key_102008 on
organization_102008 organization (cost=0.28..2.51 rows=1 width=6) (actual
time=0.014..0.015 rows=1 loops=1)
Index Cond: (_id =
'f9903e13-383e-418c-a0e9-e39916b3eb1c'::uuid)
Buffers: shared hit=3
Planning Time: 0.049 ms
Trigger for constraint customer_org_uuid_fkey_102008:
time=0.106 calls=1
Trigger for constraint parent_uuid_102008: time=0.053 calls=1
Trigger for constraint parent_org_102009: time=0.045 calls=1
Execution Time: 0.273 ms
Buffers: shared hit=6
Planning Time: 0.053 ms
Execution Time: 328233.799 ms
(20 rows)

If I understand this correct, each step is quite quick, but for some
reason, the total execution time still took >5mins.

Thank you.

Ping

--

Pook-Ping Yao (He/Him)

Chief Technology Officer and co-Founder

Optigo Networks Inc.

+1-604-897-7464 | ping(at)optigo(dot)net

This email, including any files attached hereto, may contain privileged or
confidential information and is only for the intended addressee(s). If this
email was sent to you in error, this does not constitute a waiver by Optigo
Networks Inc. and we request that you kindly delete the email and notify
the sender. Unauthorized use of this email is prohibited.