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

Re: Key/Value reference table generation: INSERT/UPDATE performance

From: valgog <valgog(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
Date: 2007-05-22 10:38:06
Message-ID: 1179830285.972069.141610@a26g2000pre.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-performance
On May 22, 12:00 pm, valgog <val(dot)(dot)(dot)(at)gmail(dot)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...


In response to

pgsql-performance by date

Next:From: ArnauDate: 2007-05-22 10:39:02
Subject: Performace comparison of indexes over timestamp fields
Previous:From: valgogDate: 2007-05-22 10:35:29
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance

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