Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table table1 (field1 varchar(3), field2 varchar(100));
insert into table1 values
 ('a','select from svc_lz.fn_abc;svc_lz.fn_cde;')
,('b','select *From mfg_lz.fn_test,mfg_a.fn_test1')
,('c','select *From mfg_j.fn_test2')
,('d','select *From mfg_j.fn_test3;mfg_j.fn_test4;')
;
select * from table1;
create table table2 (field3 varchar(100), field4 varchar(100));
insert  into table2 values
 ('svc_lz.fn_abc','{test}')
,('svc_lz.fn_cde','{test1,test2}')
,('mfg_lz.fn_test','{test3}')
,('mfg_j.fn_test4','{test5}')
;
select * from table2;

CREATE TABLE



INSERT 0 4




field1
field2


a
select from svc_lz.fn_abc;svc_lz.fn_cde;

b
select *From mfg_lz.fn_test,mfg_a.fn_test1

c
select *From mfg_j.fn_test2

d
select *From mfg_j.fn_test3;mfg_j.fn_test4;



SELECT 4



CREATE TABLE



INSERT 0 4




field3
field4


svc_lz.fn_abc
{test}

svc_lz.fn_cde
{test1,test2}

mfg_lz.fn_test
{test3}

mfg_j.fn_test4
{test5}



SELECT 4


select field1,field2,coalesce(field3,' ') field3,coalesce(field4,' ') field4
from table1 t1
left join table2 t2 on t1.field2 like concat('%',t2.field3,'%')


field1
field2
field3
field4


a
select from svc_lz.fn_abc;svc_lz.fn_cde;
svc_lz.fn_abc
{test}

a
select from svc_lz.fn_abc;svc_lz.fn_cde;
svc_lz.fn_cde
{test1,test2}

b
select *From mfg_lz.fn_test,mfg_a.fn_test1
mfg_lz.fn_test
{test3}

c
select *From mfg_j.fn_test2
 
 

d
select *From mfg_j.fn_test3;mfg_j.fn_test4;
mfg_j.fn_test4
{test5}



SELECT 5


select * from table2
cross join string_to_table(trim(replace(replace(field4,'{',''),'}','')),',') fld


field3
field4
fld


svc_lz.fn_abc
{test}
test

svc_lz.fn_cde
{test1,test2}
test1

svc_lz.fn_cde
{test1,test2}
test2

mfg_lz.fn_test
{test3}
test3

mfg_j.fn_test4
{test5}
test5



SELECT 5


select *
from(
select field1,field2,coalesce(field3,'x ') field3,coalesce(fld,'x ') field4
from table1 t1
left join (
    select * from table2
    cross join 
       string_to_table(trim(replace(replace(field4,'{',''),'}','')),',') fld
  )t2
  on t1.field2 like concat('%',t2.field3,'%')
)x


field1
field2
field3
field4


a
select from svc_lz.fn_abc;svc_lz.fn_cde;
svc_lz.fn_abc
test

a
select from svc_lz.fn_abc;svc_lz.fn_cde;
svc_lz.fn_cde
test1

a
select from svc_lz.fn_abc;svc_lz.fn_cde;
svc_lz.fn_cde
test2

b
select *From mfg_lz.fn_test,mfg_a.fn_test1
mfg_lz.fn_test
test3

c
select *From mfg_j.fn_test2
x
x

d
select *From mfg_j.fn_test3;mfg_j.fn_test4;
mfg_j.fn_test4
test5



SELECT 6


select field1,field2,fld
from table1 t1
left join (
    select * from table2
    cross join 
       string_to_table(trim(replace(replace(field4,'{',''),'}','')),',') fld
  )t2
  on t1.field2 like concat('%',t2.field3,'%')


field1
field2
fld


a
select from svc_lz.fn_abc;svc_lz.fn_cde;
test

a
select from svc_lz.fn_abc;svc_lz.fn_cde;
test1

a
select from svc_lz.fn_abc;svc_lz.fn_cde;
test2

b
select *From mfg_lz.fn_test,mfg_a.fn_test1
test3

c
select *From mfg_j.fn_test2
null

d
select *From mfg_j.fn_test3;mfg_j.fn_test4;
test5



SELECT 6


select field1,field2,string_agg(distinct fld,',') field3
from(
select *
from(
select field1,field2,fld
from table1 t1
left join (
    select * from table2
    cross join 
       string_to_table(trim(replace(replace(field4,'{',''),'}','')),',') fld
  )t2
  on t1.field2 like concat('%',t2.field3,'%')
)x
)b
group by field1,field2


field1
field2
field3


a
select from svc_lz.fn_abc;svc_lz.fn_cde;
test,test1,test2

b
select *From mfg_lz.fn_test,mfg_a.fn_test1
test3

c
select *From mfg_j.fn_test2
null

d
select *From mfg_j.fn_test3;mfg_j.fn_test4;
test5



SELECT 4

field1	field2
a	select from svc_lz.fn_abc;svc_lz.fn_cde;
b	select *From mfg_lz.fn_test,mfg_a.fn_test1
c	select *From mfg_j.fn_test2
d	select *From mfg_j.fn_test3;mfg_j.fn_test4;

field3	field4
svc_lz.fn_abc	{test}
svc_lz.fn_cde	{test1,test2}
mfg_lz.fn_test	{test3}
mfg_j.fn_test4	{test5}

field3	field4	fld
svc_lz.fn_abc	{test}	test
svc_lz.fn_cde	{test1,test2}	test1
svc_lz.fn_cde	{test1,test2}	test2
mfg_lz.fn_test	{test3}	test3
mfg_j.fn_test4	{test5}	test5

field1	field2	field3
a	select from svc_lz.fn_abc;svc_lz.fn_cde;	test,test1,test2
b	select *From mfg_lz.fn_test,mfg_a.fn_test1	test3
c	select *From mfg_j.fn_test2	null
d	select *From mfg_j.fn_test3;mfg_j.fn_test4;	test5