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@xxxxxxxx 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@xxxxxxxxxxxxxxxxxxxx > 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 >
