From: Lucas Adamski <ladamski@manageww.com>
To: 'Stephan Szabo' <sszabo@megazone23.bigpanda.com>
Cc: 'Postgresql Performance Mailing list (E-mail)' <pgsql-performance@postgresql.org>
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> (raw)
In-Reply-To: <20030507154625.C32502-100000@megazone23.bigpanda.com>
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).
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: ladamski@manageww.com, sszabo@megazone23.bigpanda.com
Subject: Re: Hack around lack of CORRESPONDING BY in EXCEPT?
In-Reply-To: <001c01c31858$46967500$11f5ec0c@LADAMSKI>
References: <20030507154625.C32502-100000@megazone23.bigpanda.com> <001c01c31858$46967500$11f5ec0c@LADAMSKI>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox