Message-ID: <5d3f776063f41977df5910c6cdaef6b963342023.camel@cybertec.at>
Subject: Re: Native Foreign Keys housekeeping time intensive for Relational
 Model
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jacque Edmund <jacqueedmund@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Sun, 28 Mar 2021 06:49:24 +0200
In-Reply-To: <2002503.1616856201@sss.pgh.pa.us>
References: 
	<CANOmo02GKKLsGbszLtAzLLX--Ab6d-9Bp51bYDC3dfRhe8dgvA@mail.gmail.com>
	 <fb45b9f1134ad17b944eb073ac8523f37f4199a9.camel@cybertec.at>
	 <2002503.1616856201@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/5d3f776063f41977df5910c6cdaef6b963342023.camel%40cybertec.at>
Precedence: bulk

On Sat, 2021-03-27 at 10:43 -0400, Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> 
> > On Fri, 2021-03-26 at 14:00 -0400, Jacque Edmund wrote:
> > > I've already seen that my current set of FK relationships between < 100 tables and data footprint
> > > has yielded very large lookup times for the underlying referential integrity of the FKs to vet
> > > whether it can DELETE a row or not based on its FK relationships.
> 
> > I would be surprised if correctly indexed foreign keys would slow deletes down so that
> > the performance becomes intolerable.
> 
> Deletes being slow often means that you don't have an index on the
> referencing column(s).  PG requires you to have an index on the referenced
> columns, but not on the other side of the FK relationship.

Exactly, that's what I wanted to say: I suspect that such indexes are missing
or not created correctly.  Otherwise, the performance should be good.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com