Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 6ABE44759B0
	for <pgsql-sql@postgresql.org>; Thu, 16 May 2002 16:21:50 -0400 (EDT)
Received: from temp.joelburton.com (unknown [209.190.238.101])
	by postgresql.org (Postfix) with ESMTP id 8E72147596E
	for <pgsql-sql@postgresql.org>; Thu, 16 May 2002 16:21:49 -0400 (EDT)
Received: from joeltf0d13cyyp (unknown [209.190.238.100])
	by temp.joelburton.com (Postfix) with SMTP
	id 1E8E72B981; Thu, 16 May 2002 16:27:11 -0400 (EDT)
From: "Joel Burton" <joel@joelburton.com>
To: "Joel Burton" <joel@joelburton.com>,
	"Gregory Brauer" <greg@wildbrain.com>, <pgsql-sql@postgresql.org>
Subject: Re: SQL over my head...
Date: Thu, 16 May 2002 16:21:50 -0400
Message-ID: <JGEPJNMCKODMDHGOBKDNCEHKCOAA.joel@joelburton.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
In-Reply-To: <JGEPJNMCKODMDHGOBKDNKEHJCOAA.joel@joelburton.com>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Archive-Number: 200205/174
X-Sequence-Number: 7809

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