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:35:29 -0700
Message-ID: <1179830129.622698.240380@x35g2000prf.googlegroups.com> (raw)
In-Reply-To: <op.tsp32yfscigqcu@apollo13>
References: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>
<op.tsp32yfscigqcu@apollo13>
On May 22, 12:14 pm, l...@peufeu.com (PFC) wrote:
> On Tue, 22 May 2007 10:23:03 +0200, valgog <val...@gmail.com> 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.
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: <1179830129.622698.240380@x35g2000prf.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