X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id A81A79FB403
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 13 Jul 2006 13:25:13 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 16209-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 13 Jul 2006 13:25:05 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172])
	by postgresql.org (Postfix) with ESMTP id EDCEA9FB2D0
	for <pgsql-sql@postgresql.org>; Thu, 13 Jul 2006 13:25:04 -0300 (ADT)
Received: by ug-out-1314.google.com with SMTP id m3so362610uge
	for <pgsql-sql@postgresql.org>; Thu, 13 Jul 2006 09:25:03 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=YFepr+pIWONNwexbzTHynswNiXnrmrLOGmpnYVZO+IXNFmRPaz3vKnn0VLVQET1rPzZ/xsai1dD33UlXJf9gN5bzIUDV4MVPnlrCAfu+MaDHVIyynt5lYaFyr/U/eRaUXRNbYMEIwISegoabmLtapaoU1agumVn8HtRXLNMLNEs=
Received: by 10.78.177.3 with SMTP id z3mr834387hue;
	Thu, 13 Jul 2006 09:25:03 -0700 (PDT)
Received: by 10.78.29.15 with HTTP; Thu, 13 Jul 2006 09:25:03 -0700 (PDT)
Message-ID: <bf05e51c0607130925u12635434v98d877a7f1eb449a@mail.gmail.com>
Date: Thu, 13 Jul 2006 11:25:03 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "Richard Broersma Jr" <rabroersma@yahoo.com>
Subject: Re: How to find entries missing in 2nd table?
Cc: "Exner, Peter" <Exner@his.de>,
	"SQL Postgresql List" <pgsql-sql@postgresql.org>
In-Reply-To: <20060713161651.39567.qmail@web31809.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_12394_12957530.1152807903303"
References: <bf05e51c0607130846ufdb8a31n5757f67361447c2b@mail.gmail.com>
	<20060713161651.39567.qmail@web31809.mail.mud.yahoo.com>
X-Google-Sender-Auth: e1013e489b9bb91c
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/201
X-Sequence-Number: 25775

------=_Part_12394_12957530.1152807903303
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/13/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> > > SELECT controller_id FROM control
> > > WHERE controller_id NOT IN
> > > (SELECT DISTINCT controller_id FROM datapack);
> > The DISTINCT is not necessary.  I have heard with Oracle that DISTINCT
> is a
> > huge performance problem.  Is that true on PostgreSQL also?
>
> From my experience, it does not preform as well as the standard group by
> clause. I noticed a ~20%
> increase in query run times.



So in that case this would be better:

SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT controller_id FROM datapack);

or

SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT controller_id FROM datapack GROUP BY controller_id);

Guess you need to do some explain plans to see which would be best.

Good luck!

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

------=_Part_12394_12957530.1152807903303
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/13/06, <b class="gmail_sendername">Richard Broersma Jr</b> &lt;<a href="mailto:rabroersma@yahoo.com">rabroersma@yahoo.com</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
&gt; &gt; SELECT controller_id FROM control<br>&gt; &gt; WHERE controller_id NOT IN<br>&gt; &gt; (SELECT DISTINCT controller_id FROM datapack);<br>&gt; The DISTINCT is not necessary.&nbsp;&nbsp;I have heard with Oracle that DISTINCT is a
<br>&gt; huge performance problem.&nbsp;&nbsp;Is that true on PostgreSQL also?<br><br>From my experience, it does not preform as well as the standard group by clause. I noticed a ~20%<br>increase in query run times.</blockquote><div>
<br><br>So in that case this would be better:<br><br><span class="q">SELECT controller_id FROM control<br>WHERE controller_id NOT IN<br>(SELECT controller_id FROM datapack);</span> <br></div><br>or<br><br>
<span class="q">SELECT controller_id FROM control<br>WHERE controller_id NOT IN<br>(SELECT controller_id FROM datapack GROUP BY controller_id);</span> <br><br>Guess you need to do some explain plans to see which would be best.
<br><br>Good luck!<br>
</div><br>==================================================================<br>&nbsp;&nbsp; Aaron Bono<br>&nbsp;&nbsp; Aranya Software Technologies, Inc.<br>&nbsp;&nbsp; <a href="http://www.aranya.com">http://www.aranya.com</a><br>==================================================================

------=_Part_12394_12957530.1152807903303--