Re: Plans for ON DELETE CASCADE? Which index is used, if at all?

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