From: Andreas Pflug <pgadmin@pse-consulting.de>
To: Jim C. Nasby <decibel@decibel.org>
Cc: Matthew Schumacher <matt.s@aptalaska.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
Date: Sun, 31 Jul 2005 20:19:11 +0200
Message-ID: <42ED161F.1020408@pse-consulting.de> (raw)
In-Reply-To: <20050731171012.GS60019@decibel.org>
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>
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
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: pgadmin@pse-consulting.de, decibel@decibel.org, matt.s@aptalaska.net
Subject: Re: Performance problems testing with Spamassassin 3.1.0
In-Reply-To: <42ED161F.1020408@pse-consulting.de>
* 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