Bad performance with cascaded deletes

Started by Haug Bürgeralmost 8 years ago4 messagesgeneral
Jump to latest
#1Haug Bürger
haug.buerger@zalando.de

I have an issue with delete performance I can't explain.

Delete on zpg_data.session (cost=190.51..4491.20 rows=500 width=46)
...
Planning time: 0.222 ms
Trigger RI_ConstraintTrigger_a_16481 for constraint
availability_cache_ac_session_id_fkey: time=350.116 calls=500

For me this reads like the delete takes 0.2ms and the cascaded delete
takes 350ms. There is a primary key (index) on the availability_cache
table and if I delete it manually deleting with an id is pretty fast.
For me it looks like the trigger doesn't use the primary key/index.

Any ideas why the trigger is slow or how to get a plan for the trigger?

Thanks for help
Haug

Table "zpg_data.session"
Column | Type | Nullable
| Default | Storage |
-----------------------------------+--------------------------+----------+--------------+----------+
id | uuid | not null
| | plain |
... rows deleted ...
Indexes:
"pk_session" PRIMARY KEY, btree (id)
Referenced by:
TABLE "zpg_data.availability_cache" CONSTRAINT
"availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id)
REFERENCES zpg_data.session(id) ON DELETE CASCADE

Table "zpg_data.availability_cache"
Column | Type | Nullable | Default |
Storage |
---------------+--------------------------+----------+-------------+----------+
ac_session_id | uuid | not null | |
plain |
ac_created | timestamp with time zone | not null | |
plain |
ac_content | jsonb | not null | '{}'::jsonb |
extended |
Indexes:
"pk_availability_cache" PRIMARY KEY, btree (ac_session_id)
Foreign-key constraints:
"availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id)
REFERENCES zpg_data.session(id) ON DELETE CASCADE

#2Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Haug Bürger (#1)
Re: Bad performance with cascaded deletes

Haug Bürger wrote:

Delete on zpg_data.session (cost=190.51..4491.20 rows=500 width=46)
...
Planning time: 0.222 ms
Trigger RI_ConstraintTrigger_a_16481 for constraint
availability_cache_ac_session_id_fkey: time=350.116 calls=500

For me this reads like the delete takes 0.2ms and the cascaded delete
takes 350ms.

Could you share the complete plan?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

#3Don Seiler
don@seiler.us
In reply to: Haug Bürger (#1)
Re: Bad performance with cascaded deletes

On Tue, Jun 12, 2018 at 2:26 AM, Haug Bürger <haug.buerger@zalando.de>
wrote:

"availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id)
REFERENCES zpg_data.session(id) ON DELETE CASCADE

Do you have an index on availability_cache.ac_session_id? These fields are
not automatically indexed and that can lead to horrible performance on
cascading operations like this.

--
Don Seiler
www.seiler.us

#4Don Seiler
don@seiler.us
In reply to: Don Seiler (#3)
Re: Bad performance with cascaded deletes

On Tue, Jun 12, 2018 at 10:48 AM, Don Seiler <don@seiler.us> wrote:

On Tue, Jun 12, 2018 at 2:26 AM, Haug Bürger <haug.buerger@zalando.de>
wrote:

"availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id)
REFERENCES zpg_data.session(id) ON DELETE CASCADE

Do you have an index on availability_cache.ac_session_id? These fields
are not automatically indexed and that can lead to horrible performance on
cascading operations like this.

I'm blind apparently, it's your PK.

--
Don Seiler
www.seiler.us