Query Tuning

[Sonam Sharma <sonams1209@xxxxxxxxx>]

We have a query which is running slow and it's taking 26secs to complete.. we have run the analyzer also and it's taking the same time.

Any tool is there for query optimization or any suggestions.

My query plan looks like this :

CTE Scan on approvalwflscreen (cost=8736.21..8737.25 rows=52 width=1214)

CTE constants

-> Result (cost=0.00..0.01 rows=1 width=44)

CTE approval

-> Sort (cost=7793.89..7805.22 rows=4530 width=292)

Sort Key: apv_1.t616_vbu_nbr, apv_1.t617_fnc_typ_cd, apv_1.t8071_cai_ivo_id, apv_1.t8071_add_dm

-> WindowAgg (cost=0.00..7518.80 rows=4530 width=292)

-> Nested Loop (cost=0.00..7450.85 rows=4530 width=72)

Join Filter: ((apv_1.t8118_apv_sts_cd IS NULL) OR (((apv_1.t8118_apv_sts_cd = con.dummy) OR (apv_1.t8118_apv_sts_cd = con.t8118_rejected) OR (apv_1.t8118_apv_sts_cd =

con.t8118_approved) OR (apv_1.t8118_apv_sts_cd = con.t8118_pending)) AND ((apv_1.t8130_apv_job_lvl_cd = con.t8130_deflt) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_processor) OR (apv_1.t81

30_apv_job_lvl_cd = con.t8130_assistant_mgr) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_manager) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_vp) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_re

ad_only)) AND (SubPlan 2)))

-> CTE Scan on constants con (cost=0.00..0.02 rows=1 width=42)

-> Seq Scan on t8119_cai_ivo_apv_wfl apv_1 (cost=0.00..268.18 rows=9818 width=72)

SubPlan 2

-> Nested Loop (cost=0.29..3913.17 rows=9507 width=0)

-> Seq Scan on t8071_cai_ivo_hdr hdr (cost=0.00..457.98 rows=9760 width=37)

Filter: (ivo_sts_cd = ANY (ARRAY[con.dummy, con.t8070_rejct, con.t8070_pndap, con.t8070_aprvd, con.t8070_pndps, con.t8070_cmplt, con.t8070_rdpmt, con.t8

070_stgap, con.t8070_cmeim, con.t8070_pndrv, con.t8070_delet, con.t8070_cncld]))

-> Index Only Scan using t8119i0 on t8119_cai_ivo_apv_wfl apv (cost=0.29..0.34 rows=1 width=37)

Index Cond: ((t616_vbu_nbr = hdr.t616_vbu_nbr) AND (t617_fnc_typ_cd = hdr.t617_fnc_typ_cd) AND (t8071_cai_ivo_id = hdr.t8071_cai_ivo_id) AND (t8071_add_

dm = hdr.t8071_add_dm))

CTE maxapproval

-> Sort (cost=149.09..150.22 rows=453 width=12)

Sort Key: apv_2.joinkey

-> HashAggregate (cost=124.58..129.11 rows=453 width=12)

Group Key: apv_2.joinkey, apv_2.t8119_apv_seq_nbr

-> CTE Scan on approval apv_2 (cost=0.00..90.60 rows=4530 width=10)

CTE header

-> Limit (cost=508.37..649.77 rows=1 width=618)

-> Nested Loop (cost=508.37..649.77 rows=1 width=618)

Join Filter: ((hdr_1.ivo_sts_cd = con_1.dummy) OR (hdr_1.ivo_sts_cd = con_1.t8070_rejct) OR (hdr_1.ivo_sts_cd = con_1.t8070_pndap) OR (hdr_1.ivo_sts_cd = con_1.t8070_aprvd)

OR (hdr_1.ivo_sts_cd = con_1.t8070_pndps) OR (hdr_1.ivo_sts_cd = con_1.t8070_cmplt) OR (hdr_1.ivo_sts_cd = con_1.t8070_rdpmt) OR (hdr_1.ivo_sts_cd = con_1.t8070_stgap) OR (hdr_1.ivo_sts_cd

= con_1.t8070_cmeim) OR (hdr_1.ivo_sts_cd = con_1.t8070_pndrv) OR (hdr_1.ivo_sts_cd = con_1.t8070_delet) OR (hdr_1.ivo_sts_cd = con_1.t8070_cncld))

-> Hash Join (cost=508.37..646.53 rows=1 width=126)

Hash Cond: ((apv_3.t616_vbu_nbr = hdr_1.t616_vbu_nbr) AND (apv_3.t617_fnc_typ_cd = hdr_1.t617_fnc_typ_cd) AND (apv_3.t8071_cai_ivo_id = hdr_1.t8071_cai_ivo_id) AND (a

pv_3.t8071_add_dm = hdr_1.t8071_add_dm))

-> CTE Scan on approval apv_3 (cost=0.00..90.60 rows=4530 width=114)

-> Hash (cost=306.79..306.79 rows=10079 width=118)

-> Seq Scan on t8071_cai_ivo_hdr hdr_1 (cost=0.00..306.79 rows=10079 width=118)

-> CTE Scan on constants con_1 (cost=0.00..0.02 rows=1 width=