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" <bri...@consistentstate.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@postgresql.** > org <pgsql-performance@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-performance> >
