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

On Mon, Sep 03, 2018 at 09:27:52AM +0300, Mariel Cherkassky wrote:
> I'm trying to find the best way to delete most of the table but not all of it
> according to a range of dates.

> Indexes:
> "end_date_idx" btree (end_date)

> Referenced by:
> TABLE "table1" CONSTRAINT "application_change_my_table_id_fkey" FOREIGN
> KEY (my_table_id) REFERENCES my_table(id)
> TABLE "table2" CONSTRAINT "configuration_changes_my_table_id_fkey"
> FOREIGN KEY (my_table_id) REFERENCES my_table(id)
...

> As you can see alot of other tables uses the id col as a foreign key which
> make the delete much slower.

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

Do the other tables have indices on their referencING columns ?

https://www.postgresql.org/docs/devel/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
"Since a DELETE of a row from the referenced table [...] will require a scan of
the referencing table for rows matching the old value, it is often a good idea
to index the referencing columns too."

Note, I believe it's planned in the future for foreign keys to support
referenes to partitioned tables, at which point you could just DROP the monthly
partition...but not supported right now.

Justin