Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id B025D4764BA for ; Thu, 16 May 2002 16:42:00 -0400 (EDT) Received: from wildbrain.com (unknown [64.1.15.36]) by postgresql.org (Postfix) with ESMTP id 9CA06475C8A for ; Thu, 16 May 2002 16:39:21 -0400 (EDT) Received: from wildbrain.com (spock.wildbrain.com [10.20.2.38]) by wildbrain.com (8.12.2/8.12.2) with ESMTP id g4GKd9TP010091; Thu, 16 May 2002 13:39:09 -0700 Message-ID: <3CE418ED.7070103@wildbrain.com> Date: Thu, 16 May 2002 13:39:09 -0700 From: Gregory Brauer User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0rc2) Gecko/20020510 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joel Burton Cc: pgsql-sql@postgresql.org Subject: Re: SQL over my head... References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200205/176 X-Sequence-Number: 7811 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