X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4A738475E9D for ; Sat, 22 Feb 2003 01:47:41 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 69142-06 for ; Sat, 22 Feb 2003 01:47:30 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 13A47476067 for ; Fri, 21 Feb 2003 23:23:48 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1M4Nl5u002704; Fri, 21 Feb 2003 23:23:48 -0500 (EST) To: "scott.marlowe" Cc: Kevin White , pgsql-performance@postgresql.org Subject: Re: Really bad insert performance: what did I do wrong? In-reply-to: References: Comments: In-reply-to "scott.marlowe" message dated "Fri, 21 Feb 2003 10:55:41 -0700" Date: Fri, 21 Feb 2003 23:23:47 -0500 Message-ID: <2703.1045887827@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/164 X-Sequence-Number: 1220 "scott.marlowe" writes: > 3: Inserting ALL 700,000 rows in one transaction is probably not optimal. > Try putting a test in every 1,000 or 10,000 rows to toss a "commit;begin;" > pair at the database while loading. Inserting all 700,000 rows at once > means postgresql can't recycle the transaction logs, so you'll have > 700,000 rows worth of data in the transaction logs waiting for you to > commit at the end. That was true in 7.1.0, but we got rid of that behavior *very* quickly (by 7.1.3, according to the release notes). Long transactions do not currently stress the WAL storage any more than the same amount of work in short transactions. Which is not to say that there's anything wrong with divvying the work into 1000-row-or-so transactions. I agree that that's enough to push the per-transaction overhead down into the noise. regards, tom lane