Message-ID: <42E84021.9040904@aptalaska.net>
Date: Wed, 27 Jul 2005 18:17:05 -0800
From: Matthew Schumacher <matt.s@aptalaska.net>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
References: <42E80C28.2040608@aptalaska.net>
	<200507271712.15299.josh@agliodbs.com>
	<42E83C09.2040508@aptalaska.net>
	<200507271912.39780.josh@agliodbs.com>
In-Reply-To: <200507271912.39780.josh@agliodbs.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Josh Berkus wrote:
> Matt,
> 

> 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.

I was more talking about min() and max() but yea, I think you knew where
I was going with it...

> 
> 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.

I have gone though all that.

> 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.
> 

Sounds like I need to completely understand what the proc is doing and
work on a rewrite.  I'll look into writing it in C, I need to do some
reading about how that works and exactly what it buys you.

Thanks for the helpful comments.

schu