Re: SQL over my head...

Joel Burton wrote:
>>-----Original Message-----
>>From: pgsql-sql-owner(at)postgresql(dot)org
>>[mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Joel Burton
>>Sent: Thursday, May 16, 2002 4:14 PM
>>To: Gregory Brauer; pgsql-sql(at)postgresql(dot)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