I'm not responsible for this design but I'm trying to improve it. Using partition isnt an option because partitions doesnt support foreign key. Moreover, most queries on all those tables uses the id col of the main table.
בתאריך יום ב׳, 3 בספט׳ 2018 ב-14:09 מאת Carrie Berlin <berlincarrie@xxxxxxxxx>:
This is a terribley inflexible design, why so many foreign keys? If the table requires removing data, rebuild with partitions. Parent keys should be in reference tables, not in fact table.On Mon, Sep 3, 2018 at 04:51 Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> wrote:Cant drop foreign keys, there are too much.בתאריך יום ב׳, 3 בספט׳ 2018 ב-11:35 מאת Sergei Kornilov <sk@xxxxxxxx>: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
