Query Tuning - Mailing list pgsql-general

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=