From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Extremely slow DELETE with cascade foreign keys
Date: Tue, 5 Dec 2017 14:49:14 -0300
Message-ID: <20171205174914.7q4x56gii3f626zg@alvherre.pgsql> (raw)
In-Reply-To: <734f6343-35f6-1d1d-3fd7-87e1a1dea9da@gmail.com>
Rodrigo Rosenfeld Rosas wrote:
> Em 05-12-2017 15:25, Tom Lane escreveu:
> > > 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?
>
> Yes, I was also hoping to get more insights through the EXPLAIN output :)
It normally does. Can you show \d of the table containing the FK?
alvherre=# begin; explain analyze delete from pk where a = 505; rollback;
BEGIN
Duración: 0,207 ms
QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Delete on pk (cost=0.00..8.27 rows=1 width=6) (actual time=0.023..0.023 rows=0 loops=1)
-> Index Scan using pk_pkey on pk (cost=0.00..8.27 rows=1 width=6) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (a = 505)
Trigger for constraint fk_a_fkey: time=201.580 calls=1
Total runtime: 201.625 ms
(5 filas)
alvherre=# \d fk
Tabla «public.fk»
Columna │ Tipo │ Modificadores
─────────┼─────────┼───────────────
a │ integer │
Restricciones de llave foránea:
"fk_a_fkey" FOREIGN KEY (a) REFERENCES pk(a) ON DELETE CASCADE
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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-performance@postgresql.org
Cc: alvherre@alvh.no-ip.org, rr.rosas@gmail.com, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: Extremely slow DELETE with cascade foreign keys
In-Reply-To: <20171205174914.7q4x56gii3f626zg@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox