X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 68F164762CF for ; Wed, 7 May 2003 15:13:09 -0400 (EDT) Received: from sccrmhc02.attbi.com (sccrmhc02.attbi.com [204.127.202.62]) by postgresql.org (Postfix) with ESMTP id 4CCE5476085 for ; Wed, 7 May 2003 15:13:08 -0400 (EDT) Received: from ladamski (12-236-245-17.client.attbi.com[12.236.245.17]) by attbi.com (sccrmhc02) with SMTP id <20030507191308002006t32je>; Wed, 7 May 2003 19:13:08 +0000 From: "Lucas Adamski" To: "Postgresql Performance Mailing list (E-mail)" Subject: Hack around lack of CORRESPONDING BY in EXCEPT? Date: Wed, 7 May 2003 12:11:46 -0700 Message-ID: <000301c314cc$81c4ad20$11f5ec0c@LADAMSKI> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0004_01C31491.D5675BC0" 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 X-Spam-Status: No, hits=-2.4 required=5.0 tests=BAYES_20,HTML_30_40 version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/88 X-Sequence-Number: 1867 This is a multi-part message in MIME format. ------=_NextPart_000_0004_01C31491.D5675BC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit I'm not sure if this a performance question or a sql question really, but since my primarily peeve here is performance, here goes: I'm trying to write a query which takes the output of a join and shows me only what the items that are in the main join but not in the subselect of just one of the tables in the join, using EXCEPT. This is a little complicated, so please bear with me. I have two tables: an event table that logs random events as they come in, and a tracking table that keeps a state of events it cares about. In this particular case I'm trying to obtain a list of tracking pkeys for related event data that do not correspond to a certain (other) set of event data. Ideally, here is what I want: SELECT tracking.pk,events.data1,events.data2 FROM tracking,events WHERE tracking.event_fk = event.pk EXCEPT (SELECT events.data1,events.data2 FROM events WHERE event.type = 10) The problem I have of course is that I get an error regarding trying to use different columns for the two queries in EXCEPT. I'm sure someone will point this out, but the following suggestion will not work: 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. And even if there were, I don't want to do the join twice if its not necessary, as the events table is liable to be very large. The official solution to this I believe would be to just use CORRESPONDING BY, but that's not supported by PG (why exactly, oh why!) Suggestions, anyone? Thanks in advance, Lucas. ------=_NextPart_000_0004_01C31491.D5675BC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I'm not s= ure if this=20 a performance question or a sql question really, but since my primarily pee= ve=20 here is performance, here goes:
 
I'm tryin= g to write=20 a query which takes the output of a join and shows me only what the items t= hat=20 are in the main join but not in the subselect of just one of the tables in = the=20 join, using EXCEPT.
 
This is a= little=20 complicated, so please bear with me. 
 
I have tw= o tables:=20 an event table that logs random events as they come in, and a tracking tabl= e=20 that keeps a state of events it cares about.  In this particular case = I'm=20 trying to obtain a list of tracking pkeys for related event data that = do=20 not correspond to a certain (other) set of event data.
 
Ideally, = here is=20 what I want:
 
SELECT=20 tracking.pk,events.data1,events.data2 FROM tracking,events WHERE=20 tracking.event_fk =3D event.pk EXCEPT (SELECT events.data1,events.data= 2=20 FROM events WHERE event.type =3D 10)
 
The probl= em I have=20 of course is that I get an error regarding trying to use different columns = for=20 the two queries in EXCEPT.  I'm sure someone will point this out, but = the=20 following suggestion will not work:
 
SELECT=20 tracking.pk,events.data1,events.data2 FROM tracking,events WHERE=20 tracking.event_fk =3D event.pk EXCEPT (SELECT tracking.pk,events.data1,events.data2 FROM=20 tracking,events WHERE tracking.event_fk =3D event.pk AND=20 event.type =3D 10)
 
That won't work for two reasons... fi= rst,=20 there are no matching entries in the tracking table pointing to events wher= e=20 event.type =3D 10, meaning this query would always return an empty set.&nbs= p; And=20 even if there were, I don't want to do the join twice if its not necessary,= as=20 the events table is liable to be very large.
 
The offic= ial=20 solution to this I believe would be to just use CORRESPONDING BY, but= =20 that's not supported by PG (why exactly, oh why!)
 
Suggestio= ns,=20 anyone?  Thanks in advance,
 =20 Lucas.
 
 
------=_NextPart_000_0004_01C31491.D5675BC0--