From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
cc: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>,
        pgsql-performance@lists.postgresql.org
Subject: Re: Extremely slow DELETE with cascade foreign keys
In-reply-to: <20171205164328.6t4w3abfpzp54y2y@alvherre.pgsql>
References: <20171205164328.6t4w3abfpzp54y2y@alvherre.pgsql>
Comments: In-reply-to Alvaro Herrera <alvherre@alvh.no-ip.org>
	message dated "Tue, 05 Dec 2017 13:43:28 -0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <10849.1512494726.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 05 Dec 2017 12:25:26 -0500
Message-ID: <10850.1512494726@sss.pgh.pa.us>
Precedence: bulk

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Rodrigo Rosenfeld Rosas wrote:
>> explain analyze delete from field_values where transaction_id=3D226;
>> QUERY PLAN
>> -----------------------------------------------------------------------=
--------------------------------------------------------------------------=
--------------
>> =C2=A0Delete on field_values=C2=A0 (cost=3D0.43..257.93 rows=3D481 widt=
h=3D6) (actual
>> time=3D367375.805..367375.805 rows=3D0 loops=3D1)
>> =C2=A0=C2=A0 ->=C2=A0 Index Scan using index_field_values_on_transactio=
n_id on
>> field_values=C2=A0 (cost=3D0.43..257.93 rows=3D481 width=3D6) (actual t=
ime=3D0.223..4.216
>> rows=3D651 loops=3D1)
>> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Index Cond: (transacti=
on_id =3D 226)
>> =C2=A0Planning time: 0.234 ms
>> =C2=A0Execution time: 367375.882 ms
>> (5 registros)
>> =

>> Time: 367377,085 ms (06:07,377)

> Normally this is because you lack indexes on the referencing columns, so
> the query that scans the table to find the referencing rows is a
> seqscan.

Actually though ... the weird thing about this is that I'd expect to
see a separate line in the EXPLAIN output for time spent in the FK
trigger.  Where'd that go?

			regards, tom lane