Обсуждение: 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
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@optigo.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.
> On Dec 1, 2023, at 09:47, Ping Yao <ping@optigo.net> wrote: > Can someone help me understand why my simple DELETE query is so slow to run? Based on the plan, you're running PostgreSQL with the Citus extension, and the delay is in Citus-related code. This is probablya question best directed to either the open-source Citus community, or Microsoft.
On Fri, Dec 1, 2023 at 11:48 AM Ping Yao <ping@optigo.net> wrote:
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?
Not familiar with citus to diagnose any citus issues. Having said that, can you confirm that all tables that have a foreign key reference to this table have an index on the field referencing?
For example, if you have a table customer, with a column 'customer_id', a table 'customer_employee' might refer to the customer table if it has 'customer_id REFERENCES customer', which would be very slow with deletes on customer. that may not be happening here with the citus stuff, hard to tell. Something to rule out though.
merlin
Hi Merlin,
Thank you for the comment. I think we do. What I found odd here is the Query Plan (with analyze) clearly shows the actual execution to be very quick, that's why I don't think it's an index problem. What do you think?
Ping
--
Pook-Ping Yao (He/Him)
Chief Technology Officer and co-Founder
Optigo Networks Inc.
+1-604-897-7464 | ping@optigo.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.
On Mon, Dec 4, 2023 at 1:40 PM Merlin Moncure <mmoncure@gmail.com> wrote:
On Fri, Dec 1, 2023 at 11:48 AM Ping Yao <ping@optigo.net> wrote: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?Not familiar with citus to diagnose any citus issues. Having said that, can you confirm that all tables that have a foreign key reference to this table have an index on the field referencing?For example, if you have a table customer, with a column 'customer_id', a table 'customer_employee' might refer to the customer table if it has 'customer_id REFERENCES customer', which would be very slow with deletes on customer. that may not be happening here with the citus stuff, hard to tell. Something to rule out though.merlin
On Mon, Dec 4, 2023 at 3:43 PM Ping Yao <ping@optigo.net> wrote:
Hi Merlin,Thank you for the comment. I think we do. What I found odd here is the Query Plan (with analyze) clearly shows the actual execution to be very quick, that's why I don't think it's an index problem. What do you think?
I'm not sure with citus. it isn't hard to verify; just check the referring tables and ensure any relating key is indexed. Perhaps the citus based plans don't surface that item -- hard to say. However, when you have complaints about slow deletes, this is always first thing to check. Second thing is slow triggers.
merlin
Ping Yao <ping@optigo.net> writes:
> Thank you for the comment. I think we do. What I found odd here is the
> Query Plan (with analyze) clearly shows the actual execution to be very
> quick, that's why I don't think it's an index problem. What do you think?
I wouldn't rule it out on that basis. Foreign key enforcement is done
by AFTER triggers, which are not part of the query plan. Having said
that, all modern versions of PG show trigger runtime as a separate
EXPLAIN output line, so slow FK checks aren't that hard to diagnose.
I agree with the other comments that this is likely a Citus-specific
issue, and that you came to the wrong place for expertise on that.
regards, tom lane