agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Kretschmer <andreas@a-kretschmer.de>
To: ivo liondov <ivo.liondov@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: delete taking long time
Date: Wed, 16 Mar 2016 02:12:27 +0100 (CET)
Message-ID: <503958962.9182.1458090747344.JavaMail.open-xchange@oxweb01.ims-firmen.de> (raw)
In-Reply-To: <CAJ2MONRiYczFWzL5_6bh3ZV_pcnN2zzg2Q+GDW1r_VXS=-SRtw@mail.gmail.com>
References: <CAJ2MONRiYczFWzL5_6bh3ZV_pcnN2zzg2Q+GDW1r_VXS=-SRtw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
>
> I am trying to delete the connections with date 2016-03-10 by using the
> following:
>
>
> delete from connection where uid in (select uid from connection where ts >
> '2016-03-10 00:30:00');
try to rewrite that to :
delete from connection where ts > '2016-03-10 00:30:00';
It's simpler - and (maybe) faster.
> 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?
>
> Regards.
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?
How large is shared_buffers set?
Can you show us the explain (analyse)?
Regards, Andreas
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (12+ messages) latest in thread
Message-ID: <503958962.9182.1458090747344.JavaMail.open-xchange@oxweb01.ims-firmen.de>
Permalink: ../503958962.9182.1458090747344.JavaMail.open-xchange@oxweb01.ims-firmen.de/
Also on: postgresql.org/message-id/503958962.9182.1458090747344.JavaMail.open-xchange@oxweb01.ims-firmen.de
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: andreas@a-kretschmer.de, ivo.liondov@gmail.com
Subject: Re: delete taking long time
In-Reply-To: <503958962.9182.1458090747344.JavaMail.open-xchange@oxweb01.ims-firmen.de>
* 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