Message-ID: <51DF51DA.7010509@agliodbs.com>
Date: Thu, 11 Jul 2013 17:46:18 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130623 Thunderbird/17.0.7
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Trying to eliminate union and sort
References: <51DEF924.1040809@consistentstate.com>
 <WM!193bd65d81fa7c39e56918332a81c274363f4bb6c8de7151691ea39398f8f9117cf6aec5b0fd2887b97b4b5a921c1aa8!@asav-1.01.com>
In-Reply-To: 
 <WM!193bd65d81fa7c39e56918332a81c274363f4bb6c8de7151691ea39398f8f9117cf6aec5b0fd2887b97b4b5a921c1aa8!@asav-1.01.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Brian,

> 3. I'm trying to eliminate the union, however I have two problems.
> A) I can't figure out how to have an 'or' clause in a single join that
> would fetch all the correct rows. If I just do:
> LEFT OUTER JOIN table2 t2 ON (t2.real_id = t.id OR t2.real_id =
> t.backup_id), I end up with many less rows than the original query. B.
> 
> I believe the issue with this is a row could have one of three
> possibilities:
> * part of the first query but not the second -> results in 1 row after
> the union
> * part of the second query but not the first -> results in 1 row after
> the union
> * part of the first query and the second -> results in 2 rows after the
> union (see 'B)' for why)
> 
> B) the third and fourth column in the SELECT will need to be different
> depending on what column the row is joined on in the LEFT OUTER JOIN to
> table2, so I may need some expensive case when logic to filter what is
> put there based on whether that row came from the first join clause, or
> the second.

No, it doesn't:

SELECT t.id,
	t.mycolumn1,
	table3.otherid as otherid1,
	table3a.otherid as otherid2,
	t.mycolumn2
FROM t
	LEFT OUTER JOIN table2
	   ON ( t.id = t2.real_id OR t.backup_id = t2.real_id )
	LEFT OUTER JOIN table3
	   ON ( t.typeid = table3.id )
        LEFT OUTER JOIN table3 as table3a
           ON ( table2.third_id = table3.id )
WHERE t.external_id IN ( ... )
ORDER BY t.mycolumn2, t.id

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance