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 EA0CF9F9F27 for ; Sun, 16 Jul 2006 22:10: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 97924-06 for ; Sun, 16 Jul 2006 22:10:01 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 64E7C9F9840 for ; Sun, 16 Jul 2006 22:10:02 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from elasmtp-kukur.atl.sa.earthlink.net (elasmtp-kukur.atl.sa.earthlink.net [209.86.89.65]) by svr4.postgresql.org (Postfix) with ESMTP id 84ADA5AF024 for ; Mon, 17 Jul 2006 01:10:00 +0000 (GMT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=dk20050327; d=mindspring.com; b=g0QzdSvOMUdGwD9zXFd6qmJH1cELdlJ2XmFQmkbs+7c9kpCHirgtHop9BBcVirYt; h=Received:From:To:Cc:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:Importance:In-Reply-To:X-MimeOLE:X-ELNK-Trace:X-Originating-IP; Received: from [69.3.205.55] (helo=gecko1) by elasmtp-kukur.atl.sa.earthlink.net with asmtp (Exim 4.34) id 1G2HcT-0006Xv-17; Sun, 16 Jul 2006 21:09:57 -0400 From: "Nate Teller" To: "Aaron Bono" , "Exner, Peter" Cc: "Richard Broersma Jr" , "SQL Postgresql List" Subject: Re: How to find entries missing in 2nd table? Date: Sun, 16 Jul 2006 21:09:48 -0400 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0008_01C6A91C.2BCEA820" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-ELNK-Trace: 2d74a9b8ceea343745bb702b9c31c75c416dc04816f3191c84aa206e35593531a343ab0b66858d51c561a0d5f1a1de9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 69.3.205.55 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/221 X-Sequence-Number: 25795 This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C6A91C.2BCEA820 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit I have received good performance with the following: select c.controller from control c left outer join datapack d on d.controller_id = c.controller_id where d.controller_id is null Nate Teller -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Aaron Bono Sent: Thursday, July 13, 2006 11:46 AM To: Exner, Peter Cc: Richard Broersma Jr; SQL Postgresql List Subject: Re: [SQL] How to find entries missing in 2nd table? On 7/12/06, Exner, Peter < Exner@his.de > wrote: Hi, what about 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? ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_NextPart_000_0008_01C6A91C.2BCEA820 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

I = have received good performance with the = following:

se= lect c.controller

from control = c

= left outer join datapack d on d.controller_id =3D = c.controller_id

where d.controller_id is = null

Na= te Teller

-----Original Message-----
From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Aaron Bono
Sent: Thursday, July 13, = 2006 11:46 AM
To: Exner, Peter
Cc: Richard Broersma Jr; = SQL Postgresql List
Subject: Re: [SQL] How to = find entries missing in 2nd table?

On = 7/12/06, Exner, Peter <Exner@his.de> = wrote:<= /p>

Hi,

what about

SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT DISTINCT controller_id FROM datapack);<= /p>

The DISTINCT is not necessary. I have heard with Oracle that = DISTINCT is a huge performance problem. Is that true on PostgreSQL also?

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D <= /p>

------=_NextPart_000_0008_01C6A91C.2BCEA820--