From: Andrew Lazarus <andrew@pillette.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: when is a DELETE FK trigger planned?
Date: Wed, 27 Feb 2008 16:54:20 -0800
Message-ID: <1069587644.20080227165420@pillette.com> (raw)
In-Reply-To: <19945.1203989110@sss.pgh.pa.us>
References: <010161370.20080225125626@pillette.com>
<19945.1203989110@sss.pgh.pa.us>
I figured out what appears to happen with cascading delete using a
seqscan. In this case, the foreign keys in the child table are not
equally distributed. A few parent values occur often. Most parent
values do not occur at all. So the planner, faced with an unknown
generic key, takes the safe route.
What I've done is remove the FK (maybe it would be better to leave it
albeit disabled for documentation) and written my own AFTER DELETE
trigger that uses EXECUTE to delay planning until the actual value is
known. This appears to work correctly.
--
Sincerely,
Andrew Lazarus mailto:andrew@pillette.com
Attachments:vCard.VCF (text/x-vcard, 377B) download
BEGIN:VCARD
VERSION:2.1
N:Lazarus;Andrew;;;Ph.D.
FN:Andrew Lazarus, Ph.D.
EMAIL;PREF;INTERNET:andrew@pillette.com
TITLE:Director of R&D
ADR;WORK:;800-366-0688;3028 Fillmore Street;San Francisco;CA;94123;USA
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:800-366-0688=0D=0A3028 Fillmore S=
treet=0D=0ASan Francisco=0D=0ACA=0D=0A94123=0D=0AUSA
X-GENDER:Male
REV:18991230T080000Z
END:VCARD
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-performance@postgresql.org
Cc: andrew@pillette.com, tgl@sss.pgh.pa.us
Subject: Re: when is a DELETE FK trigger planned?
In-Reply-To: <1069587644.20080227165420@pillette.com>
* 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