Re: Eliminating unnecessary left joins

2007/4/7, Andreas Pflug <pgadmin@pse-consulting.de>:

> Tom Lane wrote:
>
>> "Ottó Havasvölgyi" <havasvolgyi.otto@gmail.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