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 4470C14B281F for ; Tue, 10 Aug 2004 23:31:35 -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 53761-02 for ; Wed, 11 Aug 2004 02:31:30 +0000 (GMT) Received: from smtp.gvtc.com (smtp.gvtc.com [216.177.160.80]) by svr1.postgresql.org (Postfix) with ESMTP id 6FF4D14B281E for ; Tue, 10 Aug 2004 23:31:13 -0300 (ADT) Received: from [192.168.1.102] (unknown [12.181.66.221]) by smtp.gvtc.com (Postfix) with ESMTP id 6DAC91939C3 for ; Tue, 10 Aug 2004 21:31:17 -0500 (CDT) Message-ID: <41198514.1020103@gvtc.com> Date: Tue, 10 Aug 2004 21:31:48 -0500 From: Jim J User-Agent: Mozilla Thunderbird 0.7.3 (X11/20040803) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Bulk Insert and Index use References: <41195462.9090303@wildcash.com> In-Reply-To: <41195462.9090303@wildcash.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/141 X-Sequence-Number: 7777 If the bulk load has the possibility of duplicating data, then you need to change methods. Try bulk loading into a temp table, index it like the original, eliminate the dups and merge the tables. It is also possible to do an insert from the temp table into the final table like: insert into original (x,x,x) (select temp.1, temp.2, etc from temp left join original on temp.street=original.street where original.street is null) Good Luck Jim Rudi Starcevic wrote: > Hi, > > I have a question on bulk checking, inserting into a table and > how best to use an index for performance. > > The data I have to work with is a monthly CD Rom csv data dump of > 300,000 property owners from one area/shire. > > So every CD has 300,000 odd lines, each line of data which fills the > 'property' table. > > Beginning with the first CD each line should require one SELECT and > one INSERT as it will be the first property with this address. > > The SELECT uses fields like 'street' and 'suburb', to check for an > existing property, > so I have built an index on those fields. > > My question is does each INSERT rebuild the index on the 'street' and > 'suburb' fields? > I believe it does but I'm asking to be sure. > > If this is the case I guess performance will suffer when I have, say, > 200,000 > rows in the table. > > Would it be like: > > a) Use index to search on 'street' and 'suburb' > b) No result? Insert new record > c) Rebuild index on 'street' and 'suburb' > > for each row? > Would this mean that after 200,000 rows each INSERT will require > the index of 000's of rows to be re-indexed? > > So far I believe my only options are to use either and index > or sequential scan and see which is faster. > > A minute for your thoughts and/or suggestions would be great. > > Thanks. > Regards, > Rudi. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >