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

[Dominique Devienne <ddevienne@xxxxxxxxx>]

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.

The reason I'm asking, is because we currently (automatically) add indexes on FK columns, to avoid full-scan of child tables when a parent row is deleted (i.e. un-indexed FKs), but I realized that index we create explicitly is also a *prefix* of the natural-key constraint, thus I suspect redundant with the index supporting that NK constraint.

But before getting rid of that index, which I suspect is redundant, I wanted to verify.

And I happen to discover I don't know how to verify it. My PostgreSQL Fu is not great...

So, could someone please:

1) teach me how to know which index is used when cascading FKs in child tables?

2) confirm my suspicion that the  child_parent_idx index below is redundant, given the  child_parent_name_key one?

Thanks, --DD

```

dd=> create table parent (id int generated always as identity primary key, name text not null unique);
CREATE TABLE

dd=> create table child (id int generated always as identity primary key, parent int not null references parent(id) on delete cascade, name text not null, unique(parent, name));
CREATE TABLE

dd=> create index child_parent_idx on child(parent);
CREATE INDEX

dd=> insert into parent(name) values ('foo');
INSERT 0 1
dd=> insert into child(parent, name) values (1, 'c1');
INSERT 0 1
dd=> insert into child(parent, name) values (1, 'c2');
INSERT 0 1
dd=> insert into parent(name) values ('bar');
INSERT 0 1
dd=> insert into child(parent, name) values (2, 'c3');
INSERT 0 1

dd=> explain (analyze) delete from parent where id = 2;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Delete on parent  (cost=0.15..8.17 rows=1 width=6) (actual time=0.052..0.053 rows=0 loops=1)
   ->  Index Scan using parent_pkey on parent  (cost=0.15..8.17 rows=1 width=6) (actual time=0.021..0.024 rows=1 loops=1)
         Index Cond: (id = 2)
 Planning Time: 0.090 ms
 Trigger for constraint child_parent_fkey: time=0.242 calls=1
 Execution Time: 0.329 ms
(6 rows)

dd=> \d+ child
                                              Table "public.child"
 Column |  Type   | Collation | Nullable |           Default            | Storage  | Stats target | Description
--------+---------+-----------+----------+------------------------------+----------+--------------+-------------
 id     | integer |           | not null | generated always as identity | plain    |              |
 parent | integer |           | not null |                              | plain    |              |
 name   | text    |           | not null |                              | extended |              |
Indexes:
    "child_pkey" PRIMARY KEY, btree (id)
    "child_parent_name_key" UNIQUE CONSTRAINT, btree (parent, name)
    "child_parent_idx" btree (parent)
Foreign-key constraints:
    "child_parent_fkey" FOREIGN KEY (parent) REFERENCES parent(id) ON DELETE CASCADE
Access method: heap
```