From: Guillaume Smet <guillaume.smet@gmail.com>
To: cedric <cedric.villemain@dalibo.com>
Cc: pgsql-performance@postgresql.org, "Richard Huxton" <dev@archonet.com>
Cc: valgog <valgog@gmail.com>
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
Date: Wed, 23 May 2007 10:04:24 +0200
Message-ID: <1d4e0c10705230104o30db1cf6x86207a96b23f823a@mail.gmail.com> (raw)
In-Reply-To: <200705221428.09546.cedric.villemain@dalibo.com>
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
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: guillaume.smet@gmail.com, cedric.villemain@dalibo.com, dev@archonet.com, valgog@gmail.com
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
In-Reply-To: <1d4e0c10705230104o30db1cf6x86207a96b23f823a@mail.gmail.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