From: Joel Burton <joel@joelburton.com>
To: Joel Burton <joel@joelburton.com>
To: Gregory Brauer <greg@wildbrain.com>
To: 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> (raw)
In-Reply-To: <JGEPJNMCKODMDHGOBKDNKEHJCOAA.joel@joelburton.com>
> -----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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: joel@joelburton.com, greg@wildbrain.com
Subject: Re: SQL over my head...
In-Reply-To: <JGEPJNMCKODMDHGOBKDNCEHKCOAA.joel@joelburton.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox