On 3/28/23 06:55, Dominique Devienne wrote:
> On Tue, Mar 28, 2023 at 3:23 PM Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> Dominique Devienne <ddevienne@gmail.com
> <mailto:ddevienne@gmail.com>> writes:
> > Hi. Given the classical parent/child schema below, with an ON DELETE
> > CASCADE FK constraint, I'd like to know which index is used (if
> any) to
> > delete child rows affected by the CASCADE.
>
> > But explain (analyze) does not give me that.
>
> Yeah, it will just report the time spent in the FK triggers,
> not what they were doing exactly.
>
> IIRC, you can see the CASCADE operations with contrib/auto_explain,
> if you enable auto_explain.log_nested_statements.
>
>
> Thanks. Looks like this won't be easily available to me :(. --DD
It is part of the community contrib modules:
https://www.postgresql.org/docs/current/auto-explain.html
So install via whatever package system you are using. Or if building
from source build in the contrib/ directory.
>
> dd=> select * from pg_available_extensions where name like '%auto%';
> name | default_version | installed_version | comment
> ---------+-----------------+-------------------+---------------------------------------
> autoinc | 1.0 | | functions for
> autoincrementing fields
> (1 row)
>
> dd=> select * from pg_available_extensions where name like '%explain%';
> name | default_version | installed_version | comment
> ------+-----------------+-------------------+---------
> (0 rows)
--
Adrian Klaver
adrian.klaver@aklaver.com