We are encountering this issue which results in poor planning for some
views.
Some examples to illustrate the issue:
-- setup
create table foo as
select id1, id2
from generate_series(1, 100) id1,
generate_series(1, 100) id2;
alter table foo add unique (id1, id2);
-- join elimination works as expected
explain (costs off)
select a.*
from foo a
left join foo b on (b.id1, b.id2) = (a.id1, a.id2)
left join foo c on (c.id1, c.id2) = (a.id1, a.id2);
-- ^^^^^^^^^^^^^^
-- QUERY PLAN
-- -------------------
-- Seq Scan on foo a
-- join elimination works as expected
explain (costs off)
select a.*
from foo a
left join foo b on (b.id1, b.id2) = (a.id1, a.id2)
left join foo c on (c.id1, c.id2) = (b.id1, b.id2);
-- ^^^^^^^^^^^^^^
-- QUERY PLAN
-- -------------------
-- Seq Scan on foo a
-- join elimination fails
-- expect both b and c to be eliminated, but b remains
explain (costs off)
select a.*
from foo a
left join foo b on (b.id1, b.id2) = (a.id1, a.id2)
left join foo c on (c.id1, c.id2) = (a.id1, b.id2);
-- ^^^^^^^^^^^^^^
-- QUERY PLAN
-- ----------------------------------------------------
-- Hash Left Join
-- Hash Cond: ((a.id1 = b.id1) AND (a.id2 = b.id2))
-- -> Seq Scan on foo a
-- -> Hash
-- -> Seq Scan on foo b
