From: Matthew Schumacher <matt.s@aptalaska.net>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
Date: Wed, 27 Jul 2005 18:17:05 -0800
Message-ID: <42E84021.9040904@aptalaska.net> (raw)
In-Reply-To: <200507271912.39780.josh@agliodbs.com>
References: <42E80C28.2040608@aptalaska.net>
<200507271712.15299.josh@agliodbs.com>
<42E83C09.2040508@aptalaska.net>
<200507271912.39780.josh@agliodbs.com>
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
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, josh@agliodbs.com
Subject: Re: Performance problems testing with Spamassassin 3.1.0
In-Reply-To: <42E84021.9040904@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