On Wed, Nov 17, 2004 at 07:15:23PM -0800, Rupa Schomaker wrote: > > With a bayes_token db of ~125000 entries the system ground to a halt. > Time to look further... > > The sample creation script creates a primary key but no index. Postgres > creates the constraint but not the index to support the PK. So, all > queries resulted in a full table scan. Not good. > > So, I created a unique index in the same order as the primary key > definition. (id, token). Not smart. Since I'm running with only 1 > user, id is always a single value. Queries using that id would always > result in a table scan (dunno why). > > Creating the index as so: > > CREATE UNIQUE INDEX pk_bayes_token > ON bayes_token > USING btree > (token, id); > > resulted in reasonable performance (queries with large inlist in the > tokens are around 100ms rather than 20s). > > I also created a similar index on bayes_seen: > > CREATE UNIQUE INDEX "pk_ bayes_seen" > ON bayes_seen > USING btree > (msgid, id); >
You should look at Bug 3771: http://bugzilla.spamassassin.org/show_bug.cgi?id=3771 and feel free to comment or improve the patch. Michael
pgpIqKWMAnFL9.pgp
Description: PGP signature
