Why is join via views much slower? Both of the views are used in other selects… Migrating from DB2(where both selects run instantly) to Postgres.
DB:
<code> table SRC_1_A(id,a,b,c,...) unique index on (id,a) --80 million rows
table SRC_1_B(id,d,e,...) unique index on (id), index on(d) --10 million rows
table SRC_1_C(id,f,g,...) unique index on (id) -- 5 million rows
table SRC_2_A(id,a,b,c,...) unique index on (id,a) --40 million rows
table SRC_2_B(id,d,e,...) unique index on (id), index on(d) -- 5 million rows
table SRC_2_C(id,f,g,...) unique index on (id) -- 2 million rows
</code>
<code> table SRC_1_A(id,a,b,c,...) unique index on (id,a) --80 million rows
table SRC_1_B(id,d,e,...) unique index on (id), index on(d) --10 million rows
table SRC_1_C(id,f,g,...) unique index on (id) -- 5 million rows
table SRC_2_A(id,a,b,c,...) unique index on (id,a) --40 million rows
table SRC_2_B(id,d,e,...) unique index on (id), index on(d) -- 5 million rows
table SRC_2_C(id,f,g,...) unique index on (id) -- 2 million rows
</code>
table SRC_1_A(id,a,b,c,...) unique index on (id,a) --80 million rows
table SRC_1_B(id,d,e,...) unique index on (id), index on(d) --10 million rows
table SRC_1_C(id,f,g,...) unique index on (id) -- 5 million rows
table SRC_2_A(id,a,b,c,...) unique index on (id,a) --40 million rows
table SRC_2_B(id,d,e,...) unique index on (id), index on(d) -- 5 million rows
table SRC_2_C(id,f,g,...) unique index on (id) -- 2 million rows
view1:
<code> create view view1 as (
select src1B.id,d,f
from SRC_1_B src1B
inner join SRC_1_C src1C on src1B.id = src1C.id
UNION ALL
select src2B.id,d,f
from SRC_2_B src2B
inner join SRC_2_C src2C on src2B.id = src2C.id
)
</code>
<code> create view view1 as (
select src1B.id,d,f
from SRC_1_B src1B
inner join SRC_1_C src1C on src1B.id = src1C.id
UNION ALL
select src2B.id,d,f
from SRC_2_B src2B
inner join SRC_2_C src2C on src2B.id = src2C.id
)
</code>
create view view1 as (
select src1B.id,d,f
from SRC_1_B src1B
inner join SRC_1_C src1C on src1B.id = src1C.id
UNION ALL
select src2B.id,d,f
from SRC_2_B src2B
inner join SRC_2_C src2C on src2B.id = src2C.id
)
view2:
<code> create view view2 as (
select src1A.id,a,b,d,f
from SRC_1_A src1A
inner join SRC_1_B src1B on src1A.id = src1B.id
inner join SRC_1_C src1C on src1A.id = src1C.id
UNION ALL
select src2A.id,a,b,d,f
from SRC_2_A src2A
inner join SRC_2_B src2B on src2A.id = src2B.id
inner join SRC_2_C src2C on src2A.id = src2C.id
)
</code>
<code> create view view2 as (
select src1A.id,a,b,d,f
from SRC_1_A src1A
inner join SRC_1_B src1B on src1A.id = src1B.id
inner join SRC_1_C src1C on src1A.id = src1C.id
UNION ALL
select src2A.id,a,b,d,f
from SRC_2_A src2A
inner join SRC_2_B src2B on src2A.id = src2B.id
inner join SRC_2_C src2C on src2A.id = src2C.id
)
</code>
create view view2 as (
select src1A.id,a,b,d,f
from SRC_1_A src1A
inner join SRC_1_B src1B on src1A.id = src1B.id
inner join SRC_1_C src1C on src1A.id = src1C.id
UNION ALL
select src2A.id,a,b,d,f
from SRC_2_A src2A
inner join SRC_2_B src2B on src2A.id = src2B.id
inner join SRC_2_C src2C on src2A.id = src2C.id
)
–running more than 2 minutes
<code>select v1.d,v1.f,v2.a,v2.b
from view1 v1
inner join view2 v2 on v1.id = v2.id
where v1.d = 'XYZ';
</code>
<code>select v1.d,v1.f,v2.a,v2.b
from view1 v1
inner join view2 v2 on v1.id = v2.id
where v1.d = 'XYZ';
</code>
select v1.d,v1.f,v2.a,v2.b
from view1 v1
inner join view2 v2 on v1.id = v2.id
where v1.d = 'XYZ';
–this run instantly after rewriting view2
<code>select v1.d,v1.f,src1A.a,src1A.b
from view1 v1
inner join SRC_1_A src1A on v1.id = src1A.id
inner join SRC_1_B src1B on srcA.id = src1B.id
inner join SRC_1_C src1C on srcA.id = src1C.id
where v1.d = 'XYZ'
union all
select v1.d,v1.f,src2A.a,src2A.b
from view1 v1
inner join SRC_2_A src2A on v1.id = src2A.id
inner join SRC_2_B src2B on srcA.id = src2B.id
inner join SRC_2_C src2C on srcA.id = src2C.id
where v1.d = 'XYZ'
</code>
<code>select v1.d,v1.f,src1A.a,src1A.b
from view1 v1
inner join SRC_1_A src1A on v1.id = src1A.id
inner join SRC_1_B src1B on srcA.id = src1B.id
inner join SRC_1_C src1C on srcA.id = src1C.id
where v1.d = 'XYZ'
union all
select v1.d,v1.f,src2A.a,src2A.b
from view1 v1
inner join SRC_2_A src2A on v1.id = src2A.id
inner join SRC_2_B src2B on srcA.id = src2B.id
inner join SRC_2_C src2C on srcA.id = src2C.id
where v1.d = 'XYZ'
</code>
select v1.d,v1.f,src1A.a,src1A.b
from view1 v1
inner join SRC_1_A src1A on v1.id = src1A.id
inner join SRC_1_B src1B on srcA.id = src1B.id
inner join SRC_1_C src1C on srcA.id = src1C.id
where v1.d = 'XYZ'
union all
select v1.d,v1.f,src2A.a,src2A.b
from view1 v1
inner join SRC_2_A src2A on v1.id = src2A.id
inner join SRC_2_B src2B on srcA.id = src2B.id
inner join SRC_2_C src2C on srcA.id = src2C.id
where v1.d = 'XYZ'
1