Thread

1. Re: Eliminating unnecessary left joins

   Robert Haas <robert.haas@dyntek.com> — 2007-04-10T21:41:53Z

   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
   
   

2. Re: Eliminating unnecessary left joins

   Zeugswetter Andreas DCP SD <zeugswettera@spardat.at> — 2007-04-13T08:04:20Z

   > 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
   
   

3. Re: Eliminating unnecessary left joins

   Havasvölgyi Ottó <havasvolgyi.otto@gmail.com> — 2007-04-16T09:51:24Z

   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
   

4. Re: Eliminating unnecessary left joins

   Nicolas Barbier <nicolas.barbier@gmail.com> — 2007-04-20T21:02:52Z

   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
   
   

5. Re: Eliminating unnecessary left joins

   Havasvölgyi Ottó <havasvolgyi.otto@gmail.com> — 2007-04-21T08:55:07Z

   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
   >