Postgres 9.5

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(50000), field2 varchar(50000));
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;')
,('e','select *From mfg_j.fn_test5(100);mfg_j.fn_test6(1000)')  
,('f','select *From mfg_j.fn_test5_abc(100);mfg_j.fn_test6_abc(1000)')   
,('J','select *From mfg_j.fn_test5_abc;mfg_j.fn_test6_abc')   
,('k','INSERT INTO abc.table2_insert_target{ name, age, city, id}
       SELECT name, age, city, id FROM abc.table1_select_source')
,('l','select service_management.analyze_table(kanban_job,mfg_lz_inc);DELETE FROM mfg_lz.icc_axbi_kanban_job  WHERE dw_upd_dtsz < To_Date(Cast(extract (year from (Current_Timestamp))-7 AS CHAR(4))||/02/01,yyyy/mm/dd);DELETE FROM mfg_lz.icc_axbi_kanban_job AWHERE EXISTS (SELECT 1 FROM mfg_lz_inc.icc_axbi_kanban_job B WHERE A.recid = B.recid and A.KANBAN=B.KANBAN);INSERT into mfg_lz.icc_axbi_kanban_job(ACTUALENDDATETIME,ACTUALENDDATETIMETZID,ACTUALPREPAREDDATETIME,ACTUALPREPAREDDATETIMETZID,ACTUALSTARTDATETIME,ACTUALSTARTDATETIMETZID,DEFAULTDIMENSION,DUEDATETIME,DUEDATETIMETZID,EXECUTABLE,EXPECTEDDATETIME,EXPECTEDDATETIMETZID,INVENTDIMDATAAREAID,INVENTDIMID,INVENTLOCATIONDATAAREAID,INVENTLOCATIONID,INVENTTRANSDATAAREAID,INVENTTRANSID,ISSUEINVENTDIMDATAAREAID,ISSUEINVENTDIMID,ISSUEINVENTTRANSDATAAREAID,ISSUEINVENTTRANSID,KANBAN,LEANPRODUCTIONFLOWACTIVITY,LEANSCHEDULEGROUPCOLOR,LEANSCHEDULEGROUPNAME,LEANSCHEDULEITEMRATIO,PLANACTIVITYNAME,QUANTITYORDERED,QUANTITYRECEIVED,QUANTITYSCRAPPED,SEQUENCE,SOURCEDOCUMENTHEADER,STATUS,TYPEE,WMSLOCATIONDATAAREAID,WMSLOCATIONID,WORKCELL,LOADPERCENT,PARTITIONN,RECID,RECVERSION,AXLWIPENDDATETIME,AXLWIPENDDATETIMETZID,AXLESTIMATEDSTARTDATETIME,AXLESTIMATEDSTARTDATETIMETZID,AXLRESOURCECONSTRAINEDJOBTIMEMINUTES,AXLKANBANACTIVITYPLANNEDSTARTDATE,AXLKANBANACTIVITYPLANNEDSTARTDATETIME,AXLKANBANACTIVITYPLANNEDSTARTDATETIMETZID,AXLKANBANACTIVITYPLANNEDENDDATETIME,AXLKANBANACTIVITYPLANNEDENDDATETIMETZID,AXLKANBANACTIVITYSCHEDULINGENDDATETIME,AXLKANBANACTIVITYSCHEDULINGENDDATETIMETZID,AXLFIS,MANUALCLEAR,MODIFIEDBY,TERMINAL,MODIFIEDDATETIME,MODIFIEDBY,CREATEDDATETIME,CREATEDBY,KFK_INS_DTSZ,SRC_SYS_NM,DW_ROW_HASH_VAL,DW_SRC_SITE_ID,DW_INS_DTSZ,DW_UPD_DTSZ,DW_LD_GRP_VAL,DW_ETL_SESS_NM)	select INC_L.ACTUALENDDATETIME,INC_L.ACTUALENDDATETIMETZID,INC_L.ACTUALPREPAREDDATETIME,INC_L.ACTUALPREPAREDDATETIMETZID,INC_L.ACTUALSTARTDATETIME,INC_L.ACTUALSTARTDATETIMETZID,INC_L.DEFAULTDIMENSION,INC_L.DUEDATETIME,INC_L.DUEDATETIMETZID,INC_L.EXECUTABLE,INC_L.EXPECTEDDATETIME,INC_L.EXPECTEDDATETIMETZID,INC_L.INVENTDIMDATAAREAID,INC_L.INVENTDIMID,INC_L.INVENTLOCATIONDATAAREAID,INC_L.INVENTLOCATIONID,INC_L.INVENTTRANSDATAAREAID,INC_L.INVENTTRANSID,INC_L.ISSUEINVENTDIMDATAAREAID,INC_L.ISSUEINVENTDIMID,INC_L.ISSUEINVENTTRANSDATAAREAID,INC_L.ISSUEINVENTTRANSID,INC_L.KANBAN,INC_L.LEANPRODUCTIONFLOWACTIVITY,INC_L.LEANSCHEDULEGROUPCOLOR,INC_L.LEANSCHEDULEGROUPNAME,INC_L.LEANSCHEDULEITEMRATIO,INC_L.PLANACTIVITYNAME,INC_L.QUANTITYORDERED,INC_L.QUANTITYRECEIVED,INC_L.QUANTITYSCRAPPED,INC_L.SEQUENCE,INC_L.SOURCEDOCUMENTHEADER,INC_L.STATUS,INC_L.TYPEE,INC_L.WMSLOCATIONDATAAREAID,INC_L.WMSLOCATIONID,INC_L.WORKCELL,INC_L.LOADPERCENT,INC_L.PARTITIONN,INC_L.RECID,INC_L.RECVERSION,INC_L.AXLWIPENDDATETIME,INC_L.AXLWIPENDDATETIMETZID,INC_L.AXLESTIMATEDSTARTDATETIME,INC_L.AXLESTIMATEDSTARTDATETIMETZID,INC_L.AXLRESOURCECONSTRAINEDJOBTIMEMINUTES,INC_L.AXLKANBANACTIVITYPLANNEDSTARTDATE,INC_L.AXLKANBANACTIVITYPLANNEDSTARTDATETIME,INC_L.AXLKANBANACTIVITYPLANNEDSTARTDATETIMETZID,INC_L.AXLKANBANACTIVITYPLANNEDENDDATETIME,INC_L.AXLKANBANACTIVITYPLANNEDENDDATETIMETZID,INC_L.AXLKANBANACTIVITYSCHEDULINGENDDATETIME,INC_L.AXLKANBANACTIVITYSCHEDULINGENDDATETIMETZID,INC_L.AXLFIS,INC_L.MANUALCLEAR,INC_L.MODIFIEDBY,INC_L.TERMINAL,INC_L.MODIFIEDDATETIME,INC_L.MODIFIEDBY,INC_L.CREATEDDATETIME,INC_L.CREATEDBY,INC_L.KFK_INS_DTSZ,COALESCE(INC_L.src_sys_nm, ) AS src_sys_nm,md5(coalesce(cast(INC_L.recid as varchar(100))||cast(INC_L.KANBAN as varchar(100))||cast(INC_L.src_sys_nm as varchar(100)),1))  as dw_row_hash_val,1040 as dw_src_site_id,current_timestamp as dw_ins_dtsz,current_timestamp as dw_upd_dtsz,cast((to_char(current_timestamp, YYYYMMDDHH24MI)||.00) as numeric) as dw_ld_grp_val,ICM_INVSSF_DDLDSC_KFK_GPSS_ICC_AXBI_KANBAN_JOB as dw_etl_sess_nm	from (select ACTUALENDDATETIME,ACTUALENDDATETIMETZID,ACTUALPREPAREDDATETIME,ACTUALPREPAREDDATETIMETZID,ACTUALSTARTDATETIME,ACTUALSTARTDATETIMETZID,DEFAULTDIMENSION,DUEDATETIME,DUEDATETIMETZID,EXECUTABLE,EXPECTEDDATETIME,EXPECTEDDATETIMETZID,INVENTDIMDATAAREAID,INVENTDIMID,INVENTLOCATIONDATAAREAID,INVENTLOCATIONID,INVENTTRANSDATAAREAID,INVENTTRANSID,ISSUEINVENTDIMDATAAREAID,ISSUEINVENTDIMID,ISSUEINVENTTRANSDATAAREAID,ISSUEINVENTTRANSID,KANBAN,LEANPRODUCTIONFLOWACTIVITY,LEANSCHEDULEGROUPCOLOR,LEANSCHEDULEGROUPNAME,LEANSCHEDULEITEMRATIO,PLANACTIVITYNAME,QUANTITYORDERED,QUANTITYRECEIVED,QUANTITYSCRAPPED,SEQUENCE,SOURCEDOCUMENTHEADER,STATUS,TYPEE,WMSLOCATIONDATAAREAID,WMSLOCATIONID,WORKCELL,LOADPERCENT,PARTITIONN,RECID,RECVERSION,AXLWIPENDDATETIME,AXLWIPENDDATETIMETZID,AXLESTIMATEDSTARTDATETIME,AXLESTIMATEDSTARTDATETIMETZID,AXLRESOURCECONSTRAINEDJOBTIMEMINUTES,AXLKANBANACTIVITYPLANNEDSTARTDATE,AXLKANBANACTIVITYPLANNEDSTARTDATETIME,AXLKANBANACTIVITYPLANNEDSTARTDATETIMETZID,AXLKANBANACTIVITYPLANNEDENDDATETIME,AXLKANBANACTIVITYPLANNEDENDDATETIMETZID,AXLKANBANACTIVITYSCHEDULINGENDDATETIME,AXLKANBANACTIVITYSCHEDULINGENDDATETIMETZID,AXLFIS,MANUALCLEAR,MODIFIEDBY,TERMINAL,MODIFIEDDATETIME,MODIFIEDBY,CREATEDDATETIME,CREATEDBY,KFK_INS_DTSZ,SRC_SYS_NM,row_number ()over (partition by recid,kanban,src_sys_nm order by kfk_ins_dtsz desc) as first_value		from mfg_lz_inc.icc_axbi_kanban_job) INC_Lwhere first_value=1;select service_management.analyze_table(icc_axbi_kanban_job,mfg_lz);')  
;
select * from table1;
create table table2 (field3 varchar(5000), field4 varchar(5000));
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}')
,('mfg_j.fn_test5','{x5}')
,('mfg_j.fn_test6','{test7}')
,('mfg_j.fn_test5_abc','{5_abc}')
,('mfg_j.fn_test6_abc','{6_abc}')
;
select * from table2;

CREATE TABLE table5(field6 varchar NULL,field7 varchar NULL,field8 varchar NULL,field9 
  varchar NULL);
insert  into table5 values('j','abc','def','xyz_inc');

select *from table5;

field6	field7	field8	field9
j	abc	def	xyz_inc

SELECT 1

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 
       unnest(string_to_array(trim(replace(replace(field4,'{',''),'}','')),',')) fld
  )t2
  on concat(t1.field2,';') similar to concat('%',t2.field3,'[();]%')
)x
)b
  where field2 like '%fn@_%' escape '@' and fld is not null
group by field1,field2
union
(with cte as (
    select field1,field2
          ,translate(''''||
              translate( btrim(field2)
                        ,'"'''
                        ,'' )
              ||''''
             ,E'\n"'
             ,'' ) as edited_field2 
  from table1)
,cte2 as (
    select *,t.spotted_table as spotted_target,s.spotted_table as spotted_source
    from cte
    left join regexp_matches(
         edited_field2
        ,'(?:UPDATE|INTO)(?:\s+ONLY)?\s+([[:alpha:]_]+[\.\w]*|"[^"]+"(?:\."[^"]+")?)'
        ,'gi'
      ) with ordinality as target_matches(hits,n1) on true
  left join unnest(target_matches.hits) with ordinality as t(spotted_table,n2) on true
  left join   regexp_matches(
         edited_field2
        ,'(?:FROM|JOIN|USING|TABLE)(?:\s+ONLY)?\s+([[:alpha:]_]+[\.\w]*|"[^"]+"(?:\."[^"]+")?)'
        ,'gi'
      ) with ordinality as source_matches(hits,n1) on true
  left join unnest(source_matches.hits) with ordinality as s(spotted_table,n2) on true)
select field1
      ,string_agg(distinct spotted_target,',') as spotted_targets
     -- ,string_agg(distinct spotted_source,',') as spotted_sources
      ,edited_field2
from cte2 where edited_field2 not like '%fn@_%' escape '@' and spotted_target is not null
and coalesce(split_part(spotted_target,'.',1) !~* '_inc"?$',true)
  and coalesce(split_part(spotted_source,'.',1) !~* '_inc"?$',true)
group by field1,edited_field2
order by field1)

field1	field2	field3
J	select *From mfg_j.fn_test5_abc;mfg_j.fn_test6_abc	5_abc,6_abc
a	select from svc_lz.fn_abc;svc_lz.fn_cde;	test,test1,test2
d	select *From mfg_j.fn_test3;mfg_j.fn_test4;	test5
e	select *From mfg_j.fn_test5(100);mfg_j.fn_test6(1000)	test7,x5
f	select *From mfg_j.fn_test5_abc(100);mfg_j.fn_test6_abc(1000)	5_abc,6_abc
k	abc.table2_insert_target	'INSERT INTO abc.table2_insert_target{ name, age, city, id} SELECT name, age, city, id FROM abc.table1_select_source'
l	mfg_lz.icc_axbi_kanban_job	'select service_management.analyze_table(kanban_job,mfg_lz_inc);DELETE FROM mfg_lz.icc_axbi_kanban_job WHERE dw_upd_dtsz < To_Date(Cast(extract (year from (Current_Timestamp))-7 AS CHAR(4))\|\|/02/01,yyyy/mm/dd);DELETE FROM mfg_lz.icc_axbi_kanban_job AWHERE EXISTS (SELECT 1 FROM mfg_lz_inc.icc_axbi_kanban_job B WHERE A.recid = B.recid and A.KANBAN=B.KANBAN);INSERT into mfg_lz.icc_axbi_kanban_job(ACTUALENDDATETIME,ACTUALENDDATETIMETZID,ACTUALPREPAREDDATETIME,ACTUALPREPAREDDATETIMETZID,ACTUALSTARTDATETIME,ACTUALSTARTDATETIMETZID,DEFAULTDIMENSION,DUEDATETIME,DUEDATETIMETZID,EXECUTABLE,EXPECTEDDATETIME,EXPECTEDDATETIMETZID,INVENTDIMDATAAREAID,INVENTDIMID,INVENTLOCATIONDATAAREAID,INVENTLOCATIONID,INVENTTRANSDATAAREAID,INVENTTRANSID,ISSUEINVENTDIMDATAAREAID,ISSUEINVENTDIMID,ISSUEINVENTTRANSDATAAREAID,ISSUEINVENTTRANSID,KANBAN,LEANPRODUCTIONFLOWACTIVITY,LEANSCHEDULEGROUPCOLOR,LEANSCHEDULEGROUPNAME,LEANSCHEDULEITEMRATIO,PLANACTIVITYNAME,QUANTITYORDERED,QUANTITYRECEIVED,QUANTITYSCRAPPED,SEQUENCE,SOURCEDOCUMENTHEADER,STATUS,TYPEE,WMSLOCATIONDATAAREAID,WMSLOCATIONID,WORKCELL,LOADPERCENT,PARTITIONN,RECID,RECVERSION,AXLWIPENDDATETIME,AXLWIPENDDATETIMETZID,AXLESTIMATEDSTARTDATETIME,AXLESTIMATEDSTARTDATETIMETZID,AXLRESOURCECONSTRAINEDJOBTIMEMINUTES,AXLKANBANACTIVITYPLANNEDSTARTDATE,AXLKANBANACTIVITYPLANNEDSTARTDATETIME,AXLKANBANACTIVITYPLANNEDSTARTDATETIMETZID,AXLKANBANACTIVITYPLANNEDENDDATETIME,AXLKANBANACTIVITYPLANNEDENDDATETIMETZID,AXLKANBANACTIVITYSCHEDULINGENDDATETIME,AXLKANBANACTIVITYSCHEDULINGENDDATETIMETZID,AXLFIS,MANUALCLEAR,MODIFIEDBY,TERMINAL,MODIFIEDDATETIME,MODIFIEDBY,CREATEDDATETIME,CREATEDBY,KFK_INS_DTSZ,SRC_SYS_NM,DW_ROW_HASH_VAL,DW_SRC_SITE_ID,DW_INS_DTSZ,DW_UPD_DTSZ,DW_LD_GRP_VAL,DW_ETL_SESS_NM) select INC_L.ACTUALENDDATETIME,INC_L.ACTUALENDDATETIMETZID,INC_L.ACTUALPREPAREDDATETIME,INC_L.ACTUALPREPAREDDATETIMETZID,INC_L.ACTUALSTARTDATETIME,INC_L.ACTUALSTARTDATETIMETZID,INC_L.DEFAULTDIMENSION,INC_L.DUEDATETIME,INC_L.DUEDATETIMETZID,INC_L.EXECUTABLE,INC_L.EXPECTEDDATETIME,INC_L.EXPECTEDDATETIMETZID,INC_L.INVENTDIMDATAAREAID,INC_L.INVENTDIMID,INC_L.INVENTLOCATIONDATAAREAID,INC_L.INVENTLOCATIONID,INC_L.INVENTTRANSDATAAREAID,INC_L.INVENTTRANSID,INC_L.ISSUEINVENTDIMDATAAREAID,INC_L.ISSUEINVENTDIMID,INC_L.ISSUEINVENTTRANSDATAAREAID,INC_L.ISSUEINVENTTRANSID,INC_L.KANBAN,INC_L.LEANPRODUCTIONFLOWACTIVITY,INC_L.LEANSCHEDULEGROUPCOLOR,INC_L.LEANSCHEDULEGROUPNAME,INC_L.LEANSCHEDULEITEMRATIO,INC_L.PLANACTIVITYNAME,INC_L.QUANTITYORDERED,INC_L.QUANTITYRECEIVED,INC_L.QUANTITYSCRAPPED,INC_L.SEQUENCE,INC_L.SOURCEDOCUMENTHEADER,INC_L.STATUS,INC_L.TYPEE,INC_L.WMSLOCATIONDATAAREAID,INC_L.WMSLOCATIONID,INC_L.WORKCELL,INC_L.LOADPERCENT,INC_L.PARTITIONN,INC_L.RECID,INC_L.RECVERSION,INC_L.AXLWIPENDDATETIME,INC_L.AXLWIPENDDATETIMETZID,INC_L.AXLESTIMATEDSTARTDATETIME,INC_L.AXLESTIMATEDSTARTDATETIMETZID,INC_L.AXLRESOURCECONSTRAINEDJOBTIMEMINUTES,INC_L.AXLKANBANACTIVITYPLANNEDSTARTDATE,INC_L.AXLKANBANACTIVITYPLANNEDSTARTDATETIME,INC_L.AXLKANBANACTIVITYPLANNEDSTARTDATETIMETZID,INC_L.AXLKANBANACTIVITYPLANNEDENDDATETIME,INC_L.AXLKANBANACTIVITYPLANNEDENDDATETIMETZID,INC_L.AXLKANBANACTIVITYSCHEDULINGENDDATETIME,INC_L.AXLKANBANACTIVITYSCHEDULINGENDDATETIMETZID,INC_L.AXLFIS,INC_L.MANUALCLEAR,INC_L.MODIFIEDBY,INC_L.TERMINAL,INC_L.MODIFIEDDATETIME,INC_L.MODIFIEDBY,INC_L.CREATEDDATETIME,INC_L.CREATEDBY,INC_L.KFK_INS_DTSZ,COALESCE(INC_L.src_sys_nm, ) AS src_sys_nm,md5(coalesce(cast(INC_L.recid as varchar(100))\|\|cast(INC_L.KANBAN as varchar(100))\|\|cast(INC_L.src_sys_nm as varchar(100)),1)) as dw_row_hash_val,1040 as dw_src_site_id,current_timestamp as dw_ins_dtsz,current_timestamp as dw_upd_dtsz,cast((to_char(current_timestamp, YYYYMMDDHH24MI)\|\|.00) as numeric) as dw_ld_grp_val,ICM_INVSSF_DDLDSC_KFK_GPSS_ICC_AXBI_KANBAN_JOB as dw_etl_sess_nm from (select ACTUALENDDATETIME,ACTUALENDDATETIMETZID,ACTUALPREPAREDDATETIME,ACTUALPREPAREDDATETIMETZID,ACTUALSTARTDATETIME,ACTUALSTARTDATETIMETZID,DEFAULTDIMENSION,DUEDATETIME,DUEDATETIMETZID,EXECUTABLE,EXPECTEDDATETIME,EXPECTEDDATETIMETZID,INVENTDIMDATAAREAID,INVENTDIMID,INVENTLOCATIONDATAAREAID,INVENTLOCATIONID,INVENTTRANSDATAAREAID,INVENTTRANSID,ISSUEINVENTDIMDATAAREAID,ISSUEINVENTDIMID,ISSUEINVENTTRANSDATAAREAID,ISSUEINVENTTRANSID,KANBAN,LEANPRODUCTIONFLOWACTIVITY,LEANSCHEDULEGROUPCOLOR,LEANSCHEDULEGROUPNAME,LEANSCHEDULEITEMRATIO,PLANACTIVITYNAME,QUANTITYORDERED,QUANTITYRECEIVED,QUANTITYSCRAPPED,SEQUENCE,SOURCEDOCUMENTHEADER,STATUS,TYPEE,WMSLOCATIONDATAAREAID,WMSLOCATIONID,WORKCELL,LOADPERCENT,PARTITIONN,RECID,RECVERSION,AXLWIPENDDATETIME,AXLWIPENDDATETIMETZID,AXLESTIMATEDSTARTDATETIME,AXLESTIMATEDSTARTDATETIMETZID,AXLRESOURCECONSTRAINEDJOBTIMEMINUTES,AXLKANBANACTIVITYPLANNEDSTARTDATE,AXLKANBANACTIVITYPLANNEDSTARTDATETIME,AXLKANBANACTIVITYPLANNEDSTARTDATETIMETZID,AXLKANBANACTIVITYPLANNEDENDDATETIME,AXLKANBANACTIVITYPLANNEDENDDATETIMETZID,AXLKANBANACTIVITYSCHEDULINGENDDATETIME,AXLKANBANACTIVITYSCHEDULINGENDDATETIMETZID,AXLFIS,MANUALCLEAR,MODIFIEDBY,TERMINAL,MODIFIEDDATETIME,MODIFIEDBY,CREATEDDATETIME,CREATEDBY,KFK_INS_DTSZ,SRC_SYS_NM,row_number ()over (partition by recid,kanban,src_sys_nm order by kfk_ins_dtsz desc) as first_value from mfg_lz_inc.icc_axbi_kanban_job) INC_Lwhere first_value=1;select service_management.analyze_table(icc_axbi_kanban_job,mfg_lz);'

SELECT 7