Date: Tue, 5 Dec 2017 14:49:14 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@lists.postgresql.org
Subject: Re: Extremely slow DELETE with cascade foreign keys
Message-ID: <20171205174914.7q4x56gii3f626zg@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <734f6343-35f6-1d1d-3fd7-87e1a1dea9da@gmail.com>
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
Precedence: bulk

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