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?
In-reply-to: <189c6fff-0180-8179-d059-e222ec83bb1e@gusw.net>
References: <189c6fff-0180-8179-d059-e222ec83bb1e@gusw.net>
Comments: In-reply-to Gunther <raj@gusw.net>
	message dated "Thu, 21 Mar 2019 15:31:42 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <16580.1553203016.1@sss.pgh.pa.us>
Date: Thu, 21 Mar 2019 17:16:56 -0400
Message-ID: <16581.1553203016@sss.pgh.pa.us>
Precedence: bulk

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