X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id F0D68475EC9 for ; Wed, 7 May 2003 18:50:27 -0400 (EDT) Received: from rwcrmhc52.attbi.com (rwcrmhc52.attbi.com [216.148.227.88]) by postgresql.org (Postfix) with ESMTP id 77662475B8E for ; Wed, 7 May 2003 18:50:26 -0400 (EDT) Received: from ladamski (12-236-245-17.client.attbi.com[12.236.245.17]) by attbi.com (rwcrmhc52) with SMTP id <2003050722502905200m2i3he>; Wed, 7 May 2003 22:50:29 +0000 From: "Lucas Adamski" To: "Postgresql Performance Mailing list (E-mail)" Subject: Re: Hack around lack of CORRESPONDING BY in EXCEPT? Date: Wed, 7 May 2003 15:49:06 -0700 Message-ID: <000a01c314ea$de8c5f30$11f5ec0c@LADAMSKI> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4925.2800 Importance: Normal In-Reply-To: <5npibv8l1090ppjkt17is5ipq26honl1e1@4ax.com> X-Spam-Status: No, hits=-12.8 required=5.0 tests=BAYES_10,IN_REP_TO,QUOTED_EMAIL_TEXT,QUOTE_TWICE_1 autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/95 X-Sequence-Number: 1874 Manfred, I think what you propose is similar to what Patrick proposed, let me see if I can explain below: > -----Original Message----- > From: Manfred Koizar [mailto:mkoi-pg@aon.at] > Sent: Wednesday, May 07, 2003 1:23 PM > To: Lucas Adamski > Cc: Postgresql Performance Mailing list (E-mail) > Subject: Re: [PERFORM] Hack around lack of CORRESPONDING BY in EXCEPT? > > Lucas, try this untested query: > > SELECT tr.pk, ev.data1, ev.data2 > FROM tracking tr INNER JOIN events ev > ON tr.event_fk = ev.pk > WHERE ev.type != 10; > > (Should also work with AND instead of WHERE.) The problem is that it simply removes all events where type != 10, versus subtracting all events from subselect of type 10 where data1 and data2 match those in the main join. The goal of the query is to remove all events that match (matching being defined as both data1 and data2 matching) that are present in events of type 10 and events that are referenced by the tracking table, then return those tracking.pk's for entries that are left over. Its not required that I join tracking and events in the primary select before doing the EXCEPT join, but it should make it a bit more efficient. > > >SELECT tracking.pk,events.data1,events.data2 FROM > tracking,events WHERE > >tracking.event_fk = event.pk EXCEPT (SELECT > >tracking.pk,events.data1,events.data2 FROM tracking,events WHERE > >tracking.event_fk = event.pk AND event.type = 10) > > > >That won't work for two reasons... first, there are no > matching entries in > >the tracking table pointing to events where event.type = 10, > meaning this > >query would always return an empty set. > > I don't understand this. If there are no entries with event.type 10, > then the subselect returns an empty result set, and EXCEPT > should give the original result? Its not that there are no entires with event.type=10, its that there may not be any tracking entires for events of type 10, and if I join them before doing the EXCEPT I will lose them. That's why I have to do the EXCEPT subselect without joining it to the table. Thanks, Lucas. > > Servus > Manfred > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >