From: Gregory Brauer <greg@wildbrain.com>
To: Joel Burton <joel@joelburton.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL over my head...
Date: Thu, 16 May 2002 13:39:09 -0700
Message-ID: <3CE418ED.7070103@wildbrain.com> (raw)
References: <JGEPJNMCKODMDHGOBKDNCEHKCOAA.joel@joelburton.com>
Joel Burton wrote:
>>-----Original Message-----
>>From: pgsql-sql-owner@postgresql.org
>>[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Joel Burton
>>Sent: Thursday, May 16, 2002 4:14 PM
>>To: Gregory Brauer; pgsql-sql@postgresql.org
>>Subject: Re: [SQL] SQL over my head...
>>
>>
>>I think that
>>
>>SELECT F0.id
>> FROM Foo AS F0
>> JOIN Bar AS B0 ON (F0.id=B0.id)
>> WHERE ts =
>> (SELECT MAX(ts)
>> FROM Foo AS F1,
>> Bar as B1
>> WHERE ts < CURRENT_TIME
>> AND F0.attr_a=F1.attr_a
>> AND F0.attr_b=F1.attr_b
>> AND B0.attr_a=B1.attr_a);
>
>
> Just glancing over this, I realized that this will perform slowly. If you
> have indexes on sensible things (ts, ids, attrs, etc.), something like:
>
> SELECT F0.id
> FROM Foo as F0
> JOIN Bar as B0 ON (F0.id=B0.id)
> WHERE NOT EXISTS (
> (SELECT *
> FROM Foo AS F1,
> Bar as B1
> WHERE ts < CURRENT_TIME
> AND F0.attr_a=F1.attr_a
> AND F0.attr_b=F1.attr_b
> AND B0.attr_a=B1.attr_a
> AND B1.ts > B0.ts)
>
> should be equivalent (assuming NOT NULL data) and perform better.
>
Wow, thanks for the help! I'm still parsing this, but one comment
and one question...
First, I realized that though I will probably need this
more general case later, in what I am doing right now, I know
a single bar.attr_a value that I want, so only foo.attr_a and
foo.attr_b are variable. Does that change anything?
Secondly, in the third line, should (F0.id=B0.id) actually be
(F0.bar_id=B0.id) to join on the foreign key?
I'm trying this out now...
Greg
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: greg@wildbrain.com, joel@joelburton.com
Subject: Re: SQL over my head...
In-Reply-To: <3CE418ED.7070103@wildbrain.com>
* 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