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 C9C0832A15F for ; Thu, 21 Oct 2004 18:03:47 +0100 (BST) 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 89977-03 for ; Thu, 21 Oct 2004 17:03:45 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.206]) by svr1.postgresql.org (Postfix) with ESMTP id 01E7F32A095 for ; Thu, 21 Oct 2004 18:03:45 +0100 (BST) Received: by rproxy.gmail.com with SMTP id 73so54722rnk for ; Thu, 21 Oct 2004 10:03:41 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=IQ9dpCYO4Dn87UkWt+DaueTvl0uZ9UpNxzs0DafKjH8V9qTrq9lgc7ClqoVdp09Q+fVYEKNkAe9GU1ZPA2GkqediNQInZdYrNRcaVa7vc1pM06h3OF8Q7xtg6oXNkYvlCxMiLsr3RpTJ+gjxMvntCAPN2nY028swawfUfzAw6Zg= Received: by 10.38.151.68 with SMTP id y68mr3190700rnd; Thu, 21 Oct 2004 10:03:41 -0700 (PDT) Received: by 10.38.125.26 with HTTP; Thu, 21 Oct 2004 10:03:41 -0700 (PDT) Message-ID: Date: Thu, 21 Oct 2004 20:03:41 +0300 From: Victor Ciurus Reply-To: Victor Ciurus To: pgsql-performance@postgresql.org Subject: Re: Simple machine-killing query! Cc: Tom Lane In-Reply-To: <16017.1098373308@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <16017.1098373308@sss.pgh.pa.us> 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: 200410/359 X-Sequence-Number: 8835 Well guys, Your replies have been more than helpful to me, showing me both the learning stuff I still have to get in my mind about real SQL and the wonder called PostgreSQL and a very good solution from Tom Lane (thanks a lot sir!)! Indeed, changing mem_sort and other server parmeters along with the quite strange (to me!) outer join Tom mentioned finally got me to finalize the cleaning task and indeed in warp speed (some 5 mintues or less!). I am running PG v7.4.5 on a PIV Celeron 1,7Ghz with 1,5Gb ram so talking about the time performance I might say that I'm more than pleased with the result. As with the amazement PG "caused" me through its reliability so far I am decided to go even deeper in learning it! Thanks again all for your precious help! Regards, Victor > > If you are using PG 7.4, you can get reasonable performance out of this > approach, but you need to jack sort_mem up to the point where the whole > DIRTY table will fit into sort_mem (so that you get a hashed-subplan > plan and not a plain subplan). If you find yourself setting sort_mem to > more than say half of your machine's available RAM, you should probably > forget that idea. > > > [explain] select * from bigma,dirty where bigma.email!=dirty.email; > > This of course does not give the right answer at all. > > A trick that people sometimes use is an outer join: > > select * from bigma left join dirty on (bigma.email=dirty.email) > where dirty.email is null; > > Understanding why this works is left as an exercise for the reader > ... but it does work, and pretty well too. If you're using pre-7.4 > PG then this is about the only effective solution AFAIR. > > regards, tom lane >