pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp understand why DELETE is so slow
6+ messages / 4 participants
[nested] [flat]
* Help understand why DELETE is so slow
@ 2023-12-01 17:47 Ping Yao <ping@optigo.net>
0 siblings, 2 replies; 6+ messages in thread
From: Ping Yao @ 2023-12-01 17:47 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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@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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help understand why DELETE is so slow
@ 2023-12-01 17:50 Christophe Pettus <xof@thebuild.com>
parent: Ping Yao <ping@optigo.net>
1 sibling, 0 replies; 6+ messages in thread
From: Christophe Pettus @ 2023-12-01 17:50 UTC (permalink / raw)
To: Ping Yao <ping@optigo.net>; +Cc: pgsql-general@lists.postgresql.org
> 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 probably a question best directed to either the open-source Citus community, or Microsoft.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help understand why DELETE is so slow
@ 2023-12-04 21:40 Merlin Moncure <mmoncure@gmail.com>
parent: Ping Yao <ping@optigo.net>
1 sibling, 1 reply; 6+ messages in thread
From: Merlin Moncure @ 2023-12-04 21:40 UTC (permalink / raw)
To: Ping Yao <ping@optigo.net>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help understand why DELETE is so slow
@ 2023-12-04 21:42 Ping Yao <ping@optigo.net>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Ping Yao @ 2023-12-04 21:42 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help understand why DELETE is so slow
@ 2023-12-04 21:51 Merlin Moncure <mmoncure@gmail.com>
parent: Ping Yao <ping@optigo.net>
1 sibling, 0 replies; 6+ messages in thread
From: Merlin Moncure @ 2023-12-04 21:51 UTC (permalink / raw)
To: Ping Yao <ping@optigo.net>; +Cc: pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help understand why DELETE is so slow
@ 2023-12-05 00:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ping Yao <ping@optigo.net>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2023-12-05 00:43 UTC (permalink / raw)
To: Ping Yao <ping@optigo.net>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-12-05 00:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-01 17:47 Help understand why DELETE is so slow Ping Yao <ping@optigo.net>
2023-12-01 17:50 ` Christophe Pettus <xof@thebuild.com>
2023-12-04 21:40 ` Merlin Moncure <mmoncure@gmail.com>
2023-12-04 21:42 ` Ping Yao <ping@optigo.net>
2023-12-04 21:51 ` Merlin Moncure <mmoncure@gmail.com>
2023-12-05 00:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox