Re: trying to delete most of the table by range of date col

[Jeff Janes <jeff.janes@xxxxxxxxx>]

4)delete in chunks : 

do $$

declare 

rec integer;

begin

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.

Cheers,

Jeff