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 42C3852A88 for ; Tue, 19 Jul 2005 17:45:06 -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 61554-02 for ; Tue, 19 Jul 2005 20:45:04 +0000 (GMT) Received: from smtp-gw-cl-c.dmv.com (smtp-gw-cl-c.dmv.com [216.240.97.41]) by svr1.postgresql.org (Postfix) with ESMTP id 3770352A75 for ; Tue, 19 Jul 2005 17:45:02 -0300 (ADT) Received: from lanshark.dmv.com (lanshark.dmv.com [216.240.97.46]) by smtp-gw-cl-c.dmv.com (8.12.10/8.12.10) with ESMTP id j6JKivYY034048; Tue, 19 Jul 2005 16:44:57 -0400 (EDT) (envelope-from sven@dmv.com) Subject: Re: Looking for tips From: Sven Willenberger To: Oliver Crosby Cc: Dawid Kuroczko , Kevin Grittner , jd@commandprompt.com, pgsql-performance@postgresql.org In-Reply-To: <1efd553a050719132836c31b78@mail.gmail.com> References: <1efd553a0507191308219a098b@mail.gmail.com> <758d5e7f0507191319636afaa3@mail.gmail.com> <1efd553a050719132836c31b78@mail.gmail.com> Content-Type: text/plain Date: Tue, 19 Jul 2005 16:46:01 -0400 Message-Id: <1121805961.3674.25.camel@lanshark.dmv.com> Mime-Version: 1.0 X-Mailer: Evolution 2.2.1.1 Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.39 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.327 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200507/326 X-Sequence-Number: 13567 On Tue, 2005-07-19 at 16:28 -0400, Oliver Crosby wrote: > > If it is possible try: > > 1) wrapping many inserts into one transaction > > (BEGIN;INSERT;INSERT;...INSERT;COMMIT;). As PostgreSQL will need to > > handle less transactions per second (each your insert is a transaction), it > > may work faster. > > Aye, that's what I have it doing right now. The transactions do save a > HUGE chunk of time. (Cuts it down by about 40%). > > > 2) If you can do 1, you could go further and use a COPY command which is > > the fastest way to bulk-load a database. > > I don't think I can use COPY in my case because I need to do > processing on a per-line basis, and I need to check if the item I want > to insert is already there, and if it is, I need to get it's ID so I > can use that for further processing. > since triggers work with COPY, you could probably write a trigger that looks for this condition and does the ID processsing you need; you could thereby enjoy the enormous speed gain resulting from COPY and maintain your data continuity. Sven