Re: EXPLAIN PLAN for DELETE CASCADE or DELETE not using pkey index despite EXPLAINing that it would?

On 3/21/2019 17:16, Tom Lane wrote:
> Gunther <raj(at)gusw(dot)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.