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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to