Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Dominique Devienne
Date:
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
```

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

From
Tom Lane
Date:
Dominique Devienne <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.

            regards, tom lane

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

From
Dominique Devienne
Date:
On Tue, Mar 28, 2023 at 3:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dominique Devienne <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

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) 

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

From
Adrian Klaver
Date:
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