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

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(dot)cherkassky(at)gmail(dot)com> 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(at)gmail(dot)com‬‏>:‬
>
>> 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(dot)cherkassky(at)gmail(dot)com> wrote:
>>
>>> 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
>>>>
>>>