Re: SQL over my head...

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