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 658F614B2869 for ; Wed, 11 Aug 2004 00:36:08 -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 95343-09 for ; Wed, 11 Aug 2004 03:36:03 +0000 (GMT) Received: from diablo.OntheNet.com.au (diablo.OntheNet.com.au [203.10.89.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9F59814B2827 for ; Wed, 11 Aug 2004 00:35:59 -0300 (ADT) Received: from [192.168.0.114] (imp3.dsl.onthenet.net [203.144.21.139]) by diablo.OntheNet.com.au (8.12.10/8.12.10) with ESMTP id i7B3Zn4G012260 for ; Wed, 11 Aug 2004 13:35:50 +1000 (EST) Message-ID: <4119938D.6000609@wildcash.com> Date: Wed, 11 Aug 2004 13:33:33 +1000 From: Rudi Starcevic Reply-To: tech@wildcash.com Organization: Internet Media Productions User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7) Gecko/20040616 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> <41198514.1020103@gvtc.com> In-Reply-To: <41198514.1020103@gvtc.com> X-Enigmail-Version: 0.85.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=us-ascii; 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/143 X-Sequence-Number: 7779 Hi Jim, Thanks for your time. > If the bulk load has the possibility of duplicating data Yes, each row will require either: a) One SELECT + One INSERT or b) One SELECT + One UPDATE I did think of using more than one table, ie. temp table. As each month worth of data is added I expect to see a change from lots of INSERTS to lots of UPDATES. Perhaps when the UPDATES become more dominant it would be best to start using Indexes. While INSERTS are more prevelant perhaps a seq. scan is better. I guess of all the options available it boils down to which is quicker for my data: index or sequential scan. Many thanks. Jim J wrote: > 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 >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > -- Regards, Rudi. Internet Media Productions