pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: PFC <lists@peufeu.com>
To: valgog <valgog@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
Date: Tue, 22 May 2007 12:14:48 +0200
Message-ID: <op.tsp32yfscigqcu@apollo13> (raw)
In-Reply-To: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>
References: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>

On Tue, 22 May 2007 10:23:03 +0200, valgog <valgog@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.



view thread (9+ messages)  latest in thread

Message-ID: <op.tsp32yfscigqcu@apollo13>
Permalink:  ../op.tsp32yfscigqcu@apollo13/
Also on:    postgresql.org/message-id/op.tsp32yfscigqcu@apollo13

 · 

reply
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: lists@peufeu.com, valgog@gmail.com
  Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
  In-Reply-To: <op.tsp32yfscigqcu@apollo13>

* 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