From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Campbell, Lance <lance@illinois.edu>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Delete Enhancement Request
Date: Mon, 14 Jan 2019 20:46:54 -0500
Message-ID: <2613.1547516814@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwYCdDxwDh6yK98PLs3jewVb5zdDWE3J2sS-3qZh5KTEmg@mail.gmail.com>
References: <EE8A78ED-B910-4FC0-BE85-39F74B90A055@illinois.edu>
<CAKFQuwYCdDxwDh6yK98PLs3jewVb5zdDWE3J2sS-3qZh5KTEmg@mail.gmail.com>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jan 14, 2019 at 8:18 AM Campbell, Lance <lance@illinois.edu> wrote:
>> When deleting a large number of records with constraints and triggers PostgreSQL is exceptionally slow.
> You may wish to provide an explicit situation that you are
> experiencing so that others may offer their experienced insights as to
> whether you could be doing anything different to increase the speed of
> your deletes or techniques to batch them yourself.
Given the mention of constraints, I'm suspicious that the problem is
foreign key constraint(s) that lack an index on the referencing column(s).
PG will let you do that, but if you care about the speed of deletes on
the referenced table, you don't want to omit the index on the referencing
side.
regards, tom lane
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: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, lance@illinois.edu
Subject: Re: Delete Enhancement Request
In-Reply-To: <2613.1547516814@sss.pgh.pa.us>
* 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