Thread: Bad performance with cascaded deletes
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
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
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
www.seiler.us
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
www.seiler.us