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
