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