Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id B529663283E for ; Thu, 15 Jul 2010 20:01:46 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 32181-05 for ; Thu, 15 Jul 2010 23:01:38 +0000 (UTC) X-Greylist: delayed 00:06:40.615237 by SQLgrey-1.7.6 Received: from 27.mail-out.ovh.net (27.mail-out.ovh.net [91.121.30.210]) by mail.postgresql.org (Postfix) with SMTP id 311736327B5 for ; Thu, 15 Jul 2010 20:01:38 -0300 (ADT) Received: (qmail 4037 invoked by uid 503); 15 Jul 2010 22:56:41 -0000 Received: from b7.ovh.net (HELO mail437.ha.ovh.net) (213.186.33.57) by 27.mail-out.ovh.net with SMTP; 15 Jul 2010 22:56:41 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 16 Jul 2010 00:54:55 +0200 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13) (lists%peufeu.com@88.161.102.87) by ns0.ovh.net with SMTP; 16 Jul 2010 00:54:54 +0200 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: "Benjamin Krajmalnik" , pgsql-performance@postgresql.org Subject: Re: Question of using COPY on a table with triggers References: Date: Fri, 16 Jul 2010 00:46:54 +0200 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit From: "Pierre C" Message-ID: In-Reply-To: User-Agent: Opera Mail/10.10 (Linux) X-Ovh-Tracer-Id: 10501831382766847713 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.001 tagged_above=-10 required=5 tests=BAYES_40=-0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201007/249 X-Sequence-Number: 39638 > Essentially, we insert a set of columns into a table, and each row fires > a trigger function which calls a very large stored procedure For inserting lots of rows, COPY is much faster than INSERT because it parses data (a lot) faster and is more "data-stream-friendly". However the actual inserting into the tbale and trigger-calling has to be done for both. If the trigger is a "very large stored procedure" it is very likely that executing it will take a lot more time than parsing & executing the INSERT. So, using COPY instead of INSERT will not gain you anything.