From: Matthew Schumacher <matt.s@aptalaska.net>
To: John A Meinel <john@arbash-meinel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
Date: Thu, 04 Aug 2005 08:13:35 -0800
Message-ID: <42F23EAF.2070603@aptalaska.net> (raw)
In-Reply-To: <42F221BB.5000702@arbash-meinel.com>
References: <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>
<27897.1122748133@sss.pgh.pa.us>
<42EE8043.1050207@aptalaska.net>
<op.suulgxokth1vuj@localhost>
<42EE93F8.5010003@aptalaska.net>
<20050801214931.GF60019@decibel.org>
<42EE9D83.8000405@pobox.com>
<42F1CE43.8040801@aptalaska.net>
<42F221BB.5000702@arbash-meinel.com>
John A Meinel wrote:
> Matthew Schumacher wrote:
>
> I recommend that you drop and re-create the temp table. There is no
> reason to have it around, considering you delete and re-add everything.
> That means you never have to vacuum it, since it always only contains
> the latest rows.
Whenever I have a create temp and drop statement I get these errors:
select put_tokens(1, '{"\\\\000"}', 1, 1, 1000);
ERROR: relation with OID 582248 does not exist
CONTEXT: SQL statement "INSERT INTO bayes_token_tmp VALUES ( $1 )"
PL/pgSQL function "put_tokens" line 12 at SQL statement
>
>
>
> My one question here, is the inspam_count and inham_count *always* the
> same for all tokens? I would have thought each token has it's own count.
> Anyway, there are a few lines I would change:
No, we get the userid, inspam, inham, and atime, and they are the same
for each token. If we have a different user we call the proc again.
> -- create the table at the start of the procedure
> CREATE TEMP TABLE bayes_token_tmp (intoken bytea);
> -- You might also add primary key if you are going to be adding
> -- *lots* of entries, but it sounds like you are going to have
> -- less than 1 page, so it doesn't matter
This causes errors, see above....
> -- (token) IN (SELECT intoken FROM bayes_token_tmp);
> EXISTS (SELECT token FROM bayes_token_tmp
> WHERE intoken=token LIMIT 1);
>
> -- I would also avoid your intoken (NOT) IN (SELECT token FROM
> -- bayes_token) There are a few possibilities, but to me
> -- as your bayes_token table becomes big, this will start
> -- to be the slow point
>
> -- Rather than doing 2 NOT IN queries, it *might* be faster to do
> DELETE FROM bayes_token_tmp
> WHERE NOT EXISTS (SELECT token FROM bayes_token
> WHERE token=intoken);
>
>
I'll look into this.
thanks,
schu
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: matt.s@aptalaska.net, john@arbash-meinel.com
Subject: Re: Performance problems testing with Spamassassin 3.1.0
In-Reply-To: <42F23EAF.2070603@aptalaska.net>
* 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