From: "Stuart McGraw" <smcg2297@frii.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: slow view
Date: Wed, 11 Oct 2006 22:15:58 -0600
Message-ID: <NDBBKHIAJKGCOIMLDMGJKEEBGGAB.smcg2297@frii.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-2022-jp"
Content-Transfer-Encoding: 7bit
In-Reply-To: <egk560$p53$1@sea.gmane.org>
Importance: Normal

On 2006/10/11 Stuart wrote:
> [..]

Apologies for following up my own post, but after
struggling with that query for over a day, I figured 
out the answer within thirty minutes of posting.
(Sigh)

My slow query was:
> SELECT p.id AS pid, a.id AS aid, sub.bid AS bid
>     FROM p
>         JOIN a ON a.p=p.id
>         LEFT JOIN (
>             SELECT a.id AS aid, b.id as bid
>                 FROM a 
>                     LEFT JOIN b ON b.p=a.p
>                     LEFT JOIN x ON x.a=a.id AND x.b=b.id
>                 WHERE x.a IS NULL
>             ) AS sub ON sub.aid=a.id
>     WHERE p.id=1;

Including the parent table p in the inner select was 
all it took.

SELECT p.id AS pid, a.id AS aid, sub.bid AS bid
    FROM p
        JOIN a ON a.p=p.id
        LEFT JOIN (
            SELECT p.id AS pid, a.id AS aid, b.id as bid
                FROM p
                   JOIN a ON a.p=p.id
                   LEFT JOIN b ON b.p=a.p
                   LEFT JOIN x ON x.a=a.id AND x.b=b.id
               WHERE x.a IS NULL
        ) AS sub ON sub.aid=a.id AND sub.pid=p.id
    WHERE p.id=1;