From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gunther <raj@gusw.net>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: EXPLAIN PLAN for DELETE CASCADE or DELETE not using pkey index despite EXPLAINing that it would?
Date: Thu, 21 Mar 2019 17:16:56 -0400
Message-ID: <16581.1553203016@sss.pgh.pa.us> (raw)
In-Reply-To: <189c6fff-0180-8179-d059-e222ec83bb1e@gusw.net>
References: <189c6fff-0180-8179-d059-e222ec83bb1e@gusw.net>
Gunther <raj@gusw.net> writes:
> I have 250 rows to delete, but they are a target to a bunch of child
> tables with foreign key on delete cascade.
> EXPLAIN DELETE FROM Foo WHERE id = (SELECT fooId FROM Garbage);
> shows me that it uses the nested loop by Foo_pkey index to find the 250
> items from Garbage to be deleted.
> But once that starts, I see HUGE amount of read activity from the
> tablespace Foo_main that contains the Foo table, and only the Foo table,
> not the Foo_pkey, not any other index, not any other child table, not
> even the toast table for Foo is contained in that tablespace (I have the
> toast table diverted with symlinks to another volume).
I'm betting you neglected to index the referencing column for one
or more of the foreign keys involved. You can get away with that
as long as you're not concerned with the speed of DELETE ...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, raj@gusw.net, pgsql-performance@lists.postgresql.org
Subject: Re: EXPLAIN PLAN for DELETE CASCADE or DELETE not using pkey index despite EXPLAINing that it would?
In-Reply-To: <16581.1553203016@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox