From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jacque Edmund <jacqueedmund@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Native Foreign Keys housekeeping time intensive for Relational Model
Date: Sun, 28 Mar 2021 06:49:24 +0200
Message-ID: <5d3f776063f41977df5910c6cdaef6b963342023.camel@cybertec.at> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, tgl@sss.pgh.pa.us, jacqueedmund@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Native Foreign Keys housekeeping time intensive for Relational Model
In-Reply-To: <5d3f776063f41977df5910c6cdaef6b963342023.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox