Re: Key/Value reference table generation: INSERT/UPDATE performance
От | valgog |
---|---|
Тема | Re: Key/Value reference table generation: INSERT/UPDATE performance |
Дата | |
Msg-id | 1179828041.761272.152380@x35g2000prf.googlegroups.com обсуждение исходный текст |
Ответ на | Key/Value reference table generation: INSERT/UPDATE performance (valgog <valgog@gmail.com>) |
Ответы |
Re: Key/Value reference table generation: INSERT/UPDATE performance
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: