From: Brian Fehrle <brianf@consistentstate.com>
To: pgsql-performance@postgresql.org
Subject: Re: Trying to eliminate union and sort
Date: Fri, 12 Jul 2013 16:27:36 -0600
Message-ID: <51E082D8.4030503@consistentstate.com> (raw)
In-Reply-To: <51DF51DA.7010509@agliodbs.com>
References: <51DEF924.1040809@consistentstate.com>
<WM!193bd65d81fa7c39e56918332a81c274363f4bb6c8de7151691ea39398f8f9117cf6aec5b0fd2887b97b4b5a921c1aa8!@asav-1.01.com>
<51DF51DA.7010509@agliodbs.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 07/11/2013 06:46 PM, Josh Berkus wrote:
> 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
I tried this originally, however my resulting rowcount is different.
The original query returns 9,955,729 rows
This above one returns 7,213,906
As for the counts on the tables:
table1 3,653,472
table2 2,191,314
table3 25,676,589
I think it's safe to assume right now that any resulting joins are not
one-to-one
- Brian F
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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-performance@postgresql.org
Cc: brianf@consistentstate.com
Subject: Re: Trying to eliminate union and sort
In-Reply-To: <51E082D8.4030503@consistentstate.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