X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D13B9475956 for ; Mon, 12 May 2003 03:31:21 -0400 (EDT) Received: from rwcrmhc53.attbi.com (rwcrmhc53.attbi.com [204.127.198.39]) by postgresql.org (Postfix) with ESMTP id 9711747580B for ; Mon, 12 May 2003 03:31:19 -0400 (EDT) Received: from ladamski (12-236-245-17.client.attbi.com[12.236.245.17]) by attbi.com (rwcrmhc53) with SMTP id <2003051207312005300q4uepe>; Mon, 12 May 2003 07:31:20 +0000 From: "Lucas Adamski" To: "'Stephan Szabo'" Cc: "'Postgresql Performance Mailing list (E-mail)'" Subject: Re: Hack around lack of CORRESPONDING BY in EXCEPT? Date: Mon, 12 May 2003 00:29:50 -0700 Message-ID: <001c01c31858$46967500$11f5ec0c@LADAMSKI> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" 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) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4925.2800 In-Reply-To: <20030507154625.C32502-100000@megazone23.bigpanda.com> X-Spam-Status: No, hits=-12.2 required=5.0 tests=BAYES_10,IN_REP_TO,QUOTED_EMAIL_TEXT 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/124 X-Sequence-Number: 1903 Stephan, Bingo! That worked perfectly, thank you! I was considering something like that, but couldn't figure out the syntax offhand to join two events tables in that fashion. Didn't realize you could alias a table as well! Thanks again, Lucas. > -----Original Message----- > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > Sent: Wednesday, May 07, 2003 3:59 PM > To: Lucas Adamski > Cc: Postgresql Performance Mailing list (E-mail) > Subject: Re: [PERFORM] Hack around lack of CORRESPONDING BY in EXCEPT? > > > > On Wed, 7 May 2003, Lucas Adamski wrote: > > > I wrote it originally as: > > > > 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) > > > > because each of these subqueries restricts the dataset > greatly before doing > > the join. I've simplified the actual problem (as the real > code has a bunch > > of extraneous stuff that makes it even more obtuse), but > essentially, the > > tracking table maintains a record of the last record type > that was entered. > > The type is incremented for each batch of events that is > loaded. In this > > case, I'm assuming that the latest batch is type=10 (or > 5000, or 100000), > > and the tracking table references a small subset of previous events > > (possibly of types 1-9 in this example). This particular > query is supposed > > to return all tracking.pk's that are present in the > previous batches (types) > > but not in the latest batch (10). I didn't mean to make it > quite so obtuse, > > sorry. :) > > Maybe something like nominally like (quickly done so possibly wrong > again): > > select tracking.pk, events.data1, events.data2 from > tracking,events where not exists (select * from events e where > e.type=10 and e.data1=events.data1 and e.data2=events.data2) > and tracking.event_fk=event.pk > > Get all tracking/event combinations, not including those > where the data1/2 > matches that of an event with type 10. > > That might give dups if there are multiple events rows with > that pk for > different types (but not 10). > >