From: cedric <cedric.villemain@dalibo.com>
Organization: dalibo.com
To: pgsql-performance@postgresql.org
Subject: Re: Key/Value reference table generation: INSERT/UPDATE performance
Date: Tue, 22 May 2007 14:28:05 +0200
User-Agent: KMail/1.9.6
Cc: Richard Huxton <dev@archonet.com>, valgog <valgog@gmail.com>
References: <1179822183.740891.271130@n59g2000hsh.googlegroups.com>
	<4652B627.1070109@archonet.com>
In-Reply-To: <4652B627.1070109@archonet.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200705221428.09546.cedric.villemain@dalibo.com>

Le mardi 22 mai 2007, Richard Huxton a =E9crit=A0:
> valgog 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.
>
> I think this is the root of your problem, I'm afraid. You're trying to
> count individual words when you're storing an array of words. I don't
> think any of the Gist/GIN indexes will help you with this either.
>
> However, since "you don't want to start from here" isn't very useful
> here and now:
>
> 1. See what the performance (explain analyse) of the "select
> distinct...generate_series()" statement is. I think you're right and
> it's going to be slow.
> 2. You're looping through each row of word_storage and counting
> separately. Write it as one query if possible.
> 3. As Peter says, don't insert then update, start with an empty table
> and just insert totals for the lot (see #2).
>
> I'd probably write the query in plperl/python or something else that
> supports hash/dictionary structures. Then just process the whole
> word_storage into the hash - assuming you only have a few thousand
> distinct words that shouldn't take up too much memory.
+1
I made something very similar, and using PL/pgsql is very slow, when using=
=20
perl is very quick.=20

I have also use partioning because of cost of update (copy last partition t=
o=20
the new, adding the new count, so there is only insert, and drop old table =
if=20
you want)