On May 22, 12:14 pm, [EMAIL PROTECTED] (PFC) wrote: > On Tue, 22 May 2007 10:23:03 +0200, valgog <[EMAIL PROTECTED]> 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. > > Mmm. > > If I were you, I would : > > - Create a procedure that flattens all the arrays and returns all the > > words : > > PROCEDURE flatten_arrays RETURNS SETOF TEXT > FOR word_array IN SELECT word_array FROM your_table LOOP > FOR i IN 1...array_upper( word_array ) LOOP > RETURN NEXT tolower( word_array[ i ] ) > > So, SELECT * FROM flatten_arrays() returns all the words in all the arrays. > To get the counts quickly I'd do this : > > SELECT word, count(*) FROM flatten_arrays() AS word GROUP BY word > > You can then populate your counts table very easily and quickly, since > it's just a seq scan and hash aggregate. One second for 10.000 rows would > be slow. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
good idea indeed! will try this approach. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
