Postgres views cannot use both union and join/where

Hi,

We are trying to use the postgres view to accommodate some of the complex
workflow related operations, we perform we saw like using union in a where
clause inside a view actually pushed the where clause to both subqueries
and we get good performance using the index , but when used in a join it
does a full scan and filter of the table instead of pushing the filter
column as a where clause. we also found that when used without any
join/where in the union clause (*i.e.,* *select ... from template union all
select ... from template_staging)* works with joins just fine , i think the
only problem is when we try to use both union and where/join the issue
starts to happen is there any specific flag or release planned to address
this issue.

Postgres version: PostgreSQL 12.7 (Debian 12.7-1.pgdg100+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

*SQL Steps:*

create table template
(
id int primary key,
name varchar(30) unique,
description varchar(30)
);

create table template_staging
(
id int primary key,
name varchar(30) unique,
description varchar(30),
is_deleted bool
);

insert into template (id, name, description)
values (1, 'test1', 'hello'),
(2, 'test2', 'hello world 2'),
(3, 'test3', 'hello world 3');
insert into template_staging (id, name, description, is_deleted)
values (3, 'test3', 'revert hello world 3', false),
(4, 'test4', 'hello world 2', false),
(5, 'test5', 'hello world 3', false);

create view template_view (id, name, description, is_staged) as
select t.id,t.name, t.description, false as is_staged
from template t
left join template_staging ts on t.name = ts.name and ts.name is null
UNION ALL
select t.id, t.name, t.description, true as is_stage
from template_staging t
where is_deleted is false;

create table tester(
id int primary key,
template_id int
);
insert into tester (id, template_id)
values (1, 1),
(2, 2),
(3, 3),(4, 4);

*Analysis:*

*EXPLAIN ANALYZE select * from template_view where id=1;*

Append (cost=0.15..16.36 rows=2 width=161) (actual time=0.012..0.015
rows=1 loops=1)
-> Index Scan using template_pkey on template t (cost=0.15..8.17
rows=1 width=161) (actual time=0.011..0.012 rows=1 loops=1)
Index Cond: (id = 1)
-> Index Scan using template_staging_pkey on template_staging t_1
(cost=0.15..8.17 rows=1 width=161) (actual time=0.002..0.002 rows=0
loops=1)
Index Cond: (id = 1)
Filter: (is_deleted IS FALSE)

*EXPLAIN ANALYZE select * from template_view where name='test1';*

Append (cost=0.15..16.36 rows=2 width=157) (actual time=0.012..0.015
rows=1 loops=1)
-> Index Scan using template_name_key on template t (cost=0.15..8.17
rows=1 width=157) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: ((name)::text = 'test1'::text)
-> Index Scan using template_staging_name_key on template_staging t_1
(cost=0.15..8.17 rows=1 width=157) (actual time=0.002..0.002 rows=0
loops=1)
Index Cond: ((name)::text = 'test1'::text)
Filter: (is_deleted IS FALSE)

*EXPLAIN ANALYZE select * from tester t inner join template_view tv on
tv.id <http://tv.id> = t.template_idwhere t.id <http://t.id>=1;*

Hash Join (cost=8.18..48.19 rows=3 width=169) (actual
time=0.024..0.032 rows=1 loops=1)
Hash Cond: (t_1.id = t.template_id)
-> Append (cost=0.00..38.27 rows=645 width=161) (actual
time=0.008..0.014 rows=6 loops=1)
-> Seq Scan on template t_1 (cost=0.00..14.30 rows=430
width=161) (actual time=0.008..0.009 rows=3 loops=1)
-> Seq Scan on template_staging t_2 (cost=0.00..14.30
rows=215 width=161) (actual time=0.003..0.004 rows=3 loops=1)
Filter: (is_deleted IS FALSE)
-> Hash (cost=8.17..8.17 rows=1 width=8) (actual time=0.011..0.011
rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan using tester_pkey on tester t (cost=0.15..8.17
rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (id = 1)

*EXPLAIN (ANALYZE, BUFFERS) select * from template_view where id=1;*

Append (cost=0.15..16.36 rows=2 width=161) (actual time=0.011..0.015
rows=1 loops=1)
Buffers: shared hit=3
-> Index Scan using template_pkey on template t (cost=0.15..8.17
rows=1 width=161) (actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (id = 1)
Buffers: shared hit=2
-> Index Scan using template_staging_pkey on template_staging t_1
(cost=0.15..8.17 rows=1 width=161) (actual time=0.002..0.002 rows=0
loops=1)
Index Cond: (id = 1)
Filter: (is_deleted IS FALSE)
Buffers: shared hit=1

*EXPLAIN (ANALYZE, BUFFERS) select * from tester t inner join
template_view tv on tv.id <http://tv.id> = t.template_idwhere t.id
<http://t.id>=1;*

Hash Join (cost=8.18..48.19 rows=3 width=169) (actual
time=0.019..0.025 rows=1 loops=1)
Hash Cond: (t_1.id = t.template_id)
Buffers: shared hit=4
-> Append (cost=0.00..38.27 rows=645 width=161) (actual
time=0.007..0.011 rows=6 loops=1)
Buffers: shared hit=2
-> Seq Scan on template t_1 (cost=0.00..14.30 rows=430
width=161) (actual time=0.006..0.007 rows=3 loops=1)
Buffers: shared hit=1
-> Seq Scan on template_staging t_2 (cost=0.00..14.30
rows=215 width=161) (actual time=0.002..0.003 rows=3 loops=1)
Filter: (is_deleted IS FALSE)
Buffers: shared hit=1
-> Hash (cost=8.17..8.17 rows=1 width=8) (actual time=0.008..0.009
rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=2
-> Index Scan using tester_pkey on tester t (cost=0.15..8.17
rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (id = 1)
Buffers: shared hit=2

Please let me know if you need more info.

Thanks,

Mithran