Re: Trying to eliminate union and sort

I'd try to check why discounts are different. Join with 'or' should work.
Build (one query) except all (another query) and check some rows from
result.
13 лип. 2013 01:28, "Brian Fehrle" <brianf(at)consistentstate(dot)com> напис.

> 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(at)postgresql(dot)**
> org <pgsql-performance(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-performance>
>