Re: Extremely slow DELETE with cascade foreign keys

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux