Hi
I understand about having to deal with a bad design. How big is the table "select pg_size_pretty(pg_table_size(table_name)).? If the table is not that large relative to the IOPS on your disk system, another solution is to add a binary column IS_DELETED to the table and modify the queries that hit the table to exclude rows where IS_DELETED=y. Also you need an index on this column. I did this with a user table that was a parent table to 120 data tables and users could not be dropped from the system.
On Mon, Sep 3, 2018 at 7:19 AM Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> wrote:
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
