pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
when is a DELETE FK trigger planned? 3+ messages / 2 participants [nested] [flat]
* when is a DELETE FK trigger planned? @ 2008-02-25 20:56 Andrew Lazarus <andrew@pillette.com> 2008-02-26 01:25 ` Re: when is a DELETE FK trigger planned? Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: Andrew Lazarus @ 2008-02-25 20:56 UTC (permalink / raw) To: pgsql-performance I have a cascading delete trigger that is obviously using a seqscan. (Explain analyze shows that trigger as taking over 1000s while all other triggers are <1s. The value in test delete didn't even appear in this child table, so an index scan would have been almost instant.) If I do DELETE FROM child_table WHERE fkey = value; I get an index scan. Why doesn't the trigger do that, and how can I force it to re-plan? ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: when is a DELETE FK trigger planned? 2008-02-25 20:56 when is a DELETE FK trigger planned? Andrew Lazarus <andrew@pillette.com> @ 2008-02-26 01:25 ` Tom Lane <tgl@sss.pgh.pa.us> 2008-02-28 00:54 ` Re: when is a DELETE FK trigger planned? Andrew Lazarus <andrew@pillette.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2008-02-26 01:25 UTC (permalink / raw) To: Andrew Lazarus <andrew@pillette.com>; +Cc: pgsql-performance Andrew Lazarus <andrew@pillette.com> writes: > I have a cascading delete trigger that is obviously using a seqscan. > (Explain analyze shows that trigger as taking over 1000s while all > other triggers are <1s. The value in test delete didn't even appear in > this child table, so an index scan would have been almost instant.) > If I do > DELETE FROM child_table WHERE fkey = value; > I get an index scan. Why doesn't the trigger do that, and how can I > force it to re-plan? That would depend on what PG version you're using. However, starting a fresh connection should get you a new trigger function plan in any case. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: when is a DELETE FK trigger planned? 2008-02-25 20:56 when is a DELETE FK trigger planned? Andrew Lazarus <andrew@pillette.com> 2008-02-26 01:25 ` Re: when is a DELETE FK trigger planned? Tom Lane <tgl@sss.pgh.pa.us> @ 2008-02-28 00:54 ` Andrew Lazarus <andrew@pillette.com> 0 siblings, 0 replies; 3+ messages in thread From: Andrew Lazarus @ 2008-02-28 00:54 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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. -- Sincerely, Andrew Lazarus mailto:andrew@pillette.com Attachments:^ permalink raw reply [nested|flat] 3+ messages in threadvCard.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
end of thread, other threads:[~2008-02-28 00:54 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2008-02-25 20:56 when is a DELETE FK trigger planned? Andrew Lazarus <andrew@pillette.com> 2008-02-26 01:25 ` Tom Lane <tgl@sss.pgh.pa.us> 2008-02-28 00:54 ` Andrew Lazarus <andrew@pillette.com>
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