Plans for ON DELETE CASCADE? Which index is used, if at all? - Mailing list pgsql-general
| From | Dominique Devienne |
|---|---|
| Subject | Plans for ON DELETE CASCADE? Which index is used, if at all? |
| Date | |
| Msg-id | CAFCRh-9YVryrkLa8eEXPG91wOWyMty4K4oy9YYKA9Jny0BTfGA@mail.gmail.com Whole thread Raw |
| Responses |
Re: Plans for ON DELETE CASCADE? Which index is used, if at all?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-general |
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.
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
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
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
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
```
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
```
pgsql-general by date: