{

ForeignKeyOptInfo*fkinfo= (ForeignKeyOptInfo*)lfirst(lc);

boolref_is_outer;

booluse_smallest_selectivity= false;

List*removedlist;

ListCell*cell;

ListCell*prev;

continue;

if ((jointype==JOIN_SEMI||jointype==JOIN_ANTI)&&

(ref_is_outer||bms_membership(inner_relids)!=BMS_SINGLETON))

continue;

if (ref_is_outer&&jointype!=JOIN_INNER)

if (jointype==JOIN_SEMI||jointype==JOIN_ANTI)

{

use_smallest_selectivity= true;

}

elseif (jointype==JOIN_SEMI||jointype==JOIN_ANTI)

{

if (bms_membership(inner_relids)==BMS_SINGLETON)

{

RelOptInfo*ref_rel=find_base_rel(root,fkinfo->ref_relid);

doubleref_tuples=Max(ref_rel->tuples,1.0);

RelOptInfo*ref_rel=find_base_rel(root,fkinfo->ref_relid);

doubleref_tuples=Max(ref_rel->tuples,1.0);

fkselec *=ref_rel->rows /ref_tuples;

}

{

use_smallest_selectivity= true;

}

fkselec *=ref_rel->rows /ref_tuples;

}

{

fkselec *=1.0 /ref_tuples;

}

if (use_smallest_selectivity)

{

Selectivitythisfksel=1.0;

foreach(cell,removedlist)

{

RestrictInfo*rinfo= (RestrictInfo*)lfirst(cell);

Selectivitycsel;

csel=clause_selectivity(root, (Node*)rinfo,

0,jointype,sjinfo);

thisfksel=Min(thisfksel,csel);

}

fkselec *=thisfksel;

}

}

*restrictlist=worklist;

^

HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.

--

-- test that foreign key join estimation performs sanely for outer joins

--

begin;

create table fkest (a int, b int, c int unique, primary key(a,b));

create table fkest1 (a int, b int, primary key(a,b));

insert into fkest select x/10, x%10, x from generate_series(1,1000) x;

insert into fkest1 select x/10, x%10 from generate_series(1,1000) x;

alter table fkest1

add constraint fkest1_a_b_fkey foreign key (a,b) references fkest;

analyze fkest;

analyze fkest1;

explain (costs off)

select *

from fkest f

left join fkest1 f1 on f.a = f1.a and f.b = f1.b

left join fkest1 f2 on f.a = f2.a and f.b = f2.b

left join fkest1 f3 on f.a = f3.a and f.b = f3.b

where f.c = 1;

QUERY PLAN

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

Nested Loop Left Join

-> Nested Loop Left Join

-> Nested Loop Left Join

-> Index Scan using fkest_c_key on fkest f

Index Cond: (c = 1)

-> Index Only Scan using fkest1_pkey on fkest1 f1

Index Cond: ((a = f.a) AND (b = f.b))

-> Index Only Scan using fkest1_pkey on fkest1 f2

Index Cond: ((a = f.a) AND (b = f.b))

-> Index Only Scan using fkest1_pkey on fkest1 f3

Index Cond: ((a = f.a) AND (b = f.b))

(11 rows)

rollback;

--

-- test planner's ability to mark joins as unique

--

create table j1 (id int primary key);

deletefrom xx1 using (select*from int4_tblwhere f1=xx1.x1) ss;

deletefrom xx1 using lateral (select*from int4_tblwhere f1= x1) ss;

begin;

createtablefkest (aint, bint, cint unique,primary key(a,b));

createtablefkest1 (aint, bint,primary key(a,b));

insert into fkestselect x/10, x%10, xfrom generate_series(1,1000) x;

insert into fkest1select x/10, x%10from generate_series(1,1000) x;

altertable fkest1

addconstraint fkest1_a_b_fkeyforeign key (a,b)references fkest;

analyze fkest;

analyze fkest1;

explain (costs off)

from fkest f

left join fkest1 f1onf.a=f1.aandf.b=f1.b

left join fkest1 f2onf.a=f2.aandf.b=f2.b

left join fkest1 f3onf.a=f3.aandf.b=f3.b

wheref.c=1;

rollback;