Re: Eliminating unnecessary left joins

2007/4/7, Andreas Pflug <pgadmin(at)pse-consulting(dot)de>:

> Tom Lane wrote:
>
>> "=?ISO-8859-1?Q?Ott=F3_Havasv=F6lgyi?=" <havasvolgyi(dot)otto(at)gmail(dot)com> writes:
>>
>>> When using views built with left joins, and then querying against these
>>> views, there are a lot of join in the plan that are not necessary, because I
>>> don't select/use any column of each table in the views every time. Tables
>>> that are left joined and never referenced anywhere else in the query should
>>> be removed from the plan.
>>
>> That might cause you to get the wrong number of copies of some rows ---
>> what if a row of the left table should join to multiple rows on the right?
>
>That would be trouble. But I've seen quite some cases where the right
>can contain only zero or one row, because of PK constraints. In this
> case, elimination would be safe.

I would like to mention that this kind of structure is used by
Hibernate (ORM for Java/.NET) for mapping class hierarchies. I can
attest that this optimization is supported by MS-SQL and I think (not
tested) also by Oracle.

To recapitulate, the optimization would be: Remove left outer joined
tables from the join list, if they are not used by the query, and the
join attributes are a key for it (I assume an equality join).

Typical example:

PARENT_CLASS (PK: ID)
CHILD_CLASS (PK: ID)

In query:

SELECT
P.ID
FROM
PARENT_CLASS P
LEFT OUTER JOIN CHILD_CLASS C ON P.ID = C.ID;

the join on CHILD_CLASS can be eliminated, because the join attribute
ID is a key for it, and none of its attributes are used in the query.

Hibernate:
<url:http://www.hibernate.org/>

Hibernate Inheritance Mapping:
<url:http://www.hibernate.org/hib_docs/reference/en/html/inheritance.html>

greetings,
Nicolas

--
Nicolas Barbier
http://www.gnu.org/philosophy/no-word-attachments.html