Skip site navigation (1) Skip section navigation (2)

Re: when is a DELETE FK trigger planned?

From: Andrew Lazarus <andrew(at)pillette(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: when is a DELETE FK trigger planned?
Date: 2008-02-28 00:54:20
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.

 Andrew Lazarus        mailto:andrew(at)pillette(dot)com

Attachment: vCard.VCF
Description: text/x-vcard (377 bytes)

In response to

pgsql-performance by date

Next:From: Joel StevensonDate: 2008-02-28 02:19:12
Subject: Re: LISTEN / NOTIFY performance in 8.3
Previous:From: Tom LaneDate: 2008-02-27 23:37:44
Subject: Re: disabling an index without deleting it?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group