sql delete with and without index on foreign table

gistfile1.txt

postgres=# create table t(col1 int primary key, col2 int, col3 int);
CREATE TABLE
postgres=# create table r(col4 int primary key, col1 int references t(col1) on update cascade on delete cascade);
CREATE TABLE
postgres=# insert into t select x,x,x from generate_series(1, 1000000) x;
insert into r select col1, 1+ col1 % 10 from t;
INSERT 0 1000000
INSERT 0 1000000
postgres=# create table to_be_deleted as select col1, null::int as col2 from t where col2 % 9 = 3;
SELECT 111111
postgres=# create index on to_be_deleted(col1);
CREATE INDEX
postgres=# do $$
declare i int;
begin
for i in 1..20 loop
 with cte as (select * from to_be_deleted where col2 is null limit 10000) update to_be_deleted set col2 = i from cte where cte.col1 = to_be_deleted.col1;
if not found then
 exit;
end if;
commit;
end loop;
end; $$ language plpgsql;
DO
postgres=# do $$
declare i int; j int;
begin
for i in select distinct col2 from to_be_deleted order by 1 loop
raise notice '%', i;
delete from t where col1 in  (select col1 from to_be_deleted where to_be_deleted.col2 = i); raise notice '%', 'done'; commit;
end loop;
end; $$ language plpgsql;
NOTICE:  1
^CCancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."r" WHERE $1 OPERATOR(pg_catalog.=) "col1""
SQL statement "delete from t where col1 in  (select col1 from to_be_deleted where to_be_deleted.col2 = i)"
PL/pgSQL function inline_code_block line 6 at SQL statement

-- without index on r(col1), delete will be sloooooooooooooooooooow and take forever (verfied by auto_explain)

postgres=# create index on r(col1);
CREATE INDEX
postgres=# do $$
declare i int; j int;
begin
for i in select distinct col2 from to_be_deleted order by 1 loop
raise notice '%', i;
delete from t where col1 in  (select col1 from to_be_deleted where to_be_deleted.col2 = i); raise notice '%', 'done'; commit;
end loop;
end; $$ language plpgsql;
NOTICE:  1
NOTICE:  done
NOTICE:  2
NOTICE:  done
NOTICE:  3
NOTICE:  done
NOTICE:  4
NOTICE:  done
NOTICE:  5
NOTICE:  done
NOTICE:  6
NOTICE:  done
NOTICE:  7
NOTICE:  done
NOTICE:  8
NOTICE:  done
NOTICE:  9
NOTICE:  done
NOTICE:  10
NOTICE:  done
NOTICE:  11
NOTICE:  done
NOTICE:  12
NOTICE:  done
DO


postgres@ubuntu:~$ cat 16/main/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
wal_level = 'logical'
session_preload_libraries = auto_explain
auto_explain.log_min_duration = 0
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_timing = true
auto_explain.log_verbose = on
auto_explain.log_triggers = on
auto_explain.log_nested_statements = on