From: valgog <valgog@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
Date: 22 May 2007 03:38:06 -0700
Message-ID: <1179830285.972069.141610@a26g2000pre.googlegroups.com> (raw)
In-Reply-To: <1179828041.761272.152380@x35g2000prf.googlegroups.com>
References: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>
<1179828041.761272.152380@x35g2000prf.googlegroups.com>
On May 22, 12:00 pm, valgog <val...@gmail.com> wrote:
> I have rewritten the code like
>
> existing_words_array := ARRAY( select word
> from WORD_COUNTS
> where word = ANY
> ( array_of_words ) );
> not_existing_words_array := ARRAY( select distinct_word
> from ( select distinct
> (array_of_words)[s.index] as distinct_word
> from
> generate_series(1, array_upper( array_of_words, 1 ) ) as s(index)
> ) as distinct_words
> where distinct_word <> ALL
> ( existing_words_array ) );
> -- insert the missing words
> if not_existing_words_array is not null then
> insert into WORD_COUNTS
> ( word, count )
> ( select word, 1
> from ( select
> not_existing_words_array[s.index] as word
> from generate_series( 1,
> array_upper( not_existing_words_array, 1 ) ) as s(index) ) as
> distinct_words
> );
> end if;
> -- update the counts
> if existing_words_array is not null then
> update WORD_COUNTS
> set count = COALESCE( count, 0 ) + 1
> where sw_word = ANY ( existing_words_array );
> end if;
>
> Now it processes a million records in 14 seconds... so it was probably
> the problem of looking up NOT IN WORD_COUNTS was way too expencive
Sorry... this code did not update anythig at all, as I forgot about
the NULL values... had to COALASCE practically everything and use
array_upper()... do not have the performance numbers of the insert,
updates yet...
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: valgog@gmail.com
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
In-Reply-To: <1179830285.972069.141610@a26g2000pre.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