select count(*) from my_table into rec where end_date <= to_date('12/12/2018','DD/MM/YYYY') and end_date > to_date('11/12/2018','DD/MM/YYYY');
while rec > 0 loop
DELETE FROM my_Table WHERE id IN (select id from my_tablewhere end_date <= to_date('12/12/2018','DD/MM/YYYY') and end_date > to_date('11/12/2018','DD/MM/YYYY') limit 5000);
rec := rec - 5000;
raise notice '5000 records were deleted, current rows :%',rec;
end loop;
end;
$$
;
Execution time : 6 minutes.
So, it seems that the second solution is the fastest one. It there a reason why the delete chunks (solution 4) wasnt faster?
Why would it be faster? The same amount of work needs to get done, no matter how you slice it. Unless there is a specific reason to think it would be faster, I would expect it won't be.
If you aren't willing to drop the constraints, then I think you just need to resign yourself to paying the price of checking those constraints. Maybe some future version of PostgreSQL will be able to do them in parallel.
