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
