X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id C7D5E528FF
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun, 31 Jul 2005 15:19:24 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 07626-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun, 31 Jul 2005 18:19:15 +0000 (GMT)
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.187])
	by svr1.postgresql.org (Postfix) with ESMTP id C9CF652825
	for <pgsql-performance@postgresql.org>;
	Sun, 31 Jul 2005 15:19:13 -0300 (ADT)
Received: from p548F13E4.dip0.t-ipconnect.de [84.143.19.228]
	(helo=pse.dyndns.org)
	by mrelayeu.kundenserver.de with ESMTP (Nemesis),
	id 0MKwtQ-1DzIP31j2N-0006Hv; Sun, 31 Jul 2005 20:19:13 +0200
Received: from pse1 ([192.168.0.3]) by pse.dyndns.org with esmtp (Exim 4.44)
	id 1DzIP1-0001z5-Vg; Sun, 31 Jul 2005 20:19:12 +0200
Message-ID: <42ED161F.1020408@pse-consulting.de>
Date: Sun, 31 Jul 2005 20:19:11 +0200
From: Andreas Pflug <pgadmin@pse-consulting.de>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: "Jim C. Nasby" <decibel@decibel.org>
Cc: Matthew Schumacher <matt.s@aptalaska.net>,
 pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
References: <1122532023.25574.1.camel@localhost.localdomain>
	<42E9749F.6000709@aptalaska.net>
	<1122616211.5691.340.camel@lamb.mcmillan.net.nz>
	<42EA6966.8090402@aptalaska.net>
	<1122668371.5691.352.camel@lamb.mcmillan.net.nz>
	<19405.1122670917@sss.pgh.pa.us> <42EAA410.9010700@aptalaska.net>
	<42EC612A.1080908@arbash-meinel.com>
	<42ECF3AE.6060809@arbash-meinel.com>
	<42ED017A.2050207@aptalaska.net>
	<20050731171012.GS60019@decibel.org>
In-Reply-To: <20050731171012.GS60019@decibel.org>
X-Enigmail-Version: 0.89.5.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: kundenserver.de abuse@kundenserver.de
	login:0ce7ee5c3478b8d72edd8e05ccd40b70
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.272 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200507/505
X-Sequence-Number: 13746

Jim C. Nasby wrote:
> On Sun, Jul 31, 2005 at 08:51:06AM -0800, Matthew Schumacher wrote:
> 
>>Ok, here is the current plan.
>>
>>Change the spamassassin API to pass a hash of tokens into the storage
>>module, pass the tokens to the proc as an array, start a transaction,
>>load the tokens into a temp table using copy, select the tokens distinct
>>into the token table for new tokens, update the token table for known
>>tokens, then commit.
> 
> 
> You might consider:
> UPDATE tokens
>     FROM temp_table (this updates existing records)
> 
> INSERT INTO tokens
>     SELECT ...
>     FROM temp_table
>     WHERE NOT IN (SELECT ... FROM tokens)
> 
> This way you don't do an update to newly inserted tokens, which helps
> keep vacuuming needs in check.

The subselect might be quite a big set, so avoiding a full table scan 
and materialization by

DELETE temp_table
   WHERE key IN (select key FROM tokens JOIN temp_table);
INSERT INTO TOKENS SELECT * FROM temp_table;

or

INSERT INTO TOKENS
SELECT temp_table.* FROM temp_table LEFT JOIN tokens USING (key)
WHERE tokens.key IS NULL

might be an additional win, assuming that only a small fraction of 
tokens is inserted and updated.

Regards,
Andreas