Re: Trying to eliminate union and sort

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