DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=gvBR2AuHCdRov/uBnv5hTjdFA/3qQkhap9/0BmXlbvQk+8YMXiRhNFpN6pEQL1LP0bxDETIyJ1l6dmSwBl0wgWKlYSMEtBNxaR1EbBecRhRzG6UlTp5pErduXzP7MfMnb9CFWGT5dxkVWLQG59v3a5R06OFCLkAK6IrfWtojOkg=
Message-ID: <34608c0c0601120418n1af567b9n@mail.gmail.com>
Date: Thu, 12 Jan 2006 13:18:58 +0100
From: =?ISO-8859-1?Q?Ott=F3_Havasv=F6lgyi?= <havasvolgyi.otto@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Throwing unnecessary joins away
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_11612_32089099.1137068338744"

------=_Part_11612_32089099.1137068338744
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all,

Is PostgreSQL able to throw unnecessary joins?
For example I have two tables, and I join then with their primary keys, say
type of bigint . In this case if I don't reference to one of the
tables anywhere except the join condition, then the join can be eliminated.
Or if I do a "table1 left join table2 (table1.referer=3Dtable2.id)"  (N : 1
relationship), and I don't reference table2 anywhere else, then it is
unnecessary.
Primary key - primary key joins are often generated by O/R mappers. These
generated queries could be optimized even more by not joining if not
necessary.

You may say that I should not write such queries. The truth is that the O/R
mapper is generating queries on views, and it does not use every field ever=
y
time, but even so the query of the view is executed with the same plan by
PostgreSQL, although some joins are unnecessary.

So basically this all is relevant only with views.

Best Regards,
Otto

------=_Part_11612_32089099.1137068338744
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<div>Hi all,</div>
<div>&nbsp;</div>
<div>Is PostgreSQL able to throw unnecessary joins?</div>
<div>For example I have two tables, and I join then with their primary keys=
, say type of bigint&nbsp;. In this case if I don't reference to one of the=
 tables&nbsp;anywhere except the join condition, then&nbsp;the join can be =
eliminated.=20
</div>
<div>Or if I do a &quot;table1 left join table2 (table1.referer=3Dtable2.id=
)&quot;&nbsp; (N : 1 relationship), and I don't reference table2 anywhere e=
lse, then it is unnecessary.</div>
<div>Primary key - primary key joins are often generated by O/R mappers. Th=
ese generated queries could be optimized even more by not joining if not ne=
cessary.</div>
<div>&nbsp;</div>
<div>You may say that I should not write such queries. The truth is that th=
e O/R mapper is generating queries on views, and it does not use every fiel=
d every time, but even so the query of the view is executed&nbsp;with the s=
ame plan by PostgreSQL, although some joins are unnecessary.
</div>
<div>&nbsp;</div>
<div>So basically this all is relevant only with views.</div>
<div>&nbsp;</div>
<div>Best Regards,</div>
<div>Otto</div>

------=_Part_11612_32089099.1137068338744--