Date: Mon, 3 Sep 2018 02:06:24 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-performance@postgresql.org>
Subject: Re: trying to delete most of the table by range of date col
Message-ID: <20180903070624.GA11702@telsasoft.com>
References: 
 <CA+t6e1krNiXeqbXPi7pSYA8vvu7KsRCcE8yexzFBGrnBhS7sZg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+t6e1krNiXeqbXPi7pSYA8vvu7KsRCcE8yexzFBGrnBhS7sZg@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

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