From: valgog <valgog@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Key/Value reference table generation: INSERT/UPDATE performance
Date: 22 May 2007 01:23:03 -0700
Message-ID: <1179822183.740891.271130@n59g2000hsh.googlegroups.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: valgog@gmail.com
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
In-Reply-To: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>
References: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox