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

Cant drop foreign keys, there are too much.

‫בתאריך יום ב׳, 3 בספט׳ 2018 ב-11:35 מאת ‪Sergei Kornilov‬‏ <‪sk(at)zsrv(dot)org
‬‏>:‬

> Hello
>
> > Delete on my_table (cost=0.00..65183.30 rows=1573862 width=6) (actual
> time=5121.344..5121.344 rows=0 loops=1)
> > -> Seq Scan on my_table (cost=0.00..65183.30 rows=1573862 width=6)
> (actual time=0.012..2244.393 rows=1572864 loops=1)
> > Filter: ((end_date <= to_date('12/12/2018'::text,
> 'DD/MM/YYYY'::text)) AND (end_date > to_date('11/12/2018'::text,
> 'DD/MM/YYYY'::text)))
> > Rows Removed by Filter: 40253
> > Planning time: 0.210 ms
> > Trigger for constraint table1: time=14730.816 calls=1572864
> > Trigger for constraint table2: time=30718.084 calls=1572864
> > Trigger for constraint table3: time=28170.363 calls=1572864
> > Trigger for constraint table4: time=29573.681 calls=1572864
> > Trigger for constraint table5: time=29629.263 calls=1572864
> > Trigger for constraint table6: time=29628.489 calls=1572864
> > Trigger for constraint table7: time=29798.121 calls=1572864
> > Trigger for constraint table8: time=29645.705 calls=1572864
> > Trigger for constraint table9: time=29657.177 calls=1572864
> > Trigger for constraint table10: time=29487.054 calls=1572864
> > Trigger for constraint table11: time=30010.978 calls=1572864
> > Trigger for constraint table12: time=26383.924 calls=1572864
> > Execution time: 350603.047 ms
>
> As you can see in "actual time" - delete was run only 5 sec. All the other
> time postgresql checked foreign keys triggers. 0,02ms per row seems
> adequate for index lookup.
> It may be better drop foreign keys, delete data, and create foreign keys
> back.
>
> regards, Sergei
>