#1.Query optimization (It’s taking 48 minutes to generate 5k rows).Can we optimize the time.
–Explain
( SELECT DISTINCT master_cores_conf.master_cores_conf_type_id AS corres_type,
cc.corresp_no,
ardw.ardw_remit_to_ic_902,
inv.inv_po_no_720 || inv.inv_po_no2_720 AS inv_po_no_720,
inv.inv_dun_letter_no_720,
inv.inv_date_720,
inv.inv_due_date_720,
round(EXTRACT(days FROM LOCALTIMESTAMP - inv.inv_due_date_720)) AS dayslate,
'''' AS escdayslate,
inv.inv_amt_720 / 100 AS inv_amt_720,
CASE
WHEN ((inv.inv_amt_720 - inv.inv_amt_paid_720) / 100) IS NULL THEN 0.0
ELSE (inv.inv_amt_720 - inv.inv_amt_paid_720) / 100
END AS balancedue,
( SELECT TEST.fncextendedname(cust.cust_long_name_717, cust.cust_extended_name_717,
(( SELECT other_table.other_table_tc_flag1_794
FROM other_table
WHERE other_table.other_table_key_794 = cust.cust_country_717 AND
other_table.other_table_type_794 = 'CT'))) AS fncextendedname) AS custname,
( SELECT customer.cust_curr3_717
FROM customer
WHERE customer.cust_no_717 = cic.corresp_customer_no) AS cust_curr3_717,
inv.inv_exfld12_720 AS contractno,
tax.inv_tax_no_820,
date_trunc('day', inv.inv_date_720) AS invoicedate,
CASE
WHEN COALESCE(arc.arc_close_flag_729, 'N') = 'N' THEN arc.arc_sub_tc_729
ELSE NULL::character varying
END AS isdisputed,
cic.corresp_customer_no ||
CASE
WHEN COALESCE(arc.arc_close_flag_729, 'N') = 'N' AND arc.arc_sub_tc_729 IS NOT NULL THEN '1'
WHEN inv.inv_open_item_stat1_720 = 'PP'
OR inv.inv_open_item_stat1_720 = ‘RA’ THEN ‘2’
ELSE ‘3’
END AS split_cond,
CASE
WHEN COALESCE(arc.arc_close_flag_729, ‘N’) = ‘N’ AND arc.arc_sub_tc_729 IS NOT NULL
THEN ‘Disputed Invoices’
WHEN inv.inv_open_item_stat1_720 = ‘PP’ OR inv.inv_open_item_stat1_720 = ‘RA’
THEN ‘Payment Promise’
ELSE ‘Remaining Items’
END AS sp_pps,
CASE
WHEN COALESCE(arc.arc_close_flag_729, ‘N’) = ‘N’ AND arc.arc_sub_tc_729 IS NOT NULL THEN ‘1’
WHEN inv.inv_open_item_stat1_720 = ‘PP’ OR inv.inv_open_item_stat1_720= ‘RA’ THEN ‘2’
ELSE ‘3’
END AS sp_cond,
cust.cust_lang_717,
( SELECT customer.cust_lang_717
FROM customer
WHERE customer.cust_no_717 = cic.corresp_high_parent_no) AS parentlang,
CASE
WHEN COALESCE(dcc.deta_corres_schedule_type, 0) <> 2 THEN ‘NA’
WHEN COALESCE(cd.cust_last_stmt_dt_717, ‘9999-12-31 00:00:00’::timestamp without time zone)
= ‘9999-12-31 00:00:00’::timestamp without time zone THEN ‘Y’
WHEN (cd.cust_last_stmt_dt_717::date - inv.inv_date_720::date) <= 7 THEN ‘Y’
ELSE ‘N’
END AS new_inv,
inv_ext.inv_consolidated_ref_730
FROM master_cores_conf,
invoice_extension inv_ext,
arc,
detailed_cores_conf dcc,
correspondence_inv_connector cic
INNER JOIN correspondence_customer cc ON cic.corresp_no = cc.corresp_NO,
customer cust
LEFT JOIN customer_dates cd ON cust.cust_no = cd.cust_no,
invoice inv
LEFT JOIN ARTA ardw ON inv.inv_ic_no_720
= ardw.ardw_remit_to_ic_902 AND inv.inv_curr3_720= ardw.ardw_remit_to_cur_902
LEFT JOIN ( SELECT invoice_tax.inv_tax_no_820,
invoice.inv_id_720
FROM invoice,
invoice_tax_reference,
invoice_tax
WHERE invoice_tax_reference.inv_billing_id_822 = invoice.inv_id_720
AND invoice_tax.inv_tax_id_820 = invoice_tax_reference.inv_tax_id_822)
tax ON inv.inv_id_720 = tax.inv_id_720
WHERE inv.inv_cust_no_720 = cic.corresp_customer_no
AND inv.inv_finder_no_720 = cic.corresp_invoice_finder_no AND inv_ext.inv_id_730 = inv.inv_id_720
AND cic.corresp_customer_no = cust.cust_no AND cc.corresp_deta_coresp_conf_id =
dcc.deta_cores_conf_id AND master_cores_conf.master_cores_conf_id = dcc.deta_master_cores_conf_id AND
(cc.corresp_deta_coresp_conf_id IN ( SELECT detailed_cores_conf.deta_cores_conf_id
FROM detailed_cores_conf
WHERE (detailed_cores_conf.deta_master_cores_conf_id IN ( SELECT master_cores_conf_1.master_cores_conf_id
FROM master_cores_conf master_cores_conf_1
WHERE master_cores_conf_1.master_cores_conf_type_id = ‘4’
AND master_cores_conf_1.master_cores_conf_status = ‘Y’)) AND
detailed_cores_conf.deta_cores_conf_status = ‘Y’)) AND cc.corresp_record_type = 1
AND dcc.deta_cores_conf_status = ‘Y’ AND
master_cores_conf.master_cores_conf_type_id = ‘4’)
– Unique (cost=22522758.29…22606997.48 rows=802278 width=808)
–Unique (cost=881041.17…881066.37 rows=240 width=808)
– Unique (cost=881064.97…881090.17 rows=240 width=808)