Re: [dspam-users] Postgres vs. MySQL - Mailing list pgsql-performance

I posted about this a couple days ago on dspam-dev...

I am using DSpam with PostgreSQL, and like you discovered the horrible
performance.  The reason is because the default PostgreSQL query planner
settings determine that a sequence scan will be more efficient than an
index scan, which is wrong.  To correct this behavior, adjust the query
planner settings for the appropriate table/column with this command:

alter table "dspam_token_data" alter "token" set statistics 200; analyze;

Let me know if it help you.  It worked wonders for me.

--
Casey Allen Shobe
cshobe@osss.net

On Fri, November 26, 2004 12:35 pm, Clifton Royston said:
> On Wed, Nov 24, 2004 at 02:14:18PM +0100, Evilio del Rio wrote:
>> I have installed the dspam filter
>> (http://www.nuclearelephant.com/projects/dspam) on our mail server
>> (RedHat 7.3 Linux with sendmail 8.13 and procmail). I have ~300 users
>> with a quite low traffic of 4000 messages/day. So it's a quite common
>> platform/environment, nothing spectacular.
>>
>> First time(s) I tried the Postgres interface that was already installed
>> for other applications.  Whenever I begin to train and/or filter
>> messages throug dspam the performance is incredibly bad. First messages
>> are ok but soon the filter time begins to increase to about 30 seconds
>> or more!
>>
>> ...so I looked for some optimization both for the linux  kernel and the
>> postgres server.  Nothing has work for me. I always have the same
>> behavior. For isolation purposes I started using another server just to
>> hold the dspam database and nothing else. No matter what I do: postgres
>> gets slower and slower with each new message fed or filtered.
>
> I know *somewhere* I recently read something indicating a critical
> configuration change for DSPAM + Postgres, but don't think I've seen it
> mentioned on this list.  Possibly it is in the UPGRADING instructions
> for 3.2.1, or in a README file there.  At any rate, it mentioned that
> it was essential to make some change to the table layout used by previous
> versions of DSPAM, and then Postgres would run many times faster.
>
> Unfortunately I no longer have 3.2.1 installed on my system, so I can't
> tell you if it was in there or somewhere else.
>
>   -- Clifton
>
> --
>           Clifton Royston  --  cliftonr@tikitechnologies.com
>          Tiki Technologies Lead Programmer/Software Architect
> Did you ever fly a kite in bed?  Did you ever walk with ten cats on your
> head?
>   Did you ever milk this kind of cow?  Well we can do it.  We know how.
> If you never did, you should.  These things are fun, and fun is good.
>                                                                  -- Dr.
> Seuss
>