public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Rikard Pavelic <[email protected]>
Cc: [email protected]
Subject: Re: slow delete due to reference
Date: Sat, 24 Jun 2017 12:14:52 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

Rikard Pavelic <[email protected]> writes:
> Is it possible to speed up deletes which have null references so they don't check if a reference is valid?

You're thinking about the problem backwards.  Since the table is
self-referential, each row is both a PK (referenced) row and an FK
(referencing) row.  In its role as an FK row, a delete requires no work,
null referencing column or otherwise --- but in its role as a PK row, a
delete does require work.  The PK column here is "id" which is not null in
any row, so for every row, the FK trigger must check to see whether that
id is referenced by any FK row.  With no index on the FK column (undo_id)
that requires an expensive seqscan.

There are optimizations to skip the check when deleting a null PK value,
but that case never occurs in your example.

> --create unique index ix_undo on large_table(undo_id) where undo_id is not null;
> (I was actually expecting that commented out index to exists, but for some reason it didn't)

It would've done the job if you'd had it, I believe.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: slow delete due to reference
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox