> 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
partnerpp ON p.partner_id = pp.id;
3. wait (or implement :-) the left join optimization in pg
Andreas
