X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C6F875E46BF for ; Tue, 17 Aug 2004 20:03:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92032-08 for ; Tue, 17 Aug 2004 23:03:34 +0000 (GMT) Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by svr1.postgresql.org (Postfix) with ESMTP id 2D6635E46C4 for ; Tue, 17 Aug 2004 20:03:31 -0300 (ADT) Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6106436; Tue, 17 Aug 2004 16:04:50 -0700 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Frank van Vugt Subject: Re: Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)? Date: Tue, 17 Aug 2004 16:03:43 -0700 User-Agent: KMail/1.6.2 Cc: pgsql-performance@postgresql.org References: <200408172329.52555.ftm.van.vugt@foxi.nl> In-Reply-To: <200408172329.52555.ftm.van.vugt@foxi.nl> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200408171603.43236.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/265 X-Sequence-Number: 7901 Frank, > It seems in this case the time needed for a single deferred trigger somehow > depends on the number of dead tuples in the table, because a vacuum of the > table will 'reset' the query-times. However, even if I wanted to, vacuum is > not allowed from within a function. > > What is happening here? And more importantly, what can I do to prevent > this? I'm not clear on all of the work you're doing in the trigger. However, it seems obvious that you're deleting and/or updating a large number of rows. The escalating execution times would be consistent with that. > NB. My real-world application 'collects' id's in need for deferred work, > but this work is both costly and only needed once per base record. So I use > an 'update' table whose content I join with the actual tables in order to > do the work for _all_ the base records involved upon the first execution of > the deferred trigger. At the end of the trigger, this 'update' table is > emptied so any additional deferred triggers on the same table will hardly > lose any time. Or at least, that was the intention.... I think you're doing a lot more than is wise to do in triggers. Deferrable triggers aren't really intended for running long procedures with the creation of types and temporary tables (your post got a bit garbled, so pardon me if I'm misreading it). I'd suggest reconsidering your approach to this application problem. At the very least, increase max_fsm_relations to some high value, which may help (or not). -Josh -- __Aglio Database Solutions_______________ Josh Berkus Consultant josh@agliodbs.com www.agliodbs.com Ph: 415-752-2500 Fax: 415-752-2387 2166 Hayes Suite 200 San Francisco, CA