X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 15B18D1B4EF for ; Mon, 27 Oct 2003 18:01:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 96060-08 for ; Mon, 27 Oct 2003 14:01:13 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 715DCD1B515 for ; Mon, 27 Oct 2003 14:01:12 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 835B8369CE; Mon, 27 Oct 2003 13:01:12 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1AEBg0-0005Ij-00; Mon, 27 Oct 2003 13:01:12 -0500 To: Damien Dougan Cc: pgsql-performance@postgresql.org Subject: Re: Very Poor Insert Performance References: <200310271626.52839.damien.dougan@mobilecohesion.com> In-Reply-To: <200310271626.52839.damien.dougan@mobilecohesion.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 27 Oct 2003 13:01:12 -0500 Message-ID: <871xsyy2zb.fsf@stark.dyndns.tv> Lines: 16 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/686 X-Sequence-Number: 4434 Damien Dougan writes: > Our batch upload is performing a number of stored procedures to insert data on > the database. Initially, this results in quite good performance, but rapidly > spirals down to approximately 1 per second after some minutes. It's fairly unlikely anyone will be able to help without you saying what you're doing. What are these procedures doing? What do the tables look like? What indexes exist? At a guess the foreign key relationships you're enforcing don't have indexes to help them. If they do perhaps postgres isn't using them. -- greg