From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Matthew Schumacher <matt.s@aptalaska.net>
Subject: Re: Performance problems testing with Spamassassin 3.1.0 Bayes
 module.
Date: Wed, 27 Jul 2005 19:12:39 -0700
User-Agent: KMail/1.8
Cc: pgsql-performance@postgresql.org
References: <42E80C28.2040608@aptalaska.net>
	<200507271712.15299.josh@agliodbs.com>
	<42E83C09.2040508@aptalaska.net>
In-Reply-To: <42E83C09.2040508@aptalaska.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200507271912.39780.josh@agliodbs.com>

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