X-Original-To: pgsql-patches-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4DF795280C for ; Tue, 19 Jul 2005 18:06:15 -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 59079-09 for ; Tue, 19 Jul 2005 21:06:05 +0000 (GMT) Received: from mail.Mi8.com (mail.mi8.com [63.240.6.41]) by svr1.postgresql.org (Postfix) with ESMTP id 2E9A152A1F for ; Tue, 19 Jul 2005 18:06:03 -0300 (ADT) Received: from 172.16.1.112 by mail.Mi8.com with ESMTP (- GW01 Welcome to Mi8 Corporation www.Mi8.com); Tue, 19 Jul 2005 17:05:55 -0400 X-Server-Uuid: F1A2E19A-84E4-48DD-8F48-B475613F58B2 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.175]) by D01SMTP02.Mi8.com with Microsoft SMTPSVC(6.0.3790.211); Tue, 19 Jul 2005 17:05:55 -0400 Received: from 67.103.45.218 ([67.103.45.218]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.106]) with Microsoft Exchange Server HTTP-DAV ; Tue, 19 Jul 2005 17:05:54 -0500 User-Agent: Microsoft-Entourage/11.1.0.040913 Date: Tue, 19 Jul 2005 14:05:56 -0700 Subject: Re: COPY FROM performance improvements From: "Alon Goldshuv" To: "Mark Wong" Cc: pgsql-patches@postgresql.org Message-ID: In-Reply-To: <200507191954.j6JJsVjA003280@smtp.osdl.org> MIME-Version: 1.0 X-OriginalArrivalTime: 19 Jul 2005 21:05:55.0052 (UTC) FILETIME=[A713EAC0:01C58CA5] X-WSS-ID: 6EC3B4B91J47477295-01-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.598 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, RCVD_NUMERIC_HELO X-Spam-Level: * X-Archive-Number: 200507/384 X-Sequence-Number: 16724 Hi Mark, I improved the data *parsing* capabilities of COPY, and didn't touch the data conversion or data insertion parts of the code. The parsing improvement will vary largely depending on the ratio of parsing -to- converting and inserting. Therefore, the speed increase really depends on the nature of your data: 100GB file with long data rows (lots of parsing) Small number of columns (small number of attr conversions per row) less rows (less tuple insertions) Will show the best performance improvements. However, same file size 100GB with Short data rows (minimal parsing) large number of columns (large number of attr conversions per row) AND/OR more rows (more tuple insertions) Will show improvements but not as significant. In general I'll estimate 40%-95% improvement in load speed for the 1st case and 10%-40% for the 2nd. But that also depends on the hardware, disk speed etc... This is for TEXT format. As for CSV, it may be faster but not as much as I specified here. BINARY will stay the same as before. HTH Alon. On 7/19/05 12:54 PM, "Mark Wong" wrote: > On Thu, 14 Jul 2005 17:22:18 -0700 > "Alon Goldshuv" wrote: > >> I revisited my patch and removed the code duplications that were there, and >> added support for CSV with buffered input, so CSV now runs faster too >> (although it is not as optimized as the TEXT format parsing). So now >> TEXT,CSV and BINARY are all parsed in CopyFrom(), like in the original file. > > Hi Alon, > > I'm curious, what kind of system are you testing this on? I'm trying to > load 100GB of data in our dbt3 workload on a 4-way itanium2. I'm > interested in the results you would expect. > > Mark >