Re: Extremely slow DELETE with cascade foreign keys

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