pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: [dspam-users] Postgres vs. MySQL
Date: Sat, 27 Nov 2004 13:43:15 -0500
Message-ID: <m3u0rbxhdo.fsf@knuth.knuth.cbbrowne.com> (raw)
References: <1101302058.338.74.camel@haddock.cmima.csic.es>
	<20041126203531.GC8991@tikitechnologies.com>
	<35118.206.63.123.15.1101521473.squirrel@206.63.123.15>

Martha Stewart called it a Good Thing when cshobe@osss.net ("Casey Allen Shobe") wrote:
> 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.

That makes a great deal of sense; the number of tokens are likely to
be rather larger than 10, and are likely to be quite unevenly
distributed.  That fits with the need you found to collect more
statistics on that column.

Other cases where it seems plausible that it would be worthwhile to do
the same:

  alter table dspam_signature_data alter signature set statistics 200;
  alter table dspam_neural_data alter node set statistics 200;
  alter table dspam_neural_decisions alter signature set statistics 200;

Lionel's suggestion of having a functional index on dspam_token_data
(innocent_hits + spam_hits) also seems likely to be helpful.  Along
with that, it might prove necessary to alter stats on dspam_token_data
thus:

  alter table dspam_token_data alter innocent_hits set statistics 200;
  alter table dspam_token_data alter spam_hits set statistics 200;

None of these changes are likely to make things materially worse; if
they do help, they'll help rather a lot.
-- 
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://www.ntlug.org/~cbbrowne/nonrdbms.html
Rules  of the  Evil  Overlord #112.  "I  will not  rely entirely  upon
"totally  reliable"  spells  that  can be  neutralized  by  relatively
inconspicuous talismans." <http://www.eviloverlord.com/;



view thread (12+ messages)

Message-ID: <m3u0rbxhdo.fsf@knuth.knuth.cbbrowne.com>
Permalink:  ../m3u0rbxhdo.fsf@knuth.knuth.cbbrowne.com/
Also on:    postgresql.org/message-id/m3u0rbxhdo.fsf@knuth.knuth.cbbrowne.com

 · 

reply

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: cbbrowne@acm.org
  Subject: Re: [dspam-users] Postgres vs. MySQL
  In-Reply-To: <m3u0rbxhdo.fsf@knuth.knuth.cbbrowne.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