From: Lucas Adamski <ladamski@manageww.com>
To: Postgresql Performance Mailing list (E-mail) <pgsql-performance@postgresql.org>
Subject: Re: Hack around lack of CORRESPONDING BY in EXCEPT?
Date: Wed, 7 May 2003 15:28:26 -0700
Message-ID: <000801c314e7$fb236e70$11f5ec0c@LADAMSKI> (raw)
In-Reply-To: <20030507124143.V30050-100000@megazone23.bigpanda.com>
Stephan,
Yup, unfortunately you are correct... I'd need to get the event.pk's out of
there somewhere to join with the tracking.event_fk. I can't put the
event.pk in the subselects as they don't match, and I would get an empty set
back.
select tracking.pk, e.data1, e.data2 from
tracking,
((select data1,data2 from events) except (select data1,data2 from events
where event.type=10)) e
where tracking.event_fk=e.pk;
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. :)
So in this case I'm getting all of the relevant data for the new entries,
subtracting those from the old entries that are referred to by the tracking
system, and returning those outdated tracking.pk's.
Lucas.
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Wednesday, May 07, 2003 12:43 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:
Of course my last suggestion won't work since you need to get the event.pk
field out. The actual subquery would need to be more complicated and
probably involve an IN or EXISTS. :(
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
Subject: Re: Hack around lack of CORRESPONDING BY in EXCEPT?
In-Reply-To: <000801c314e7$fb236e70$11f5ec0c@LADAMSKI>
References: <20030507124143.V30050-100000@megazone23.bigpanda.com> <000801c314e7$fb236e70$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