agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Mike Sofen <msofen@runbox.com>
To: 'David G. Johnston' <david.g.johnston@gmail.com>
To: 'Andreas Kretschmer' <andreas@a-kretschmer.de>
Cc: 'ivo liondov' <ivo.liondov@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: delete taking long time
Date: Tue, 15 Mar 2016 18:49:25 -0700
Message-ID: <000201d17f26$13eda040$3bc8e0c0$@runbox.com> (raw)
In-Reply-To: <CAKFQuwZ_E08T37A_eTtky2pQ95Ruc65MyywLp-yKjhVPUdgEMw@mail.gmail.com>
References: <CAJ2MONRiYczFWzL5_6bh3ZV_pcnN2zzg2Q+GDW1r_VXS=-SRtw@mail.gmail.com>
<503958962.9182.1458090747344.JavaMail.open-xchange@oxweb01.ims-firmen.de>
<CAKFQuwZ_E08T37A_eTtky2pQ95Ruc65MyywLp-yKjhVPUdgEMw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
>>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?
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…I would bet that’s 50% of the duration. The lack of an index, perhaps an issue, but
With that many FK references plus that many rows…the 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. 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. If still long, the FKs are the issue…you may need to script them out,
drop them, run the deletes, then rebuild them.
Mike S.
view thread (12+ messages) latest in thread
Message-ID: <000201d17f26$13eda040$3bc8e0c0$@runbox.com>
Permalink: ../000201d17f26$13eda040$3bc8e0c0$@runbox.com/
Also on: postgresql.org/message-id/000201d17f26$13eda040$3bc8e0c0$@runbox.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: msofen@runbox.com, david.g.johnston@gmail.com, andreas@a-kretschmer.de, ivo.liondov@gmail.com
Subject: Re: delete taking long time
In-Reply-To: <000201d17f26$13eda040$3bc8e0c0$@runbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox