Re: SQL over my head...

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