X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BA51E3A4CDF for ; Sat, 27 Nov 2004 10:14:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49469-09 for ; Sat, 27 Nov 2004 10:14:36 +0000 (GMT) Received: from mail.bouton.name (ns.bouton.name [70.85.16.101]) by svr1.postgresql.org (Postfix) with ESMTP id 646863A4CC8 for ; Sat, 27 Nov 2004 10:14:37 +0000 (GMT) Received: from [82.227.97.166] (server.bouton.name [82.227.97.166]) by mail.bouton.name (Postfix) with ESMTP id 603CBB8BB; Sat, 27 Nov 2004 11:14:33 +0100 (CET) Message-ID: <41A85386.8070407@bouton.name> Date: Sat, 27 Nov 2004 11:14:30 +0100 From: Lionel Bouton User-Agent: Mozilla Thunderbird 0.9 (X11/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Casey Allen Shobe Cc: Clifton Royston , Evilio del Rio , dspam-users@networkdweebs.com, pgsql-performance@postgresql.org Subject: Re: [dspam-users] Postgres vs. MySQL References: <1101302058.338.74.camel@haddock.cmima.csic.es> <20041126203531.GC8991@tikitechnologies.com> <35118.206.63.123.15.1101521473.squirrel@206.63.123.15> In-Reply-To: <35118.206.63.123.15.1101521473.squirrel@206.63.123.15> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/415 X-Sequence-Number: 9369 Casey Allen Shobe wrote the following on 11/27/04 03:11 : >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. > > > In tum mode, this could help too (I'm currently testing it) : CREATE INDEX id_token_data_sumhits ON dspam_token_data ((spam_hits + innocent_hits)); Indeed each UPDATE on dspam_token_data in TUM is done with : WHERE ... AND spam_hits + innocent_hits < 50