Subject: Re: EXPLAIN PLAN for DELETE CASCADE or DELETE not using pkey index
 despite EXPLAINing that it would?
To: pgsql-performance@lists.postgresql.org
References: <189c6fff-0180-8179-d059-e222ec83bb1e@gusw.net>
 <16581.1553203016@sss.pgh.pa.us>
From: Gunther <raj@gusw.net>
Message-ID: <808a0d1a-9357-6bd7-bfe9-5f401fa48d5a@gusw.net>
Date: Fri, 22 Mar 2019 16:01:38 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:60.0) Gecko/20100101
 Thunderbird/60.5.3
MIME-Version: 1.0
In-Reply-To: <16581.1553203016@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk


On 3/21/2019 17:16, Tom Lane wrote:
> 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

I had the same suspicion. But firstly my schema is generated 
automatically and all foreign keys have the indexes.

But what is even more stunning is that the table where this massive read 
activity happens is the Foo heap table. I verified that by using strace 
where all the massive amounts of reads are on those files for the main 
Foo table. And this doesn't make sense, since any foreign key targets 
its primary key. The foreign keys of the child tables are also indexed 
and there is no io on the volumes that hold these child tables, nor is 
the io on the volume that holds the Foo_pkey.