Re: Key/Value reference table generation: INSERT/UPDATE performance

["Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>]

On 5/22/07, cedric <cedric(dot)villemain(at)dalibo(dot)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