Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

Disabling Join Elimination for LEFT Joins ​

Join elimination is a query optimization that can occur during planning when primary and foreign keys exist on the tables. The query plan may eliminate redundant joins when these key relationships have been declared. However, because Yellowbrick does not enforce primary key and foreign key constraints, your tables may have key relationships that are not valid. If this is true, the elimination of joins between those tables may cause queries to return wrong results.

To prevent this problem from occurring specifically for LEFT OUTER joins, you can set the enable_join_elimination configuration parameter to OFF. This parameter defaults to ON. Note that changing this parameter to OFF does not influence the planning of other joins, such as INNER joins.

For example, consider the primary and foreign key constraints for the following tables:

yellowbrick=# \d match
               Table "public.match"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | not null
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 ftscore  | character(3)                | 
 htscore  | character(3)                | 

Distribution: Hash (seasonid)
Columns:
   seasonid PRIMARY KEY
Foreign-key constraints:
   "season_fk" FOREIGN KEY (seasonid) REFERENCES season(seasonid)
Primary-key constraints:
   "match_pk" PRIMARY KEY (seasonid)

yellowbrick=# \d season
             Table "public.season"
   Column    |         Type          | Modifiers 
-------------+-----------------------+-----------
 seasonid    | smallint              | 
 season_name | character(9)          | 
 numteams    | smallint              | 
 winners     | character varying(30) | 

Distribution: Hash (seasonid)
Columns:
   seasonid UNIQUE
Unique constraints:
   "season_seasonid_key" UNIQUE (seasonid)
Referenced by:
   TABLE "match" CONSTRAINT "season_fk" FOREIGN KEY (seasonid) REFERENCES season(seasonid)

By default, the following LEFT OUTER join of these tables eliminates the join from the query plan:

yellowbrick=# show enable_join_elimination;
 enable_join_elimination 
-------------------------
 on
(1 row)
yellowbrick=# explain select m.* from match m left join season s on m.seasonid=s.seasonid;
                   QUERY PLAN                    
--------------------------------------------------
 id   rows_planned   workers   node   
  1           8606       all   SELECT   
  3           8606       all   SCAN match AS m   
 Database: yellowbrick
 Version: 5.2.9-20211208203120
 Hostname: yb007-mgr1.mvw.yellowbrick.io
 
(7 rows)

When you set enable_join_elimination to OFF, the plan changes:

yellowbrick=# set enable_join_elimination to off;
SET
yellowbrick=# explain select m.* from match m left join season s on m.seasonid=s.seasonid;
                                    QUERY PLAN                                     
------------------------------------------------------------------------------------
 id   rows_planned   workers   node   
  1           8606       all   SELECT   
  9           8606       all   LEFT OUTER HASH JOIN ON (s.seasonid = m.seasonid)   
 12           8606       all   |-SCAN match AS m   
  2             25       all   |-BUILD   
  5             25       all     SCAN season AS s   
 Database: yellowbrick
 Version: 5.2.9-20211208203120
 Hostname: yb007-mgr1.mvw.yellowbrick.io
 
(10 rows)

Here is another example with the same two tables:

yellowbrick=# show enable_join_elimination;
 enable_join_elimination 
-------------------------
 on
(1 row)
yellowbrick=# explain select distinct(m.seasonid) from match m left join season s on m.seasonid=s.seasonid;
                      QUERY PLAN                       
--------------------------------------------------------
 id   rows_planned   workers   node   
  1           8606       all   SELECT   
  3           8606       all   GROUP BY (m.seasonid)   
  6           8606       all   SCAN match AS m   
 Database: yellowbrick
 Version: 5.2.9-20211208203120
 Hostname: yb06-mgr1.slc.yellowbrick.io
 
(8 rows)

yellowbrick=# set enable_join_elimination to off;
SET
yellowbrick=# explain select distinct(m.seasonid) from match m left join season s on m.seasonid=s.seasonid;
                                    QUERY PLAN                                     
------------------------------------------------------------------------------------
 id   rows_planned   workers   node   
  1           8606       all   SELECT   
  3           8606       all   GROUP BY (m.seasonid)   
 11           8606       all   LEFT OUTER HASH JOIN ON (s.seasonid = m.seasonid)   
 14           8606       all   |-SCAN match AS m   
  4             25       all   |-BUILD   
  7             25       all     SCAN season AS s   
 Database: yellowbrick
 Version: 5.2.9-20211208203120
 Hostname: yb06-mgr1.slc.yellowbrick.io
 
(11 rows)

Parent topic:CREATE TABLE