From rjessil@yahoo.com Fri Jul 4 00:51:30 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 49B07650E85 for ; Thu, 3 Jul 2008 21:51:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 04395-06 for ; Thu, 3 Jul 2008 21:51:17 -0300 (ADT) X-Greylist: delayed 00:06:38.042267 by SQLgrey-1.7.6 Received: from web56412.mail.re3.yahoo.com (web56412.mail.re3.yahoo.com [216.252.111.91]) by postgresql.org (Postfix) with SMTP id 7E8A5650E8A for ; Thu, 3 Jul 2008 21:51:21 -0300 (ADT) Received: (qmail 18291 invoked by uid 60001); 4 Jul 2008 00:44:40 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID; b=F7tsuHTGuEpaC0t7/95alTMtCdH6u4KbjXT45GbNqdOcmrAZgJBKSjb0nFFpWcVjgqS1zTBq8VqFluIRPPrHrtiun/8PYnHJWwWxP7WacKyy9EQ1E16l3MCnXc90Z51w5K7a9QvlzHVaFk7gnjynCc4z/1x0OcFkqABJpWpbDUk=; Received: from [198.119.134.32] by web56412.mail.re3.yahoo.com via HTTP; Thu, 03 Jul 2008 17:44:40 PDT X-Mailer: YahooMailRC/1042.33 YahooMailWebService/0.7.199 Date: Thu, 3 Jul 2008 17:44:40 -0700 (PDT) From: Jessica Richard Subject: slow delete To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-250978347-1215132280=:18257" Message-ID: <357892.18257.qm@web56412.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/41 X-Sequence-Number: 30641 --0-250978347-1215132280=:18257 Content-Type: text/plain; charset=us-ascii I have a table with 29K rows total and I need to delete about 80K out of it. I have a b-tree index on column cola (varchar(255) ) for my where clause to use. my "select count(*) from test where cola = 'abc' runs very fast, but my actual "delete from test where cola = 'abc';" takes forever, never can finish and I haven't figured why.... In my explain output, what is that "Bitmap Heap Scan on table"? is it a table scan? is my index being used? How does delete work? to delete 80K rows that meet my condition, does Postgres find them all and delete them all together or one at a time? by the way, there is a foreign key on another table that references the primary key col0 on table test. Could some one help me out here? Thanks a lot, Jessica testdb=# select count(*) from test; count -------- 295793 --total 295,793 rows (1 row) Time: 155.079 ms testdb=# select count(*) from test where cola = 'abc'; count ------- 80998 - need to delete 80,988 rows (1 row) testdb=# explain delete from test where cola = 'abc'; QUERY PLAN ---------------------------------------------------------------------------------------------------- Bitmap Heap Scan on test (cost=2110.49..10491.57 rows=79766 width=6) Recheck Cond: ((cola)::text = 'abc'::text) -> Bitmap Index Scan on test_cola_idx (cost=0.00..2090.55 rows=79766 width=0) Index Cond: ((cola)::text = 'abc'::text) (4 rows) --0-250978347-1215132280=:18257 Content-Type: text/html; charset=us-ascii

I have a table with 29K rows total and I need to delete about 80K out of it.

I have a b-tree index on column cola (varchar(255) ) for my where clause to use.

my "select count(*) from test where cola = 'abc' runs very fast,

but my actual "delete from test where cola = 'abc';" takes forever, never can finish and I haven't figured why....

In my explain output, what is that "Bitmap Heap Scan on table"? is it a table scan? is my index being used?

How does delete work? to delete 80K rows that meet my condition, does Postgres find them all and delete them all together or one at a time?

by the way, there is a foreign key on another table that references the primary key col0 on table test.

Could some one help me out here?

Thanks a lot,
Jessica

testdb=# select count(*) from test;
count
--------
295793 --total 295,793 rows
(1 row)

Time: 155.079 ms

testdb=# select count(*) from test where cola = 'abc';
count
-------
80998 - need to delete 80,988 rows
(1 row)

testdb=# explain delete from test where cola = 'abc';
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=2110.49..10491.57 rows=79766 width=6)
   Recheck Cond: ((cola)::text = 'abc'::text)
   -> Bitmap Index Scan on test_cola_idx (cost=0.00..2090.55 rows=79766 width=0)
         Index Cond: ((cola)::text = 'abc'::text)
(4 rows)

--0-250978347-1215132280=:18257-- From craig@postnewspapers.com.au Fri Jul 4 05:16:42 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7CA8F650E9F for ; Fri, 4 Jul 2008 02:16:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 73248-09 for ; Fri, 4 Jul 2008 02:16:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from www.postnewspapers.com.au (www.postnewspapers.com.au [202.61.230.242]) by postgresql.org (Postfix) with ESMTP id 5505B650E99 for ; Fri, 4 Jul 2008 02:16:38 -0300 (ADT) Received: from mail.postnewspapers.com.au (202-89-185-120.static.dsl.amnet.net.au [202.89.185.120]) by www.postnewspapers.com.au (Postfix) with ESMTP id ED80C5C1BD; Fri, 4 Jul 2008 13:16:32 +0800 (WST) Received: from localhost (access [127.0.0.1]) by mail.postnewspapers.com.au (Postfix) with ESMTP id D79D01E0435; Fri, 4 Jul 2008 13:16:32 +0800 (WST) X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au Received: from mail.postnewspapers.com.au ([127.0.0.1]) by localhost (access.postnewspapers.com.au [127.0.0.1]) (amavisd-new, port 10024) with LMTP id Ey8xoNM9CHDo; Fri, 4 Jul 2008 13:16:32 +0800 (WST) Received: from [192.168.44.95] (203.161.97.213.static.amnet.net.au [203.161.97.213]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "Craig Ringer", Issuer "POST Certificate Authority" (verified OK)) by mail.postnewspapers.com.au (Postfix) with ESMTP id 15B121E0434; Fri, 4 Jul 2008 13:16:32 +0800 (WST) Message-ID: <486DB22F.10106@postnewspapers.com.au> Date: Fri, 04 Jul 2008 13:16:31 +0800 From: Craig Ringer User-Agent: Thunderbird 2.0.0.14 (X11/20080505) MIME-Version: 1.0 To: Jessica Richard Cc: pgsql-performance@postgresql.org Subject: Re: slow delete References: <357892.18257.qm@web56412.mail.re3.yahoo.com> In-Reply-To: <357892.18257.qm@web56412.mail.re3.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/43 X-Sequence-Number: 30643 Jessica Richard wrote: > I have a table with 29K rows total and I need to delete about 80K out of it. I assume you meant 290K or something. > I have a b-tree index on column cola (varchar(255) ) for my where clause > to use. > > my "select count(*) from test where cola = 'abc' runs very fast, > > but my actual "delete from test where cola = 'abc';" takes forever, > never can finish and I haven't figured why.... When you delete, the database server must: - Check all foreign keys referencing the data being deleted - Update all indexes on the data being deleted - and actually flag the tuples as deleted by your transaction All of which takes time. It's a much slower operation than a query that just has to find out how many tuples match the search criteria like your SELECT does. How many indexes do you have on the table you're deleting from? How many foreign key constraints are there to the table you're deleting from? If you find that it just takes too long, you could drop the indexes and foreign key constraints, do the delete, then recreate the indexes and foreign key constraints. This can sometimes be faster, depending on just what proportion of the table must be deleted. Additionally, remember to VACUUM ANALYZE the table after that sort of big change. AFAIK you shouldn't really have to if autovacuum is doing its job, but it's not a bad idea anyway. -- Craig Ringer From lists@peufeu.com Fri Jul 4 10:02:17 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1BB57650E7F for ; Fri, 4 Jul 2008 07:02:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 46605-01 for ; Fri, 4 Jul 2008 07:02:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 42.mail-out.ovh.net (42.mail-out.ovh.net [213.251.189.42]) by postgresql.org (Postfix) with SMTP id 2D1BF650EB0 for ; Fri, 4 Jul 2008 07:02:03 -0300 (ADT) Received: (qmail 26100 invoked by uid 503); 4 Jul 2008 10:01:51 -0000 Received: from gw2.ovh.net (HELO mail194.ha.ovh.net) (213.251.189.202) by 42.mail-out.ovh.net with SMTP; 4 Jul 2008 10:01:51 -0000 Received: from b0.ovh.net (HELO queue-out) (213.186.33.50) by b0.ovh.net with SMTP; 4 Jul 2008 10:02:01 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13.peufeu.com) (88.161.102.87) by ns0.ovh.net with SMTP; 4 Jul 2008 10:01:59 -0000 Date: Fri, 04 Jul 2008 12:11:17 +0200 To: "Jessica Richard" , pgsql-performance@postgresql.org Subject: Re: slow delete From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: <357892.18257.qm@web56412.mail.re3.yahoo.com> Content-Transfer-Encoding: 7bit Message-ID: In-Reply-To: <357892.18257.qm@web56412.mail.re3.yahoo.com> User-Agent: Opera Mail/9.24 (Linux) X-Ovh-Tracer-Id: 17660584465451845290 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|H 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.627 tagged_above=0 required=5 tests=AWL=0.627 X-Spam-Level: X-Archive-Number: 200807/45 X-Sequence-Number: 30645 > by the way, there is a foreign key on another table that references the > primary key col0 on table test. Is there an index on the referencing field in the other table ? Postgres must find the rows referencing the deleted rows, so if you forget to index the referencing column, this can take forever. From rjessil@yahoo.com Fri Jul 4 12:30:16 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E0998650E96 for ; Fri, 4 Jul 2008 09:30:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 81437-01 for ; Fri, 4 Jul 2008 09:30:03 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web56401.mail.re3.yahoo.com (web56401.mail.re3.yahoo.com [216.252.111.80]) by postgresql.org (Postfix) with SMTP id 2659A650E8F for ; Fri, 4 Jul 2008 09:30:08 -0300 (ADT) Received: (qmail 6278 invoked by uid 60001); 4 Jul 2008 12:30:06 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type:Message-ID; b=V85WK+pV7osLv9ClwTtpiFU6JwkS+WoA9jvKHAslC5Jwb2Wmbq/p3UFmGXoNZCzTjwyK0gmxzX2R5ycMdMwJEA8Uj8jqYq6FM03zjW3j2zIIPtOxQuglceI/bx0hJ8QzRLVjcRdswtz4RLYnQjFYGxeC7rIPUhovQfVYEQOgJK4=; Received: from [98.166.112.215] by web56401.mail.re3.yahoo.com via HTTP; Fri, 04 Jul 2008 05:30:06 PDT X-Mailer: YahooMailRC/1042.33 YahooMailWebService/0.7.199 Date: Fri, 4 Jul 2008 05:30:06 -0700 (PDT) From: Jessica Richard Subject: Re: slow delete To: Craig Ringer Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1212549373-1215174606=:4752" Message-ID: <498125.4752.qm@web56401.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/46 X-Sequence-Number: 30646 --0-1212549373-1215174606=:4752 Content-Type: text/plain; charset=us-ascii Thanks so much for your help. I can select the 80K data out of 29K rows very fast, but we I delete them, it always just hangs there(> 4 hours without finishing), not deleting anything at all. Finally, I select pky_col where cola = 'abc', and redirect it to an out put file with a list of pky_col numbers, then put them in to a script with 80k lines of individual delete, then it ran fine, slow but actually doing the delete work: delete from test where pk_col = n1; delete from test where pk_col = n2; ... My next question is: what is the difference between "select" and "delete"? There is another table that has one foreign key to reference the test (parent) table that I am deleting from and this foreign key does not have an index on it (a 330K row table). Deleting one row at a time is fine: delete from test where pk_col = n1; but deleting the big chunk all together (with 80K rows to delete) always hangs: delete from test where cola = 'abc'; I am wondering if I don't have enough memory to hold and carry on the 80k-row delete..... but how come I can select those 80k-row very fast? what is the difference between select and delete? Maybe the foreign key without an index does play a big role here, a 330K-row table references a 29K-row table will get a lot of table scan on the foreign table to check if each row can be deleted from the parent table... Maybe select from the parent table does not have to check the child table? Thank you for pointing out about dropping the constraint first, I can imagine that it will be a lot faster. But what if it is a memory issue that prevent me from deleting the 80K-row all at once, where do I check about the memory issue(buffer pool) how to tune it on the memory side? Thanks a lot, Jessica ----- Original Message ---- From: Craig Ringer To: Jessica Richard Cc: pgsql-performance@postgresql.org Sent: Friday, July 4, 2008 1:16:31 AM Subject: Re: [PERFORM] slow delete Jessica Richard wrote: > I have a table with 29K rows total and I need to delete about 80K out of it. I assume you meant 290K or something. > I have a b-tree index on column cola (varchar(255) ) for my where clause > to use. > > my "select count(*) from test where cola = 'abc' runs very fast, > > but my actual "delete from test where cola = 'abc';" takes forever, > never can finish and I haven't figured why.... When you delete, the database server must: - Check all foreign keys referencing the data being deleted - Update all indexes on the data being deleted - and actually flag the tuples as deleted by your transaction All of which takes time. It's a much slower operation than a query that just has to find out how many tuples match the search criteria like your SELECT does. How many indexes do you have on the table you're deleting from? How many foreign key constraints are there to the table you're deleting from? If you find that it just takes too long, you could drop the indexes and foreign key constraints, do the delete, then recreate the indexes and foreign key constraints. This can sometimes be faster, depending on just what proportion of the table must be deleted. Additionally, remember to VACUUM ANALYZE the table after that sort of big change. AFAIK you shouldn't really have to if autovacuum is doing its job, but it's not a bad idea anyway. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --0-1212549373-1215174606=:4752 Content-Type: text/html; charset=us-ascii

Thanks so much for your help.

I can select the 80K data out of 29K rows very fast, but we I delete them, it always just hangs there(> 4 hours without finishing), not deleting anything at all. Finally, I select pky_col where cola = 'abc', and redirect it to an out put file with a list of pky_col numbers, then put them in to a script with 80k lines of individual delete, then it ran fine, slow but actually doing the delete work:

delete from test where pk_col = n1;
delete from test where pk_col = n2;
...

My next question is: what is the difference between "select" and "delete"? There is another table that has one foreign key to reference the test (parent) table that I am deleting from and this foreign key does not have an index on it (a 330K row table).

Deleting one row at a time is fine: delete from test where pk_col = n1;

but deleting the big chunk all together (with 80K rows to delete) always hangs: delete from test where cola = 'abc';

I am wondering if I don't have enough memory to hold and carry on the 80k-row delete.....
but how come I can select those 80k-row very fast? what is the difference between select and delete?

Maybe the foreign key without an index does play a big role here, a 330K-row table references a 29K-row table will get a lot of table scan on the foreign table to check if each row can be deleted from the parent table... Maybe select from the parent table does not have to check the child table?

Thank you for pointing out about dropping the constraint first, I can imagine that it will be a lot faster.

But what if it is a memory issue that prevent me from deleting the 80K-row all at once, where do I check about the memory issue(buffer pool) how to tune it on the memory side?

Thanks a lot,
Jessica

----- Original Message ----
From: Craig Ringer <craig@postnewspapers.com.au>
To: Jessica Richard <rjessil@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Friday, July 4, 2008 1:16:31 AM
Subject: Re: [PERFORM] slow delete

Jessica Richard wrote:
> I have a table with 29K rows total and I need to delete about 80K out of it.

I assume you meant 290K or something.

> I have a b-tree index on column cola (varchar(255) ) for my where clause
> to use.
>
> my "select count(*) from test where cola = 'abc' runs very fast,
>
> but my actual "delete from test where cola = 'abc';" takes forever,
> never can finish and I haven't figured why....

When you delete, the database server must:

- Check all foreign keys referencing the data being deleted
- Update all indexes on the data being deleted
- and actually flag the tuples as deleted by your transaction

All of which takes time. It's a much slower operation than a query that
just has to find out how many tuples match the search criteria like your
SELECT does.

How many indexes do you have on the table you're deleting from? How many
foreign key constraints are there to the table you're deleting from?

If you find that it just takes too long, you could drop the indexes and
foreign key constraints, do the delete, then recreate the indexes and
foreign key constraints. This can sometimes be faster, depending on just
what proportion of the table must be deleted.

Additionally, remember to VACUUM ANALYZE the table after that sort of
big change. AFAIK you shouldn't really have to if autovacuum is doing
its job, but it's not a bad idea anyway.

--
Craig Ringer

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--0-1212549373-1215174606=:4752-- From tv@fuzzy.cz Fri Jul 4 13:00:53 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 517FC650E8F for ; Fri, 4 Jul 2008 10:00:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 07986-01 for ; Fri, 4 Jul 2008 10:00:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from anastacia.gransy.com (anastacia.gransy.com [89.187.130.100]) by postgresql.org (Postfix) with ESMTP id E3795650EA8 for ; Fri, 4 Jul 2008 10:00:49 -0300 (ADT) Received: from sq.gransy.com (localhost [127.0.0.1]) by anastacia.gransy.com (Postfix) with ESMTP id EABB5282C66; Fri, 4 Jul 2008 15:00:49 +0200 (CEST) Received: from 217.77.161.17 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Fri, 4 Jul 2008 15:00:49 +0200 (CEST) Message-ID: <31248.217.77.161.17.1215176449.squirrel@sq.gransy.com> In-Reply-To: <498125.4752.qm@web56401.mail.re3.yahoo.com> References: <498125.4752.qm@web56401.mail.re3.yahoo.com> Date: Fri, 4 Jul 2008 15:00:49 +0200 (CEST) Subject: Re: slow delete From: tv@fuzzy.cz To: "Jessica Richard" Cc: "Craig Ringer" , pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.10a MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-2 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/47 X-Sequence-Number: 30647 > My next question is: what is the difference between "select" and "delete"? > There is another table that has one foreign key to reference the test > (parent) table that I am deleting from and this foreign key does not have > an index on it (a 330K row table). The difference is that with SELECT you're not performing any modifications to the data, while with DELETE you are. That means that with DELETE you may have a lot of overhead due to FK checks etc. Someone already pointed out that if you reference a table A from table B (using a foreign key), then you have to check FK in case of DELETE, and that may knock the server down if the table B is huge and does not have an index on the FK column. > Deleting one row at a time is fine: delete from test where pk_col = n1; > > but deleting the big chunk all together (with 80K rows to delete) always > hangs: delete from test where cola = 'abc'; > > I am wondering if I don't have enough memory to hold and carry on the > 80k-row delete..... > but how come I can select those 80k-row very fast? what is the difference > between select and delete? > > Maybe the foreign key without an index does play a big role here, a > 330K-row table references a 29K-row table will get a lot of table scan on > the foreign table to check if each row can be deleted from the parent > table... Maybe select from the parent table does not have to check the > child table? Yes, and PFC already pointed this out. Tomas From ahodgson@simkin.ca Fri Jul 4 15:48:28 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E98A4650EAA for ; Fri, 4 Jul 2008 12:48:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 75390-06 for ; Fri, 4 Jul 2008 12:48:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from skynet.simkin.ca (skynet.simkin.ca [72.51.27.117]) by postgresql.org (Postfix) with ESMTP id 0D98B650E9D for ; Fri, 4 Jul 2008 12:48:23 -0300 (ADT) Received: from charon.medialogik.com (charon.medialogik.com [72.51.27.114]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by skynet.simkin.ca (Postfix) with ESMTP id 25CA54A9E for ; Fri, 4 Jul 2008 08:48:20 -0700 (PDT) From: Alan Hodgson Organization: Simkin Network Consulting To: pgsql-performance@postgresql.org Subject: Re: slow delete Date: Fri, 4 Jul 2008 08:48:19 -0700 User-Agent: KMail/1.9.9 References: <498125.4752.qm@web56401.mail.re3.yahoo.com> <31248.217.77.161.17.1215176449.squirrel@sq.gransy.com> In-Reply-To: <31248.217.77.161.17.1215176449.squirrel@sq.gransy.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200807040848.19462@hal.medialogik.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/48 X-Sequence-Number: 30648 On Friday 04 July 2008, tv@fuzzy.cz wrote: > > My next question is: what is the difference between "select" and > > "delete"? There is another table that has one foreign key to reference > > the test (parent) table that I am deleting from and this foreign key > > does not have an index on it (a 330K row table). > Yeah you need to fix that. You're doing 80,000 sequential scans of that table to do your delete. That's a whole lot of memory access ... I don't let people here create foreign key relationships without matching indexes - they always cause problems otherwise. -- Alan From nagylzs@gmail.com Tue Aug 15 20:23:26 2023 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qW0Zz-001qja-3U for pgsql-performance@arkaria.postgresql.org; Tue, 15 Aug 2023 20:23:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qW0Zw-00HYJ7-IT for pgsql-performance@arkaria.postgresql.org; Tue, 15 Aug 2023 20:23:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qW0Zv-00HYIx-Pg for pgsql-performance@lists.postgresql.org; Tue, 15 Aug 2023 20:23:48 +0000 Received: from mail-oa1-x2d.google.com ([2001:4860:4864:20::2d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qW0Zn-000Jp0-Bu for pgsql-performance@lists.postgresql.org; Tue, 15 Aug 2023 20:23:46 +0000 Received: by mail-oa1-x2d.google.com with SMTP id 586e51a60fabf-1c4dda61eb0so1875863fac.3 for ; Tue, 15 Aug 2023 13:23:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692131018; x=1692735818; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=nEHYDv6m9dF9QoP3WWAl6FkDL3Vc7nSnHCaI5BgDTpw=; b=KJH+gcO+Jpulrtpx503SLqoekmTc5yfbA+3sAqsXoiz/ncy4vaV7KYbZA5NubXmRSQ 08+APy8VqRAWo2lOTYFSiVOws5Z/GjzXiyr34OMM2zhrvuJxE8Yp1d0kz70AEikPV51V QYpheZr5n2TeoaYs7/GsUPEJ0KKyQD0kxCnd1Bd2bcDsdMtcXwWig9Ghio05XLKlRt2p C6TFuzPM0LiUUpRzGGZy8vi3oz5r9tRcHmdC2VgIq9zP1/IumUWnjrALr6o6vqhyrN2+ CpiemxE7A+yokWya2vo62Bfi0jlWx+eqvkHQ+r0QGtqzOwHu54POSSzEjmr95rKNnZwT ON8g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692131018; x=1692735818; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=nEHYDv6m9dF9QoP3WWAl6FkDL3Vc7nSnHCaI5BgDTpw=; b=SIjc/s6uV9+REXb6q2I+MqqiT2oY0rzGiBniR9R0+pFffG5qJYanbWEuvro5YlQHHO b+QLPHG4s+7a0bZsqHVkejF+OWF57A/rI6Ektvlgka0mww471M2zbnb5uCb8isKMkz8P 3ukDvxQ2ihsQoKjVCm+6FRr4iQ0OStxYuey9RmhLw45cQc/xAEg9T/bbdpoqrWQ5gXEU c4RvHpm1EscvYsdHWgghAH4pu5V14nb4P5b2vc6J1NIRg5ib6JsIhzLQTPMJUr3OQU38 pLOQbzmN0iBxJsk8vzWQ35fBsK+h70oBBzj7C5u95o1W+V9/xtfuEVn1zJrxNnbJrCXa +Y3w== X-Gm-Message-State: AOJu0YzEvMoJQxNtOv6fc6sdNQr04yET4pv6oDrdN7+W5aKZ5ex2pQjv zNkkRCLkuGCefazm8gvhCq57O+d/xzFNP1HEwTNckOG+y3oHKw== X-Google-Smtp-Source: AGHT+IE8oEOBszfkzbbDCvy3ckeJ35lZAsDy82GpQSA0sh4AfaHehnvMcf1lB4LJW1FmoGAzIPA/dta06x9pStmZCAg= X-Received: by 2002:a05:6870:6114:b0:1bc:1ad8:2b7b with SMTP id s20-20020a056870611400b001bc1ad82b7bmr13635714oae.58.1692131017898; Tue, 15 Aug 2023 13:23:37 -0700 (PDT) MIME-Version: 1.0 From: Les Date: Tue, 15 Aug 2023 22:23:26 +0200 Message-ID: Subject: slow delete To: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000003c43490602fbf487" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003c43490602fbf487 Content-Type: text/plain; charset="UTF-8" I have created a table called _td with about 43 000 rows. I have tried to use this as a primary key id list to delete records from my product.product_file table, but I could not do it. It uses 100% of one CPU and it takes forever. Then I changed the query to delete 100 records only, and measure the speed: EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) delete from product.product_file where id in ( select pf2_id from _td limit 100 ) It still takes 11 seconds. It means 110 msec / record, and that is unacceptable. I'm going to post the whole query plan at the end of this email, but I would like to highlight the "Triggers" part: "Triggers": [ { "Trigger Name": "RI_ConstraintTrigger_a_26535", "Constraint Name": "fk_pfft_product", "Relation": "product_file", "Time": 4.600, "Calls": 90 }, { "Trigger Name": "RI_ConstraintTrigger_a_26837", "Constraint Name": "fk_product_file_src", "Relation": "product_file", "Time": 5.795, "Calls": 90 }, { "Trigger Name": "RI_ConstraintTrigger_a_75463", "Constraint Name": "fk_pfq_src_product_file", "Relation": "product_file", "Time": 11179.429, "Calls": 90 }, { "Trigger Name": "_trg_002_aiu_audit_row", "Relation": "product_file", "Time": 49.410, "Calls": 90 } ] It seems that two foreign key constraints use 10.395 seconds out of the total 11.24 seconds. But I don't see why it takes that much? The product.product_file table has 477 000 rows: CREATE TABLE product.product_file ( id uuid NOT NULL, c_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, c_uid uuid NULL, c_sid uuid NULL, m_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, m_uid uuid NULL, m_sid uuid NULL, product_id uuid NOT NULL, product_file_type_id uuid NOT NULL, file_id uuid NOT NULL, product_file_status_id uuid NOT NULL, dl_url text NULL, src_product_file_id uuid NULL, CONSTRAINT product_file_pkey PRIMARY KEY (id), CONSTRAINT fk_pf_file FOREIGN KEY (file_id) REFERENCES media.file(id), CONSTRAINT fk_pf_file_type FOREIGN KEY (product_file_type_id) REFERENCES product.product_file_type(id), CONSTRAINT fk_pf_product FOREIGN KEY (product_id) REFERENCES product.product(id) ON DELETE CASCADE, CONSTRAINT fk_product_file_src FOREIGN KEY (src_product_file_id) REFERENCES product.product_file(id), CONSTRAINT fk_product_file_status FOREIGN KEY (product_file_status_id) REFERENCES product.product_file_status(id) ); CREATE INDEX idx_product_file_dl_url ON product.product_file USING btree (dl_url) INCLUDE (product_id) WHERE (dl_url IS NOT NULL); CREATE INDEX idx_product_file_file_product ON product.product_file USING btree (file_id, product_id); CREATE INDEX idx_product_file_product_file ON product.product_file USING btree (product_id, file_id); CREATE INDEX idx_product_file_src ON product.product_file USING btree (src_product_file_id) WHERE (src_product_file_id IS NOT NULL); The one with fk_pfft_product looks like this, it has about 5000 records in it: CREATE TABLE product.product_file_tag ( id uuid NOT NULL, c_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, c_uid uuid NULL, c_sid uuid NULL, m_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, m_uid uuid NULL, m_sid uuid NULL, product_file_id uuid NOT NULL, file_tag_id uuid NOT NULL, CONSTRAINT product_file_tag_pkey PRIMARY KEY (id), CONSTRAINT fk_pfft_file_tag FOREIGN KEY (file_tag_id) REFERENCES product.file_tag(id) ON DELETE CASCADE DEFERRABLE, CONSTRAINT fk_pfft_product FOREIGN KEY (product_file_id) REFERENCES product.product_file(id) ON DELETE CASCADE DEFERRABLE ); CREATE UNIQUE INDEX uidx_product_file_file_tag ON product.product_file_tag USING btree (product_file_id, file_tag_id); The other constraint has zero actual references, this returns zero: select count(*) from product.product_file where src_product_file_id in ( select pf2_id from _td ); -- 0 I was trying to figure out how a foreign key constraint with zero actual references can cost 100 msec / record, but I failed. Can somebody please explain what is wrong here? The plan is also visualized here: http://tatiyants.com/pev/#/plans/plan_1692129126258 [ { "Plan": { "Node Type": "ModifyTable", "Operation": "Delete", "Parallel Aware": false, "Async Capable": false, "Relation Name": "product_file", "Schema": "product", "Alias": "product_file", "Startup Cost": 4.21, "Total Cost": 840.79, "Plan Rows": 0, "Plan Width": 0, "Actual Startup Time": 0.567, "Actual Total Time": 0.568, "Actual Rows": 0, "Actual Loops": 1, "Shared Hit Blocks": 582, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 10, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "Plans": [ { "Node Type": "Nested Loop", "Parent Relationship": "Outer", "Parallel Aware": false, "Async Capable": false, "Join Type": "Inner", "Startup Cost": 4.21, "Total Cost": 840.79, "Plan Rows": 100, "Plan Width": 46, "Actual Startup Time": 0.161, "Actual Total Time": 0.451, "Actual Rows": 90, "Actual Loops": 1, "Output": ["product_file.ctid", "\"ANY_subquery\".*"], "Inner Unique": true, "Shared Hit Blocks": 402, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 10, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "Plans": [ { "Node Type": "Aggregate", "Strategy": "Hashed", "Partial Mode": "Simple", "Parent Relationship": "Outer", "Parallel Aware": false, "Async Capable": false, "Startup Cost": 3.79, "Total Cost": 4.79, "Plan Rows": 100, "Plan Width": 56, "Actual Startup Time": 0.118, "Actual Total Time": 0.136, "Actual Rows": 100, "Actual Loops": 1, "Output": ["\"ANY_subquery\".*", "\"ANY_subquery\".pf2_id"], "Group Key": ["\"ANY_subquery\".pf2_id"], "Planned Partitions": 0, "HashAgg Batches": 1, "Peak Memory Usage": 32, "Disk Usage": 0, "Shared Hit Blocks": 2, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "Plans": [ { "Node Type": "Subquery Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Async Capable": false, "Alias": "ANY_subquery", "Startup Cost": 0.00, "Total Cost": 3.54, "Plan Rows": 100, "Plan Width": 56, "Actual Startup Time": 0.030, "Actual Total Time": 0.083, "Actual Rows": 100, "Actual Loops": 1, "Output": ["\"ANY_subquery\".*", "\"ANY_subquery\".pf2_id"], "Shared Hit Blocks": 2, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "Plans": [ { "Node Type": "Limit", "Parent Relationship": "Subquery", "Parallel Aware": false, "Async Capable": false, "Startup Cost": 0.00, "Total Cost": 2.54, "Plan Rows": 100, "Plan Width": 16, "Actual Startup Time": 0.024, "Actual Total Time": 0.053, "Actual Rows": 100, "Actual Loops": 1, "Output": ["_td.pf2_id"], "Shared Hit Blocks": 2, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "Plans": [ { "Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Async Capable": false, "Relation Name": "_td", "Schema": "public", "Alias": "_td", "Startup Cost": 0.00, "Total Cost": 1100.07, "Plan Rows": 43307, "Plan Width": 16, "Actual Startup Time": 0.023, "Actual Total Time": 0.042, "Actual Rows": 100, "Actual Loops": 1, "Output": ["_td.pf2_id"], "Shared Hit Blocks": 2, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0 } ] } ] } ] }, { "Node Type": "Index Scan", "Parent Relationship": "Inner", "Parallel Aware": false, "Async Capable": false, "Scan Direction": "Forward", "Index Name": "product_file_pkey", "Relation Name": "product_file", "Schema": "product", "Alias": "product_file", "Startup Cost": 0.42, "Total Cost": 8.36, "Plan Rows": 1, "Plan Width": 22, "Actual Startup Time": 0.003, "Actual Total Time": 0.003, "Actual Rows": 1, "Actual Loops": 100, "Output": ["product_file.ctid", "product_file.id"], "Index Cond": "(product_file.id = \"ANY_subquery\".pf2_id)", "Rows Removed by Index Recheck": 0, "Shared Hit Blocks": 400, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 10, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0 } ] } ] }, "Planning": { "Shared Hit Blocks": 0, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0 }, "Planning Time": 0.249, "Triggers": [ { "Trigger Name": "RI_ConstraintTrigger_a_26535", "Constraint Name": "fk_pfft_product", "Relation": "product_file", "Time": 4.600, "Calls": 90 }, { "Trigger Name": "RI_ConstraintTrigger_a_26837", "Constraint Name": "fk_product_file_src", "Relation": "product_file", "Time": 5.795, "Calls": 90 }, { "Trigger Name": "RI_ConstraintTrigger_a_75463", "Constraint Name": "fk_pfq_src_product_file", "Relation": "product_file", "Time": 11179.429, "Calls": 90 }, { "Trigger Name": "_trg_002_aiu_audit_row", "Relation": "product_file", "Time": 49.410, "Calls": 90 } ], "Execution Time": 11240.265 } ] --0000000000003c43490602fbf487 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

I have created a table called _td with about 43 000 rows. = I have tried to use this as a primary key id list to delete records from my= product.product_file table, but I could not do it. It uses 100% of one CPU= and it takes forever. Then I changed the query to delete 100 records only,= and measure the speed:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
delete from product.product_file where id in (
	select pf2_id from _td limit 100
)

It still takes 11 seconds. It means=C2=A0 110 msec / re= cord, and that is unacceptable.=C2=A0=C2=A0

I'= m going to post the whole query plan at the end of this email, but I would = like to highlight the "Triggers" part:

<= br>

"Triggers": [

{

= "Trigger Name": "RI_ConstraintTrigger_a_26535",

&= quot;Constraint Name": "= ;fk_pfft_product",

"Relation": "product_file",

= "Time": 4.600= ,

"Calls": 90<= /p>

{

<= p style=3D"margin:0px"> "Tri= gger Name": "RI_Constra= intTrigger_a_26837",

"Constraint Name": "fk_product_file_src",

"Relation&q= uot;: "product_file",

"Time": 5.795,

= "Calls": 90

{

"Trigger Name": "RI_ConstraintTrigger_a_75463",

"Constra= int Name": "fk_pfq_src_= product_file",

"Relation": "product_file",

= "Time": 11179.4= 29,

"Calls": 90

{

"T= rigger Name": "_trg_002= _aiu_audit_row",

"Relation": "product_file",

= "Time": 49.410<= /span>,

"Calls": 90

}

]

It seems that two foreign key constraints = use 10.395 seconds out of the total 11.24 seconds. But I don't see why = it takes that much?

The product.product_file table= has 477 000 rows:

CREATE TABLE product.product_file (
	i=
d uuid NOT NULL,
	c_tim time=
stamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
	c_uid uuid NULL,
	c_sid uuid NULL,
	=
m_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
	m_uid uuid NULL,
	m_sid uuid NULL,
	product_id uuid=
 NOT NULL,
	product_file_type_id uuid NOT NU=
LL,
	file_id uuid NOT NULL,
	product_file_stat=
us_id uuid NOT <=
span style=3D"color:rgb(128,0,0);font-weight:bold">NULL,
	dl_url text NULL,
	src_product_file_id uuid NULL,
	<=
span style=3D"color:rgb(128,0,0);font-weight:bold">CONSTRAINT produc=
t_file_pkey PRIMARY KEY (id),
	CONSTRAINT fk_pf_file FOREIGN KEY (file_id) R=
EFERENCES media.file(id),
	CONSTRAINT fk_pf_file_type FOREIGN KEY (product_file_type_id) =
REFERENCES produ=
ct.product_file_type(id),
	CONSTRAINT fk_pf_product FOREIGN KEY (product_id) REFERENCES product.product(id) ON DELETE CASCADE,
	CONSTRAINT fk_produc=
t_file_src FOREIGN KEY (src_prod=
uct_file_id) REFERENCES=
 product.product_file(id),
	CONSTRAINT fk_product_file_=
status FOREIGN <=
span style=3D"color:rgb(128,0,0);font-weight:bold">KEY (product_file=
_status_id) REFERENCES<=
/span> product.product_file_status(id)
);
CREATE INDEX idx_product_file_dl_url ON product.product_fil=
e USING btree (d=
l_url) INCLUDE (=
product_id) WHERE (dl_url IS NOT NULL);
CREATE INDEX idx_product_file_file_product ON product.product_file USING btree (file_id, produ=
ct_id);
CREATE INDEX idx_product_file_=
product_file ON =
product.product_file US=
ING btree (product_id, file_id);<=
/span>
CREATE INDEX idx_product_file_src ON product.product_file USING btree (src_product_file_id) WHERE (src_product_file_id =
IS NOT NULL);

<= div>The one with fk_pfft_product looks like this, it has about 5000 records= in it:

CREATE T= ABLE product.product_file_tag (

id uuid = NOT NULL,

c_tim timestampt= z NOT NULL DEFAULT CURRENT_TIMESTAMP,

c_uid uuid NUL= L,

c_sid uuid NULL,

m_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,

m_uid = uuid NULL,

m_sid uuid NULL,

product_file_id uuid NOT NULL,

file_tag_id uuid NO= T NULL,

CONSTRAINT product_file_tag_pkey PRIMARY KEY (id),

CONSTRAINT fk_pfft_file_tag FOREIGN KEY (file_tag_id) REFERENCES product.file_t= ag(id) ON DELETE CASCADE DEFERRABLE,

= CONSTRAINT fk_p= fft_product FOREIGN KEY (product= _file_id) REFERENCES product.product_file(id) ON DELETE= CASCADE = DEFERRABLE

);

CREA= TE UNIQUE= INDEX uidx_prod= uct_file_file_tag ON product.product_file_tag USING btree (product_file_id, file_tag_id);

<= div>

The other constraint has zero actual references, this re= turns zero:

select co= unt(*) from product.product_file where src_product_file_id in (

select pf2_id from _td

); -- 0= =C2=A0

I was tryi= ng to figure out=C2=A0how a foreign key constraint with zero actual referen= ces can cost 100 msec / record, but I failed.

Can = somebody please explain what is wrong here?

The pl= an is also visualized here:=C2=A0http://tatiyants.com/pev/#/plans/plan_1692129126258

[

{

"Plan": = {

"= ;Node Type": "ModifyTab= le",

"Operation": = "Delete",

"Parallel Aware": false,

"Async Capable": false,

"Relation Name": "product_file",

"Schema": "product",

"Alias": "product_file",

"Star= tup Cost": 4.21,

"Total Cost": 840.79,

&= quot;Plan Rows": 0,

"Pl= an Width": 0,

"Actual S= tartup Time": 0.567,

= "Actual Total Time": 0.568,

"Actual Rows": 0= ,

"Actual Loops": 1,

&q= uot;Shared Hit Blocks": 582<= /span>,

"Shared Read Blocks": = 0,

"Shared Dirtied Blocks": 10,

"Shared Written Blocks": 0,

"Local Hit Blocks": 0,

"Local Read Blocks": 0,

"Local Dirtied Blocks": 0,

"Local Written Blocks": 0,

"Temp Read Blocks": 0,

"Temp Written Blocks": 0,

"Plans": [

{

"Node Type": "Nested Loop",

"Parent Relationship": "Outer",

"Parallel Awar= e": false,

"Async C= apable": false,

"Jo= in Type": "Inner"<= /span>,

"Startup Cost": 4<= /span>.21,

"Total Cost": 840.79,

"Plan Rows": 100,

"Plan Width": 46,

"Actual Startup Time": 0.161,

&quo= t;Actual Total Time": 0.451,

"Actual Rows": 90,

"Actual Loops": 1,

"Output": [= "product_file.ctid", "\"ANY_subquery\".*"],

"Inner Unique": tr= ue,

"Shared Hit Blocks": 402,

"Shared Read Blocks": 0,

"Shared Dirtied Blocks": 10,

"Shared Written Blocks": <= span style=3D"color:rgb(0,0,255)">0,

= "Local Hit Blocks": 0,

= "Local Read Blocks"<= /span>: 0,

"Local Dirtied Block= s": 0,

"Local Writt= en Blocks": 0,

"Tem= p Read Blocks": 0,

"= ;Temp Written Blocks": 0,

"Plans": [

{

<= p style=3D"margin:0px"> &qu= ot;Node Type": "Aggrega= te",

"Strategy": "Hashed",

= "Partial Mode": "Simple",

"Parent Relati= onship": "Outer",

"Parallel Aware": false,

"Async Capable": false,

"Startup Cost": 3.79,<= /p>

"Total Cost": 4= .79,

= "Plan Rows": 100,

"Plan Width": 56,

"Actual Startup = Time": 0.118,

= "Actual Total Time": = 0.136,

"Actual Rows": 100,

"Actual Loops": 1,

"Output": ["\"ANY_subquery\".*", "\"ANY_subquery\".pf2_id"],

"Group Key": [&qu= ot;\"ANY_subquery\".pf2_id"],

"Planned Partitio= ns": 0,

"Hash= Agg Batches": 1,

<= p style=3D"margin:0px"> &qu= ot;Peak Memory Usage": 32,

"Disk Usage": 0<= /span>,

"Shared Hit Blocks": 2,

"Shared Read Blocks": 0,

"Shared Dirtied Blocks": 0,

= "Shared Written Blocks"<= /span>: 0,

"Local Hit Block= s": 0,

"Local R= ead Blocks": 0,

&quo= t;Local Dirtied Blocks": 0,

"Local Written Blocks": 0,

"Temp Read Blocks": 0,

"Temp Written Blocks": 0,

= "Plans": [

{

= "Node Type": "Subquery Scan",

&quo= t;Parent Relationship": &quo= t;Outer",

"Parallel Aware": false,

= "Async Capable"= : false,

"Alias": "ANY_subquery",<= /p>

"Startup Cost": 0<= /span>.00,

"Total Cost&= quot;: 3.54,

<= span style=3D"color:rgb(0,128,0)">"Plan Rows": 100,

= "Plan Width": 56,

= "Actual Startup Time&q= uot;: 0.030,

<= span style=3D"color:rgb(0,128,0)">"Actual Total Time": 0.083,

"Actual Rows": 100,

"Actual Loops": 1,

= "Output": ["\"ANY_subquery\".*", "\"ANY_subquery\".pf2_id&qu= ot;],

"Shared Hit Blocks": 2,

"Shared Read Blocks": 0,

= "Shared Dirtied Blocks&qu= ot;: 0,

"Shared = Written Blocks": 0,

"Local Hit Blocks": 0,

"Local Read Blocks": 0,

"Local Dirtied Blocks": 0,

= "Local Written Blocks&quo= t;: 0,

"Temp Rea= d Blocks": 0,

&q= uot;Temp Written Blocks": 0<= /span>,

"Plans": [

= {

"Node Type": "Limit",

= "Parent Relationship&= quot;: "Subquery",

"Parallel Aware": false,

"Async Capable": false,

= "Startup Cost": 0.00,

"Total Cost": 2.54,<= /p>

"Plan Rows": 1= 00,

"Plan Width": 16,

"Actual Startup Time": 0.= 024,

"Actual Total Time": 0.053,<= /p>

"Actual Rows": 100,

"Actual Loops": 1,

= "Output": ["_td.pf2_id"],

"Shar= ed Hit Blocks": 2,

"Shared Read Blocks": 0,

"Shared Dirtied Blocks": 0,

= "Shared Written Blocks&q= uot;: 0,

"Lo= cal Hit Blocks": 0,

"Local Read Blocks": 0,

"Local Dirtied Blocks": 0,

= "Local Written Blocks&quo= t;: 0,

"Temp= Read Blocks": 0,

"Temp Written Blocks": 0,

"Plans": [

{

= "Node Type": <= span style=3D"color:rgb(0,128,0)">"Seq Scan",

"Parent Relationship": "Outer",

= "Parallel Aware":= false,

"Async = Capable": false,

<= p style=3D"margin:0px"> "Relation Name": "_td",

= "Schema": "public",

"Al= ias": "_td"= ,

"Startup Cost": 0.00,

"Total Cost": 1100.07,

"Plan = Rows": 43307,

"Plan Width": 16<= /span>,

"Actual Startup Time": 0.023= ,

"Actual Total Time": 0.042,

"Actual Rows": 1= 00,

"Actual Loops": 1,

= "Output": ["_td.pf2_id"],

&q= uot;Shared Hit Blocks": 2,

"Shared Read Blocks": 0,

= "Shared Dirtied Blocks"<= /span>: 0,

"Sha= red Written Blocks": 0,

"Local Hit Blocks": 0,

= "Local Read Blocks": <= span style=3D"color:rgb(0,0,255)">0,

= "Local Dirtie= d Blocks": 0,

"Local Written Blocks": 0,

"Temp Read Blocks": 0,

= "Temp Written Blocks= ": 0

}

= ]

}

]

= }

]

{

"Node Type&q= uot;: "Index Scan",

"Parent Relationship": "Inner",

"Parallel Aware": false,

= "Async Capable":= false,

"Scan Direction&quo= t;: "Forward",

= "Index Name": "pro= duct_file_pkey",

"Relation Name": "product_file",

"Schema"= ;: "product",

&= quot;Alias": "product_f= ile",

"Startup Cost": 0.42,

"T= otal Cost": 8.36,

= "Plan Rows": 1,

= "Plan Width": 22,

= "Actual Startup Time"= : 0.003,

"Actual Total Time": 0.003,

"Actual Rows": 1<= /span>,

"Actual Loops": 100,

"Output": ["product_file.ctid", "product_file.id"]= ,

"Index Cond": "= (product_file.id =3D \"ANY_subq= uery\".pf2_id)",

= "Rows Removed by Index Recheck"= ;: 0,

"Shared Hit Bl= ocks": 400,

"Sh= ared Read Blocks": 0,=

"Shared Dirtied Blocks": 10,

"Shared Written Blocks": 0,

"Local Hit Blocks": 0,

= "Local Read Blocks": = 0,

= "Local Dirtied Blocks&qu= ot;: 0,

"Local Writt= en Blocks": 0,

"= ;Temp Read Blocks": 0= ,

"Temp Written Blocks": 0

}

]

}

]

= "Planning": {

"Shared= Hit Blocks": 0,

<= p style=3D"margin:0px"> "Share= d Read Blocks": 0,

"Sha= red Dirtied Blocks": 0,

&quo= t;Shared Written Blocks": 0<= /span>,

"Local Hit Blocks": 0<= /span>,

"Local Read Blocks": 0= ,

"Local Dirtied Blocks": 0,

"Local Written Blocks": 0,

"Temp Read Blocks": 0,

"Temp Written Blocks": 0

"Planning Time": 0.249,

"Triggers": [

&quo= t;Trigger Name": "RI_Co= nstraintTrigger_a_26535",

"Constraint Name": "fk_pfft_product",

"Relation&= quot;: "product_file",

"Time": 4.600,

= "Calls": 90

{

"Trigger Name": "RI_ConstraintTrigger_a_26837",

"Constra= int Name": "fk_product_= file_src",

"Relation": "product_file",

= "Time": 5.795,=

&quo= t;Calls": 90

{

"Trigger Na= me": "RI_ConstraintTrig= ger_a_75463",

"Constraint Name": "fk_pfq_src_product_file",

"Relation"= ;: "product_file",

&q= uot;Time": 11179.429,

= "Calls": 90

{

"Trigger Name": "_trg_002_aiu_audit_row",

"Relation"= ;: "product_file",

&q= uot;Time": 49.410,

= "Calls": 90

}

"Execution Time": 11240.265

}

]

--0000000000003c43490602fbf487-- From tgl@sss.pgh.pa.us Tue Aug 15 20:37:39 2023 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qW0nS-001rNN-Ai for pgsql-performance@arkaria.postgresql.org; Tue, 15 Aug 2023 20:37:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qW0nQ-0005AB-FZ for pgsql-performance@arkaria.postgresql.org; Tue, 15 Aug 2023 20:37:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qW0nQ-00059G-4v for pgsql-performance@lists.postgresql.org; Tue, 15 Aug 2023 20:37:44 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qW0nN-000Jv7-6o for pgsql-performance@lists.postgresql.org; Tue, 15 Aug 2023 20:37:42 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 37FKbdwQ1174735; Tue, 15 Aug 2023 16:37:39 -0400 From: Tom Lane To: Les cc: pgsql-performance@lists.postgresql.org Subject: Re: slow delete In-reply-to: References: Comments: In-reply-to Les message dated "Tue, 15 Aug 2023 22:23:26 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1174733.1692131859.1@sss.pgh.pa.us> Date: Tue, 15 Aug 2023 16:37:39 -0400 Message-ID: <1174734.1692131859@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Les writes: > It seems that two foreign key constraints use 10.395 seconds out of the > total 11.24 seconds. But I don't see why it takes that much? Probably because you don't have an index on the referencing column. You can get away with that, if you don't care about the speed of deletes from the PK table ... regards, tom lane From nagylzs@gmail.com Wed Aug 16 04:43:05 2023 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qW8NT-002EeX-Fh for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2023 04:43:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qW8NR-002uit-6F for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2023 04:43:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qW8NQ-002uil-P6 for pgsql-performance@lists.postgresql.org; Wed, 16 Aug 2023 04:43:25 +0000 Received: from mail-ot1-x336.google.com ([2607:f8b0:4864:20::336]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qW8NL-000PPn-FW for pgsql-performance@lists.postgresql.org; Wed, 16 Aug 2023 04:43:24 +0000 Received: by mail-ot1-x336.google.com with SMTP id 46e09a7af769-6bcbb0c40b1so5016581a34.3 for ; Tue, 15 Aug 2023 21:43:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692160998; x=1692765798; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=84MHlG/JaZ8vuYix0N5nR2P6QkKWbNA8riru3bXIsSE=; b=GLarJTPGyoFWAAbcuo2jzgmuCFhj8a/6gls5i7lHVfjSVFJOHkth/gYeaX9eVLZqjV buHqCajphED9vtW8XtdrjHBI/VfOvf2oRn82HQdv+g39vXJTm8CHQTF/nkAeKRfcZgxj QpZssEVFuVhEdq0G3fXW2OJjXodfDL20r9vCARvkkqQOxFxzvbVz9A4UwqmZ3B2r+idL jwXHcvN2pImAZzgpRb4hDbTxTYE0O+VV33pwIFxdgBBE029PZ2TNuosVU0EMUOKOd0rd YT+SYSegYoMKepUJhqmLsHzUm2FW8u8v83mkw3G9XOgFqsRWjhi3buvQoTp1kddlRNdt QNCQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692160998; x=1692765798; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=84MHlG/JaZ8vuYix0N5nR2P6QkKWbNA8riru3bXIsSE=; b=RigoIC6DB63z6XKrwrhiOE/xwvEeSH1Qy/FJvNDsxr5/8lW+2Th4hqk9GDA+ZATnUJ Cket9XpJmfx4XxOZ2rqY28DKAH/Hl61LnPF6ePS8Y09xQAGmo/H/Z9AA3z3Bo4ZVs0Os /PAcgd/5yfe2VfCUtd3hyQCP92ChKc1wdJ5zE10bdzSq8NgDuO/ThzlyE/1/PnImz1jS jAHHkr025JiMbzOVg2YMNyBCdDKXls3BToeMGbPNWqaAb+IM3M5TgMEVQ4ukPxndWSFf rA1jhYywUDK1Xy/yYyj9WhtgGOc84d+qJV+TJNp6Ew93T7oMYREyx7CnG2+3nLsoATWG G5Wg== X-Gm-Message-State: AOJu0YwuJI3NzSQtKemrsTqWkkjAH0YV3WLKIe0yhJ/W6s3KmkuB8r5A URoPECfh2/9wh3GaHOTEaPA79baaA8R7mubn8hqB1TI544AjUQ== X-Google-Smtp-Source: AGHT+IH28/mBTPxVe1HMI4iOlSRuEYG0Y4CN5jEMJEi91SA3B4nn20HE9Wp4DbcMOWw/xNdjJ0eFCnsdxebcCnBY+us= X-Received: by 2002:a05:6830:1bc2:b0:6bd:bcd:43ee with SMTP id v2-20020a0568301bc200b006bd0bcd43eemr823714ota.29.1692160997687; Tue, 15 Aug 2023 21:43:17 -0700 (PDT) MIME-Version: 1.0 References: <1174734.1692131859@sss.pgh.pa.us> In-Reply-To: <1174734.1692131859@sss.pgh.pa.us> From: Les Date: Wed, 16 Aug 2023 06:43:05 +0200 Message-ID: Subject: Re: slow delete To: Tom Lane Cc: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000002b8831060302efd4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002b8831060302efd4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Tom Lane ezt =C3=ADrta (id=C5=91pont: 2023. aug. 15., K= , 22:37): > Les writes: > > It seems that two foreign key constraints use 10.395 seconds out of the > > total 11.24 seconds. But I don't see why it takes that much? > > Probably because you don't have an index on the referencing column. > You can get away with that, if you don't care about the speed of > deletes from the PK table ... > For fk_pfft_product constraint this is true, but I always thought that PostgreSQL can use an index "partially". There is already an index: CREATE UNIQUE INDEX uidx_product_file_file_tag ON product.product_file_tag USING btree (product_file_id, file_tag_id); It has the same order, only it has one column more. Wouldn't it be possible to use it for the plan? After I created these two missing indices: CREATE INDEX idx_pft_pf ON product.product_file_tag USING btree (product_file_id); CREATE INDEX idx_pfq_src_pf ON product.product_file_queue USING btree (src_product_file_id); I could delete all 40 000 records in 10 seconds. Thank you! Laszlo > > --0000000000002b8831060302efd4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Tom Lane <tgl@sss.pgh.pa.us> ezt =C3=ADrta (id=C5=91pont: 2023. a= ug. 15., K, 22:37):

Les <nagylzs@= gmail.com> writes:
> It seems that two foreign key constraints use 10.395 seconds out of th= e
> total 11.24 seconds. But I don't see why it takes that much?

Probably because you don't have an index on the referencing column.
You can get away with that, if you don't care about the speed of
deletes from the PK table ...

For fk_pf= ft_product constraint this is true, but I always thought that PostgreSQL=C2= =A0can use an index "partially". There is already an index:

CREATE UNIQUE INDEX uidx_product_file_= file_tag ON product.product_file_tag USING btree (product_file_id, file_tag_id);

It has the same order, only it ha= s one column more. Wouldn't it be possible to use it for the plan?

After I created these two missing indices:

CREATE= INDEX idx_pft_p= f ON product.pro= duct_file_tag USING btree (product_file_id);

CREATE INDEX idx_pfq_src_pf ON= product.product_file_queue USING btree (src_product_file_id);

I could delete all 40 000 records in 10 seconds.=

Thank you!

=C2=A0 =C2=A0= =C2=A0Laszlo

--0000000000002b8831060302efd4-- From jeff.janes@gmail.com Wed Aug 16 14:03:49 2023 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qWH81-002icL-L1 for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2023 14:04:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qWH80-0075hQ-Bq for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2023 14:04:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qWH80-0075hG-1u for pgsql-performance@lists.postgresql.org; Wed, 16 Aug 2023 14:04:04 +0000 Received: from mail-pj1-x102c.google.com ([2607:f8b0:4864:20::102c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qWH7x-000SCk-Ez for pgsql-performance@lists.postgresql.org; Wed, 16 Aug 2023 14:04:02 +0000 Received: by mail-pj1-x102c.google.com with SMTP id 98e67ed59e1d1-2680a031283so3822128a91.3 for ; Wed, 16 Aug 2023 07:04:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692194640; x=1692799440; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=JzL37IyOL2a25gpBTjmJgObNfMQkWxx/5+IkYuOxDfE=; b=IhxHnOx0a9+4K4uM/ZsQi35bMgIa4fTLnppUV3671digbDGz5dq8/tfI6J973MAU2v OCFEvUWAkawWxRRog5BEN+uudZU7xEhK0jh8bcvN0LZtEHNj6fwZVOAVu594QQPZuSF/ 2ME3q9SRPgWeD0MQQrU2tBcpiqkvEqsQEOr7Y1CAL00BWRkHZTjyC3/c9QUG4OSNmYal EUHflQONslTTkAdx08wsLfDdfK3JG29oW2yNFx487rWqobl/J+2ss8Jnu1xyR0z4UKc/ jwvYHcTgmFMOIXHpM60qUCfzMRpCUnkEYMJqeGg2CN+tL+xsgztlVTDhlE72U1HzJ1iI Rtnw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692194640; x=1692799440; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=JzL37IyOL2a25gpBTjmJgObNfMQkWxx/5+IkYuOxDfE=; b=cuur4GLBw6W/UR0AX6RYjHEiNh8nBmi1Iu7qxlCJETrBuqv1c7sr9ffCg74gVYEBoP cOaxglyjcsqCWNlRFtSWZJdABlrh15C10q3GNkqKdXdt5BZT8JNTAl9XtFDuahGyFDvX k3jzFmTL+H/cJnfGZkgXA0EEU7tDeaGOzvsCgZ95mT4jI3xDgitVwpNMr4YH+7hk0Qx4 SyBmPyQnl8yoI3MfffnIUYOB/P+Gt7+GQ+U2CIA/QjmUQ4tDZ9YTEYL5PSA7c/z1VTDf UAPcMHDLzGbET32gcfx7lwL09q118JPPZXSPdgFBZUURj+/wFVKsN9LJ1MTdUk8K8klp gQXA== X-Gm-Message-State: AOJu0YwlpYUc0aGOzNa98Zas7d3M9RyQy0eu+V7LvUNSdr7NY2SEne0B 7GkFY3/5Q3uY93tGCkq6VVVKQc1GV912wv4akQ== X-Google-Smtp-Source: AGHT+IFU/HrIgK5jSlWxuRdOFHAHhl5fyN5uSWRSp9MCpPPrHGQr26ZvPzJzQwhoL2G1+PgF/dLq8Lf66szB/jcHei0= X-Received: by 2002:a17:90a:840d:b0:26b:365b:e59e with SMTP id j13-20020a17090a840d00b0026b365be59emr1322841pjn.26.1692194640471; Wed, 16 Aug 2023 07:04:00 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Jeff Janes Date: Wed, 16 Aug 2023 10:03:49 -0400 Message-ID: Subject: Re: slow delete To: Les Cc: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000006faec406030ac421" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006faec406030ac421 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Aug 15, 2023 at 4:23=E2=80=AFPM Les wrote: { > > "Trigger Name": "RI_ConstraintTrigger_a_75463", > > "Constraint Name": "fk_pfq_src_product_file", > > "Relation": "product_file", > > "Time": 11179.429, > > "Calls": 90 > > }, > ... > The one with fk_pfft_product looks like this, it has about 5000 records i= n > it: > That constraint took essentially no time. You need to look into the one that took all of the time, which is fk_pfq_src_product_file. Cheers, Jeff > --0000000000006faec406030ac421 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Tue, Aug 15, 2023 at 4:23=E2=80=AFPM L= es <nagylzs@gmail.com> wrote= :

{
"Trigger Name": "RI_ConstraintTrigger_a_75463",
"C= onstraint Name": "fk_pf= q_src_product_file",
"Relation": "product_file",
"Time": 11179.429,
"Calls": 90
}, =

...

=C2=A0

The one with fk_pfft_product looks like this, it has about 5= 000 records in it:

That constra= int took essentially no time.=C2=A0 You need to look into the one that took= all of the time,=C2=A0

which=C2=A0is fk_pfq_src_product_file.

=C2=A0

Cheers,

Jeff

--0000000000006faec406030ac421--