Re: Performance problems testing with Spamassassin 3.1.0 Bayes module.

Matt,

> UPDATE bayes_vars SET
>   $token_count_update
>   newest_token_age = GREATEST(newest_token_age, ?),
>   oldest_token_age = LEAST(oldest_token_age, ?)
>   WHERE id = ?
>
>
> I think the reason why the procedure was written for postgres was
> because of the greatest and least statements performing poorly.

Well, it might be because we don't have a built-in GREATEST or LEAST prior to
8.1.   However, it's pretty darned easy to construct one.

> Honestly, I'm not real up on writing procs, I was hoping the problem
> would be obvious to someone.

Well, there's the general performance tuning stuff of course (postgresql.conf)
which if you've not done any of it will pretty dramatically affect your
througput rates.   And vacuum, analyze, indexes, etc.

You should also look at ways to make the SP simpler.  For example, you have a
cycle that looks like:

SELECT
    IF NOT FOUND
        INSERT
    ELSE
        UPDATE

Which could be made shorter as:

UPDATE
    IF NOT FOUND
        INSERT

... saving you one index scan.

Also, I don't quite follow it, but the procedure seems to be doing at least
two steps that the MySQL version isn't doing at all.  If the PG version is
doing more things, of course it's going to take longer.

Finally, when you have a proc you're happy with, I suggest having an expert
re-write it in C, which should double the procedure performance.

--
Josh Berkus
Aglio Database Solutions
San Francisco