Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ag0ax-0001uF-5D for pgsql-sql@arkaria.postgresql.org; Wed, 16 Mar 2016 01:49:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ag0aw-0003om-Jn for pgsql-sql@arkaria.postgresql.org; Wed, 16 Mar 2016 01:49:54 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ag0au-0003me-69 for pgsql-sql@postgresql.org; Wed, 16 Mar 2016 01:49:52 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1ag0aq-0001U2-Gd for pgsql-sql@postgresql.org; Wed, 16 Mar 2016 01:49:50 +0000 Received: from [10.9.9.212] (helo=mailfront12.runbox.com) by bars.runbox.com with esmtp (Exim 4.71) (envelope-from ) id 1ag0an-00087X-11; Wed, 16 Mar 2016 02:49:45 +0100 Received: from cpe-76-176-177-1.san.res.rr.com ([76.176.177.1] helo=seasyslap4) by mailfront12.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.82) id 1ag0af-0004u3-Et; Wed, 16 Mar 2016 02:49:37 +0100 From: "Mike Sofen" To: "'David G. Johnston'" , "'Andreas Kretschmer'" Cc: "'ivo liondov'" , References: <503958962.9182.1458090747344.JavaMail.open-xchange@oxweb01.ims-firmen.de> In-Reply-To: Subject: Re: delete taking long time Date: Tue, 15 Mar 2016 18:49:25 -0700 Message-ID: <000201d17f26$13eda040$3bc8e0c0$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0003_01D17EEB.678EC840" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQFTOZ1BdsIiUHgoP+WtPUFQHMVQTAGdHUjCAeuCeuegO5EksA== Content-Language: en-us X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0003_01D17EEB.678EC840 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable >>From: David G. Johnston >>Sent: Tuesday, March 15, 2016 6:25 PM > There are around 800.000 records matching this rule, and seems to be = taking > an awful lot of time - 4 hours and counting. What could be the reason = for > such a performance hit and how could I optimise this for future cases? the db has to touch such many rows, and has to write the transaction = log. And update every index. And it has to check the referenced tables for the constraints. Do you have proper indexes? =20 Given the lack of indexes on the one table that is shown I suspect this = is the most likely cause (FK + indexes) =20 David J. <<=20 =20 There are SEVEN FKs against that table=E2=80=A6I would bet = that=E2=80=99s 50% of the duration. The lack of an index, perhaps an = issue, but=20 With that many FK references plus that many rows=E2=80=A6the transaction = log could easily blow out and start paging to disk. =20 When deleting more than perhaps 20k rows, I will normally write a delete = loop, grabbing roughly 20-50k rows at time (server capacity dependent), deleting that set, grabbing another set, etc. That allows = the set to commit, releasing pressure on the tran log. =20 You can easily experiment and see how long 10k rows take to delete. If = still long, the FKs are the issue=E2=80=A6you may need to script them = out,=20 drop them, run the deletes, then rebuild them. =20 Mike S. ------=_NextPart_000_0003_01D17EEB.678EC840 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

>>From:<= /b> = David G. Johnston
>>Sent:<= /b> = Tuesday, March 15, 2016 6:25 PM

> There are around = 800.000 records matching this rule, and seems to be taking
> an = awful lot of time - 4 hours and counting. What could be the reason = for
> such a performance hit and how could I optimise this for = future cases?

the db has to touch such many rows, and has to = write the transaction log. And
update every index. And it has to = check the referenced tables for the
constraints. Do you have proper = indexes?

 

Given the lack of indexes on the one table that is = shown I suspect this is the most likely cause (FK + = indexes)

 

David J.

<< 

 

There are = SEVEN FKs against that table=E2=80=A6I would bet that=E2=80=99s 50% of = the duration.=C2=A0 The lack of an index, perhaps an issue, but =

With that = many FK references plus that many rows=E2=80=A6the transaction log could = easily blow out and start paging to disk.

 

When = deleting more than perhaps 20k rows, I will normally write a delete = loop, grabbing roughly 20-50k rows at time (server = capacity

dependent), = deleting that set, grabbing another set, etc.=C2=A0 That allows the set = to commit, releasing pressure on the tran log.

 

You can = easily experiment and see how long 10k rows take to delete.=C2=A0 If = still long, the FKs are the issue=E2=80=A6you may need to script them = out,

drop them, = run the deletes, then rebuild them.

 

Mike = S.

------=_NextPart_000_0003_01D17EEB.678EC840--