Re: SQL over my head...

> -----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
FooAS 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.