Postgres : prevent altering join condition in plan I have a modified query from JOB benchmark:

SELECT * 
FROM   company_type AS ct, 
       info_type AS it, 
       movie_companies AS mc, 
       movie_info_idx AS mi_idx, 
       title AS t 
WHERE  ct.ct_kind = 'production companies' 
AND    it.it_info = 'top 250 rank' 
AND    t.t_production_year &gt;2010 
AND    ct.ct_id = mc.mc_company_type_id 
AND    t.t_id = mc.mc_movie_id 
AND    t.t_id = mi_idx.mii_movie_id 
AND    it.it_id = mi_idx.mii_info_type_id;


Now optimizer is introducing condition

mc.mc_movie_id = mi_idx.mii_movie_id


in plan.

Here is the plan

Hash Join  (cost=127405.50..221937.06 rows=150 width=275)
    Hash Cond: (t.t_id = mc.mc_movie_id)
    -&gt;  Seq Scan on title t  (cost=0.00..91054.20 rows=926896 width=94)
            Filter: (t_production_year &gt; 2010)
    -&gt;  Hash  (cost=127398.60..127398.60 rows=552 width=181)
            -&gt;  Hash Join  (cost=37908.32..127398.60 rows=552 width=181)
                    Hash Cond: (mc.mc_movie_id = mi_idx.mii_movie_id)  // HERE
                    -&gt;  Hash Join  (cost=18.93..89399.66 rows=20806 width=114)
                            Hash Cond: (mc.mc_company_type_id = ct.ct_id)
                            -&gt;  Seq Scan on movie_companies mc  (cost=0.00..75323.67 rows=3693067 width=28)
                            -&gt;  Hash  (cost=18.88..18.88 rows=4 width=86)
                                    -&gt;  Seq Scan on company_type ct  (cost=0.00..18.88 rows=4 width=86)
                                            Filter: ((ct_kind)::text = 'production companies'::text)
                    -&gt;  Hash  (cost=37676.66..37676.66 rows=17019 width=67)
                            -&gt;  Hash Join  (cost=2.43..37676.66 rows=17019 width=67)
                                    Hash Cond: (mi_idx.mii_info_type_id = it.it_id)
                                    -&gt;  Seq Scan on movie_info_idx mi_idx  (cost=0.00..30292.03 rows=1923203 width=49)
                                    -&gt;  Hash  (cost=2.41..2.41 rows=1 width=18)
                                            -&gt;  Seq Scan on info_type it  (cost=0.00..2.41 rows=1 width=18)
                                                    Filter: ((it_info)::text = 'top 250 rank'::text)


This condition

Hash Cond: (mc.mc_movie_id = mi_idx.mii_movie_id)


is not present in original sql query.  

My question is, how can I prevent postgres from introducing this condition in plan?

The application is written in our lab as a proof of concept which assumes that there are only pk-fk joins in the database that's why I need it this way. The input to the application is the query plan.

I know it's better this way but I'm testing an application which only supports pk-fk joins. None of mc_movie_id or mii_movie_id is a pk.  

Note: Using explicit JOIN syntax is not helping - the same plan is obtained.
Workarounds by changing the query


By changing one part to be a sub query and using OFFSET 0  

SELECT *
FROM   company_type AS ct,
       movie_companies AS mc, (
           SELECT t_id 
           FROM   info_type AS it, 
                  movie_info_idx AS mi_idx, 
                  title AS t 
           WHERE  it.it_info = 'top 250 rank' 
           AND    t.t_id = mi_idx.mii_movie_id 
           AND    it.it_id = mi_idx.mii_info_type_id 
           AND    t.t_production_year &gt;2010 OFFSET 0
           ) AS var 
WHERE  var.t_id = mc.mc_movie_id 
AND    ct.ct_kind = 'production companies' 
AND    ct.ct_id = mc.mc_company_type_id;


OFFSET 0 won't allow optimizer to pull subquery up in plan tree.  
One more workaround is to change query such that joins in query are in 
order which we want and setting join_collapse_limit = 1 in PostgreSQL 
configuration file which essentially tells optimizer not to change join 
order. More about join_collapse_limit here
Note : Explicit JOIN syntax should be used

So new query with explicit join order will look like this  

SELECT *
FROM   title AS t
JOIN   movie_info_idx AS mi_idx ON t.t_id = mi_idx.mii_movie_id
JOIN   info_type AS it ON it.it_id = mi_idx.mii_info_type_id
JOIN   movie_companies AS mc ON t.t_id = mc.mc_movie_id
JOIN   company_type AS ct ON ct.ct_id = mc.mc_company_type_id
WHERE  ct.ct_kind = 'production companies'
AND    it.it_info = 'top 250 rank'
AND    mc.mc_note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
AND    (
     mc.mc_note LIKE '%(co-production)%' 
  OR mc.mc_note LIKE '%(presents)%');  


Corresponding plan  

Hash Join  (cost=331522.33..438739.98 rows=1 width=281)
  Hash Cond: (mc.mc_company_type_id = ct.ct_id)
  -&gt;  Hash Join  (cost=331503.40..438720.17 rows=234 width=195)
        Hash Cond: (mc.mc_movie_id = t.t_id)
        -&gt;  Seq Scan on movie_companies mc  (cost=0.00..107038.63 rows=46879 width=34)
              Filter: ((mc_note !~~ '%(as Metro-Goldwyn-Mayer Pictures)%'::text) AND ((mc_note ~~ '%(co-production)%'::text) OR (mc_note ~~ '%(presents)%'::text)))
        -&gt;  Hash  (cost=331290.67..331290.67 rows=17019 width=161)
              -&gt;  Hash Join  (cost=174945.39..331290.67 rows=17019 width=161)
                    Hash Cond: (mi_idx.mii_info_type_id = it.it_id)
                    -&gt;  Hash Join  (cost=174942.96..323906.04 rows=1923203 width=143)
                          Hash Cond: (mi_idx.mii_movie_id = t.t_id)
                          -&gt;  Seq Scan on movie_info_idx mi_idx  (cost=0.00..30292.03 rows=1923203 width=49)
                          -&gt;  Hash  (cost=82545.76..82545.76 rows=3403376 width=94)
                                -&gt;  Seq Scan on title t  (cost=0.00..82545.76 rows=3403376 width=94)
                    -&gt;  Hash  (cost=2.41..2.41 rows=1 width=18)
                          -&gt;  Seq Scan on info_type it  (cost=0.00..2.41 rows=1 width=18)
                                Filter: ((it_info)::text = 'top 250 rank'::text)
  -&gt;  Hash  (cost=18.88..18.88 rows=4 width=86)
        -&gt;  Seq Scan on company_type ct  (cost=0.00..18.88 rows=4 width=86)
              Filter: ((ct_kind)::text = 'production companies'::text)