From: Peter Childs <peterachilds@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
Date: Tue, 22 May 2007 10:05:27 +0100
Message-ID: <a2de01dd0705220205l261ac149n6f168fbb6141480c@mail.gmail.com> (raw)
In-Reply-To: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>
References: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>
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
>
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: peterachilds@gmail.com
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
In-Reply-To: <a2de01dd0705220205l261ac149n6f168fbb6141480c@mail.gmail.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