DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=lZ35IhpvGmNGAB0kmagsixbcSlCfWlBD/K8Rvkt8hgl/SDzSOV3PMhnMhv8RkDKhnNR0XLtarK13aV/1tz4v3wCh14mkeGd18uuVBdGII1+dNECo4lGcfC8wW+RmPFpMYxmqxuievqw0hO+FqUuopGBnkfYNKQvTur2oxnOOQkg=
Message-ID: <1d4e0c10705230104o30db1cf6x86207a96b23f823a@mail.gmail.com>
Date: Wed, 23 May 2007 10:04:24 +0200
From: "Guillaume Smet" <guillaume.smet@gmail.com>
To: cedric <cedric.villemain@dalibo.com>
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
Cc: pgsql-performance@postgresql.org, "Richard Huxton" <dev@archonet.com>,
	valgog <valgog@gmail.com>
In-Reply-To: <200705221428.09546.cedric.villemain@dalibo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>
	<4652B627.1070109@archonet.com>
	<200705221428.09546.cedric.villemain@dalibo.com>

On 5/22/07, cedric <cedric.villemain@dalibo.com> wrote:
> I made something very similar, and using PL/pgsql is very slow, when using
> perl is very quick.

Another solution is to use tsearch2 for that:
CREATE TABLE word_counts AS SELECT * FROM stat('SELECT
to_tsvector(''simple'', lower(coalesce(field containing words, '''')))
FROM your table');

I don't know if the fact you have an array of words is a must have or
just a design choice. If you have to keep that, you can transform the
array easily into a string with array_to_string and use the same sort
of query.

I don't know what are exactly your speed requirements but it's quite
fast here. If you drop your table and recreate it into a transaction,
it should work like a charm (or you can use TRUNCATE and INSERT INTO).

--
Guillaume