Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 4D9619FA2FD for ; Tue, 22 May 2007 06:05:42 -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 37146-06 for ; Tue, 22 May 2007 06:05:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id E458F9FA2B7 for ; Tue, 22 May 2007 06:05:29 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so149744ugf for ; Tue, 22 May 2007 02:05:28 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=WeBSm6TqWWCGZjdpbWdyJWkI2azo6qyFxefZ132aNBCBvwj+WbGdfmfPzSnlDRaElGXaxxvaablp4nVH3zr3GYMZ3lCpvfeYp/uK+3S5PhuLPLLdl58UPeS26uAiY3xPiukftYVAoDYRN4a6Tt58ktiB7hhwseHPHG9bLpXguVI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=Oq4kTcv77ijdk8jBXoay1BhjW2g2fkA7+NPKsv3VDyHvIpT49BFqoFR940I8g30q601VHSLvRuRJxE3QeReSpJNaAveA7zsxNUK5Y3Vr6uyfeCdCHZS8vwUtuS2oaMy3SHuAjRJ3CGosU1717kl5lP/jDNbV8l28/uy0MjmzSmw= Received: by 10.78.170.17 with SMTP id s17mr1342665hue.1179824728253; Tue, 22 May 2007 02:05:28 -0700 (PDT) Received: by 10.78.150.10 with HTTP; Tue, 22 May 2007 02:05:27 -0700 (PDT) Message-ID: Date: Tue, 22 May 2007 10:05:27 +0100 From: "Peter Childs" Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance Cc: pgsql-performance@postgresql.org In-Reply-To: <1179822183.740891.271130@n59g2000hsh.googlegroups.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_54012_7276200.1179824727805" References: <1179822183.740891.271130@n59g2000hsh.googlegroups.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/422 X-Sequence-Number: 24812 ------=_Part_54012_7276200.1179824727805 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 22 May 2007 01:23:03 -0700, valgog wrote: > > 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; Is there any reason why count is not not null? (That should siplify your code by removing the coalesce) insert is more efficient than update because update is always a delete followed by an insert. Oh and group by is nearly always quicker than distinct and can always? be rewritten as such. I'm not 100% sure why its different but it is. Peter. 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > ------=_Part_54012_7276200.1179824727805 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 22 May 2007 01:23:03 -0700, valgog <valgog@gmail.com> wrote:
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;


Is there any reason why count is not not null? (That should siplify your code by removing the coalesce)

insert is more efficient than update because update is always a delete followed by an insert.

Oh and group by is nearly always quicker than distinct and can always? be rewritten as such. I'm not 100% sure why its different but it is.

Peter.



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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

------=_Part_54012_7276200.1179824727805--