Re: Performance Question Followup No.2 - Mailing list pgsql-general

From Gordan Bobic
Subject Re: Performance Question Followup No.2
Date
Msg-id 200111071815.fA7IF0M05546@sentinel.bobich.net
Whole thread Raw
In response to Re: Performance Question Followup No.2  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: [pgadmin-hackers] Fwd: Re: Howto change column
Next
From: Gordan Bobic
Date:
Subject: Re: More Performance Questions