I have this exact problem a lot. There are actually cases where you can
eliminate regular joins, not just left joins. For example:

CREATE TABLE partner (
id serial,
name varchar(40) not null,
primary key (id)
);

CREATE TABLE project (
id serial,
name varchar(40) not null,
partner_id integer not null references project (id)
);

CREATE VIEW project_view AS
SELECT p.id, p.name, p.partner_id, pp.name AS partner
FROM project p, partner pp
WHERE p.partner_id = pp.id;

If someone does a select from project_view and does not select the
partner column, the join can be eliminated, because the not null and
foreign key constraints on the partner_id column guarantee that there
will always be exactly one matching row in the project table.

If you didn't have the NOT NULL constraint on the partner_id column,
you'd have to write the view this way, as described in the original
email:

CREATE VIEW project_view AS
SELECT p.id, p.name, p.partner_id, pp.name AS partner
FROM project p LEFT JOIN partner pp ON p.partner_id = pp.id;

In this example, I just have one join, so the benefit to eliminating it
is minimal (unless the tables are very large). But in the real
application, project_view joins the project table against six other
tables using inner joins (all against the primary keys of those other
tables) and four additional tables using left joins (also against the
primary keys of those other tables). Most queries only use a subset of
these columns - a typical query requires evaluating only about three of
the ten joins.

....Robert

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> I have this exact problem a lot. There are actually cases
> where you can eliminate regular joins, not just left joins.
> For example:
>
> CREATE TABLE partner (
> id serial,
> name varchar(40) not null,
> primary key (id)
> );
>
> CREATE TABLE project (
> id serial,
> name varchar(40) not null,
> partner_id integer not null references project (id)

^^^^^^^ -- I assume typo, should be partner
> );
>
> CREATE VIEW project_view AS
> SELECT p.id, p.name, p.partner_id, pp.name AS partner FROM
> project p, partner pp WHERE p.partner_id = pp.id;

Same advice to you:

1. add not null to your id's
2. CREATE VIEW project_view AS
SELECT p.id, p.name, p.partner_id, pp.name AS partner FROM
project p left outer join partner pp ON p.partner_id = pp.id;
3. wait (or implement :-) the left join optimization in pg

Andreas

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Hi,

Could you Bruce please add a TODO item for this feature?
The description could look something like this:

Eliminate the table T from the query/subquery if the following requirements
are satisfied:
1. T is left joined
2. T is referenced only in the join expression where it is left joined
3. the left join's join expression is a simple equality expression like
T1.C1=T2.C2; T1!=T2 and (T==T1 or T==T2)
4. the column of T in the join exression is the primary key of T

----------------------------------------------------

I hope it is comlete.
I think this is the simplest case, so we should start with this.

Thanks,
Otto

2007/4/16, Ottó Havasvölgyi <havasvolgyi.otto@gmail.com>:

> Eliminate the table T from the query/subquery if the following requirements
> are satisfied:
> 1. T is left joined
> 2. T is referenced only in the join expression where it is left joined
> 3. the left join's join expression is a simple equality expression like
> T1.C1=T2.C2; T1!=T2 and (T==T1 or T==T2)
> 4. the column of T in the join exression is the primary key of T

Condition 4 should be: the column of T in the join expression is a key
of T (i.e. it doesn't need to be the PK, a UNIQUE constraint would be
enough).

This process can be done recursively (implementation doesn't have to
be recursive, of course), to eliminate whole sub-trees of the join
tree.

Nicolas

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

But then what about the null values? Perhaps unique + notnull is better?

Otto


2007/4/20, Nicolas Barbier <nicolas.barbier@gmail.com>:
>
> 2007/4/16, Ottó Havasvölgyi <havasvolgyi.otto@gmail.com>:
>
> > Eliminate the table T from the query/subquery if the following
> requirements
> > are satisfied:
> > 1. T is left joined
> > 2. T is referenced only in the join expression where it is left joined
> > 3. the left join's join expression is a simple equality expression like
> > T1.C1=T2.C2; T1!=T2 and (T==T1 or T==T2)
> > 4. the column of T in the join exression is the primary key of T
>
> Condition 4 should be: the column of T in the join expression is a key
> of T (i.e. it doesn't need to be the PK, a UNIQUE constraint would be
> enough).
>
> This process can be done recursively (implementation doesn't have to
> be recursive, of course), to eliminate whole sub-trees of the join
> tree.
>
> Nicolas
>
> --
> Nicolas Barbier
> http://www.gnu.org/philosophy/no-word-attachments.html
>