[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Trying to eliminate union and sort
From: Vitalii Tymchyshyn <tivv00 () gmail ! com>
Date: 2013-07-17 8:24:40
Message-ID: CABWW-d0rS9rWYOw+4y-vMWjTj=_M8aBrJjcRJ3cCKdasQGZw=A () mail ! gmail ! com
[Download RAW message or body]
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@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>
>
[Attachment #3 (text/html)]
<p dir="ltr">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.<br> </p>
<div class="gmail_quote">13 ΜΙΠ. 2013 01:28, "Brian Fehrle" <<a \
href="mailto:brianf@consistentstate.com">brianf@consistentstate.com</a>> ΞΑΠΙΣ.<br \
type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"> On 07/11/2013 06:46 PM, Josh \
Berkus wrote:<br> <blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"> Brian,<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> 3. I'm trying to eliminate the union, however I have two \
problems.<br> A) I can't figure out how to have an 'or' clause in a \
single join that<br> would fetch all the correct rows. If I just do:<br>
LEFT OUTER JOIN table2 t2 ON (t2.real_id = <a href="http://t.id" \
target="_blank">t.id</a> OR t2.real_id =<br> t.backup_id), I end up with many less \
rows than the original query. B.<br> <br>
I believe the issue with this is a row could have one of three<br>
possibilities:<br>
* part of the first query but not the second -> results in 1 row after<br>
the union<br>
* part of the second query but not the first -> results in 1 row after<br>
the union<br>
* part of the first query and the second -> results in 2 rows after the<br>
union (see 'B)' for why)<br>
<br>
B) the third and fourth column in the SELECT will need to be different<br>
depending on what column the row is joined on in the LEFT OUTER JOIN to<br>
table2, so I may need some expensive case when logic to filter what is<br>
put there based on whether that row came from the first join clause, or<br>
the second.<br>
</blockquote>
No, it doesn't:<br>
<br>
SELECT <a href="http://t.id" target="_blank">t.id</a>,<br>
t.mycolumn1,<br>
table3.otherid as otherid1,<br>
table3a.otherid as otherid2,<br>
t.mycolumn2<br>
FROM t<br>
LEFT OUTER JOIN table2<br>
ON ( <a href="http://t.id" target="_blank">t.id</a> = t2.real_id OR \
t.backup_id = t2.real_id )<br> LEFT OUTER JOIN table3<br>
ON ( t.typeid = <a href="http://table3.id" target="_blank">table3.id</a> \
)<br> LEFT OUTER JOIN table3 as table3a<br>
ON ( table2.third_id = <a href="http://table3.id" \
target="_blank">table3.id</a> )<br> WHERE t.external_id IN ( ... )<br>
ORDER BY t.mycolumn2, <a href="http://t.id" target="_blank">t.id</a><br>
</blockquote>
I tried this originally, however my resulting rowcount is different.<br>
<br>
The original query returns 9,955,729 rows<br>
This above one returns 7,213,906<br>
<br>
As for the counts on the tables:<br>
table1 3,653,472<br>
table2 2,191,314<br>
table3 25,676,589<br>
<br>
I think it's safe to assume right now that any resulting joins are not \
one-to-one<br> <br>
- Brian F<br>
<br>
<br>
<br>
<br>
-- <br>
Sent via pgsql-performance mailing list (<a \
href="mailto:pgsql-performance@postgresql.org" \
target="_blank">pgsql-performance@postgresql.<u></u>org</a>)<br> To make changes to \
your subscription:<br> <a href="http://www.postgresql.org/mailpref/pgsql-performance" \
target="_blank">http://www.postgresql.org/<u></u>mailpref/pgsql-performance</a><br> \
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic