-- join removal example

drop table if exists class cascade;
create table class 
(pk integer not null primary key
,c_c1 integer
);

insert into class select generate_series(1,1000000), 246;

drop table if exists subclass1;
create table subclass1
(pk integer not null primary key
,sc1_c2 integer
,foreign key (pk) references class (pk)
);

insert into subclass1 select generate_series(1,10), generate_series(1,10);

drop table if exists subclass2;
create table subclass2
(pk integer not null primary key
,sc2_c3 integer
,foreign key (pk) references class (pk)
);

create or replace view wholeclass2tables as
select
c.pk
,c_c1
,sc1_c2
from 
class c 
left outer join 
subclass1 sc1 on c.pk = sc1.pk;

create or replace view wholeclass3tables as
select
c.pk
,c_c1
,sc1_c2
,sc2_c3
from 
(class c 
left outer join 
subclass1 sc1 on c.pk = sc1.pk) 
left outer join 
subclass2 sc2 on c.pk = sc2.pk;