Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 35FA19FA31B for ; Tue, 22 May 2007 05:23:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 72793-01 for ; Tue, 22 May 2007 05:23:05 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 11F1A9F942C for ; Tue, 22 May 2007 05:23:08 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 9526C30909; Tue, 22 May 2007 10:23:05 +0200 (MET DST) From: valgog X-Newsgroups: pgsql.performance Subject: Key/Value reference table generation: INSERT/UPDATE performance Date: 22 May 2007 01:23:03 -0700 Organization: http://groups.google.com Lines: 61 Message-ID: <1179822183.740891.271130@n59g2000hsh.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Complaints-To: groups-abuse@google.com User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: n59g2000hsh.googlegroups.com; posting-host=62.220.8.122; posting-account=VhGBRA0AAAAPUct5VN1tGkfI2QuiqXqC To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200705/421 X-Sequence-Number: 24811 I found several post about INSERT/UPDATE performance in this group, but actually it was not really what I am searching an answer for... I have a simple reference table WORD_COUNTS that contains the count of words that appear in a word array storage in another table. CREATE TABLE WORD_COUNTS ( word text NOT NULL, count integer, CONSTRAINT PK_WORD_COUNTS PRIMARY KEY (word) ) WITHOUT OIDS; I have some PL/pgSQL code in a stored procedure like FOR r IN select id, array_of_words from word_storage LOOP begin -- insert the missing words insert into WORD_COUNTS ( word, count ) ( select word, 0 from ( select distinct (r.array_of_words) [s.index] as d_word from generate_series(1, array_upper( r.array_of_words, 1 ) ) as s(index) ) as distinct_words where word not in ( select d_word from WORD_COUNTS ) ); -- update the counts update WORD_COUNTS set count = COALESCE( count, 0 ) + 1 where word in ( select distinct (r.array_of_words)[s.index] as word from generate_series(1, array_upper( r.array_of_words, 1) ) as s(index) ); exception when others then error_count := error_count + 1; end; record_count := record_count + 1; END LOOP; This code runs extremely slowly. It takes about 10 minutes to process 10000 records and the word storage has more then 2 million records to be processed. Does anybody have a know-how about populating of such a reference tables and what can be optimized in this situation. Maybe the generate_series() procedure to unnest the array is the place where I loose the performance? Are the set update/inserts more effitient, then single inserts/updates run in smaller loops? Thanks for your help, Valentine Gogichashvili