From: "Mike Sofen" <msofen@runbox.com>
To: "'Andreas Kretschmer'" <akretschmer@spamfence.net>,
	<pgsql-sql@postgresql.org>
References: 
 <CAJ2MONRiYczFWzL5_6bh3ZV_pcnN2zzg2Q+GDW1r_VXS=-SRtw@mail.gmail.com>
 <503958962.9182.1458090747344.JavaMail.open-xchange@oxweb01.ims-firmen.de>
 <CAJ2MONQoLp=pd5U4vYEuW072FSKvr=QJVBzpUgs6TjGFMQGGEQ@mail.gmail.com>
 <20160316115753.GA6081@tux>
In-Reply-To: <20160316115753.GA6081@tux>
Subject: Re: delete taking long time
Date: Wed, 16 Mar 2016 05:31:45 -0700
Message-ID: <003401d17f7f$cf4f4d30$6dede790$@runbox.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQFTOZ1BdsIiUHgoP+WtPUFQHMVQTAGdHUjCAWip52sA/xZwAKA4YhJQ
Content-Language: en-us
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

I agree with Andreas (indexes) - 10 minutes to delete 10k rows is about 9.5
minutes too long.=20=20
Either the "select" part of the query can't find the rows quickly or the FK
burden is crushing the life out of it.

If every involved table has an index on their Primary Key then the 10k row
delete should take
maybe 30-60 seconds.  Highly dependent on how many FK rows are involved.

And...from a db design perspective, a table referenced by 7 FKs shouldn't be
having this type
of delete run against it...it's just too expensive if it must happen
routinely.  This is where=20
de-normalization might be called for, to collapse some of those references,
or a shift to
stored functions that maintain integrity versus the declared foreign keys
maintaining it.

Mike S.

-----Original Message-----
From: Andreas Kretschmer
Sent: Wednesday, March 16, 2016 4:58 AM

ivo liondov <ivo.liondov@gmail.com> wrote:

>=20
> explain (analyze) delete from connection where uid in (select uid from=20
> connection where ts > '2016-03-10 01:00:00' and ts < '2016-03-10=20
> 01:10:00');
>=20
>=20
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
> -----
>=20
> =A0Delete on connection=A0 (cost=3D0.43..174184.31 rows=3D7756 width=3D12=
)=20
> (actual time=3D
> 529.739..529.739 rows=3D0 loops=3D1)
>=20
> =A0=A0 ->=A0 Nested Loop=A0 (cost=3D0.43..174184.31 rows=3D7756 width=3D1=
2) (actual=20
> time=3D
> 0.036..526.295 rows=3D2156 loops=3D1)
>=20
> =A0=A0 =A0 =A0 =A0 ->=A0 Seq Scan on connection connection_1=A0=20
> (cost=3D0.00..115684.55 rows=3D
> 7756 width=3D24) (actual time=3D0.020..505.012 rows=3D2156 loops=3D1)
>=20
> =A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: ((ts > '2016-03-10 01:00:00'::time=
stamp without=20
> time
> zone) AND (ts < '2016-03-10 01:10:00'::timestamp without time zone))


there is no index on the ts-column.


>=20
> =A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 Rows Removed by Filter:=A03108811
>=20
> =A0=A0 =A0 =A0 =A0 ->=A0 Index Scan using connection_pkey on connection=
=A0=20
> (cost=3D0.43..7.53
> rows=3D1 width=3D24) (actual time=3D0.009..0.010 rows=3D1 loops=3D2156)
>=20
> =A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((uid)::text =3D (connection_1=
.uid)::text)
>=20
> =A0Planning time: 0.220 ms
>=20
> =A0Trigger for constraint dns_uid_fkey: time=3D133.046 calls=3D2156
>=20
> =A0Trigger for constraint files_uid_fkey: time=3D39780.799 calls=3D2156
>=20
> =A0Trigger for constraint http_uid_fkey: time=3D99300.851 calls=3D2156
>=20
> =A0Trigger for constraint notice_uid_fkey: time=3D128.653 calls=3D2156
>=20
> =A0Trigger for constraint snmp_uid_fkey: time=3D59.491 calls=3D2156
>=20
> =A0Trigger for constraint ssl_uid_fkey: time=3D74.052 calls=3D2156
>=20
> =A0Trigger for constraint weird_uid_fkey: time=3D25868.651 calls=3D2156
>=20
> =A0Execution time: 165880.419 ms

i guess there are no indexes for this tables and the relevant columns


> I think you are right, fk seem to take the biggest chunk of time from=20
> the hole delete operation. I made a test with 10.000 rows, it took 12=20
> minutes. 20.000 rows took about 25 minutes to delete.

create the missing indexes now and come back with the new duration.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082=B0, E 13.56889=B0


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


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