Skip site navigation (1) Skip section navigation (2)

Key/Value reference table generation: INSERT/UPDATE performance

From: valgog <valgog(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Key/Value reference table generation: INSERT/UPDATE performance
Date: 2007-05-22 08:23:03
Message-ID: 1179822183.740891.271130@n59g2000hsh.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


Responses

pgsql-performance by date

Next:From: Peter ChildsDate: 2007-05-22 09:05:27
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
Previous:From: Gregory StarkDate: 2007-05-22 08:16:56
Subject: Re: Postgres Benchmark Results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group