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
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)
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@xxxxxxxxxx
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.
