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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 







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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux