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 D754552B17 for ; Thu, 4 Aug 2005 11:37:09 -0300 (ADT) 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 51941-07 for ; Thu, 4 Aug 2005 14:37:02 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id AC8765295A for ; Thu, 4 Aug 2005 11:37:01 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j74Eb362015611; Thu, 4 Aug 2005 10:37:03 -0400 (EDT) To: Matthew Schumacher Cc: pgsql-performance@postgresql.org Subject: Re: Performance problems testing with Spamassassin 3.1.0 In-reply-to: <42F1CE43.8040801@aptalaska.net> References: <42E9749F.6000709@aptalaska.net> <1122616211.5691.340.camel@lamb.mcmillan.net.nz> <42EA6966.8090402@aptalaska.net> <1122668371.5691.352.camel@lamb.mcmillan.net.nz> <19405.1122670917@sss.pgh.pa.us> <42EAA410.9010700@aptalaska.net> <27897.1122748133@sss.pgh.pa.us> <42EE8043.1050207@aptalaska.net> <42EE93F8.5010003@aptalaska.net> <20050801214931.GF60019@decibel.org> <42EE9D83.8000405@pobox.com> <42F1CE43.8040801@aptalaska.net> Comments: In-reply-to Matthew Schumacher message dated "Thu, 04 Aug 2005 00:13:55 -0800" Date: Thu, 04 Aug 2005 10:37:02 -0400 Message-ID: <15610.1123166222@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/36 X-Sequence-Number: 13783 Matthew Schumacher writes: > for i in array_lower(intokenary, 1) .. array_upper(intokenary, 1) > LOOP > _token := intokenary[i]; > INSERT INTO bayes_token_tmp VALUES (_token); > END LOOP; > UPDATE > bayes_token > SET > spam_count = greatest_int(spam_count + inspam_count, 0), > ham_count = greatest_int(ham_count + inham_count , 0), > atime = greatest_int(atime, 1000) > WHERE > id = inuserid > AND > (token) IN (SELECT intoken FROM bayes_token_tmp); I don't really see why you think that this path is going to lead to better performance than where you were before. Manipulation of the temp table is never going to be free, and IN (sub-select) is always inherently not fast, and NOT IN (sub-select) is always inherently awful. Throwing a pile of simple queries at the problem is not necessarily the wrong way ... especially when you are doing it in plpgsql, because you've already eliminated the overhead of network round trips and repeated planning of the queries. regards, tom lane