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

Hi jefff,
I tried every solution that I checked on net. I cant disable foreign keys
or indexes.

Trying to have better performance by just changing the query / changing
parameters.

‫בתאריך יום ב׳, 3 בספט׳ 2018 ב-18:25 מאת ‪Jeff Janes‬‏ <‪
jeff(dot)janes(at)gmail(dot)com‬‏>:‬

>
>
>
>>
>> 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
>