Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4D1D32E0069 for ; Wed, 27 Feb 2008 20:23:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 71217-08 for ; Wed, 27 Feb 2008 20:23:06 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from pillette.com (adsl-67-119-5-202.dsl.snfc21.pacbell.net [67.119.5.202]) by postgresql.org (Postfix) with ESMTP id 432F72E0032 for ; Wed, 27 Feb 2008 20:23:06 -0400 (AST) Received: from dhcp203.pillette.com (dhcp203.pillette.com [192.168.1.203]) by pillette.com (8.11.6/8.11.6) with ESMTP id m1S0MuL16316; Wed, 27 Feb 2008 16:22:56 -0800 Date: Wed, 27 Feb 2008 16:54:20 -0800 From: Andrew Lazarus X-Mailer: The Bat! (v4.0.14) Home Organization: Pillette Investment Management X-Priority: 3 (Normal) Message-ID: <1069587644.20080227165420@pillette.com> To: Tom Lane CC: pgsql-performance@postgresql.org Subject: Re: when is a DELETE FK trigger planned? In-Reply-To: <19945.1203989110@sss.pgh.pa.us> References: <010161370.20080225125626@pillette.com> <19945.1203989110@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----------C67819120CCA48B" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.891 tagged_above=0 required=5 tests=AWL=-0.102, FH_HOST_EQ_PACBELL_D=0.893, RDNS_DYNAMIC=0.1 X-Spam-Level: X-Archive-Number: 200802/389 X-Sequence-Number: 28931 ------------C67819120CCA48B Content-Type: text/plain; charset=iso-8859-15 Content-Transfer-Encoding: quoted-printable 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. --=20 Sincerely, Andrew Lazarus mailto:andrew@pillette.com ------------C67819120CCA48B Content-Type: text/x-vcard; name="vCard.VCF" Content-transfer-encoding: quoted-printable Content-Disposition: attachment; filename="vCard.VCF" 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=3DQUOTED-PRINTABLE:800-366-0688=3D0D=3D0A3028 Fillmore = S=3D treet=3D0D=3D0ASan Francisco=3D0D=3D0ACA=3D0D=3D0A94123=3D0D=3D0AUSA X-GENDER:Male REV:18991230T080000Z END:VCARD ------------C67819120CCA48B--