On Wednesday 07 Nov 2001 17:30, Stephan Szabo wrote:
> On Wed, 7 Nov 2001, Gordan Bobic wrote:
> > After just having split the action into two parts (FTI delete + Master
> > delete), it would appear that most of the delay does come from the
> > triggers executing.
> >
> > delete from masterfti where masterfti.id = master.id and master.entrytime
> > < '31-Oct-2001';
> > DELETE 172609
> >
> > Came back in about 10 seconds.
> >
> > delete from master where entrytime < '31-Oct-2001';
> >
> > is still going after 10 minutes even though there are no records in the
> > FTI table that are being deleted.
> >
> > Is there an explanation for this? Anything that I should check? Any
> > performance tweaks that would improve this situation?
> >
> > Sorry to go on about this, but I'm totally puzzled by this.
>
> Have you tried restarting your connection and doing the set for the
> sequence scan and then doing the first delete? The foreign key
> stuff caches a query plan the first time IIRC, so if you do the
> set after it's run once, it isn't going to change its plan.
Yes, I did, but with triggers enabled, doing bulk cascaded deletes just takes
too long. I have now removed all the triggers and am handling things at the
application level. It is by no means as neat, tidy and elegant as triggers,
but it is worth it for a several hundred fold performance improvement.
Thanks for telling me about the foreign key caching, though. I can see how
that could cause interesting performance tuning situations. :-)
Gordan