explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aeZf : Optimization for: abc; plan #cBQU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on approvalwflscreen (cost=8,736.21..8,737.25 rows=52 width=1,214) (actual rows= loops=)

  • 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.t8130_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_read_only)) AND (SubPlan 2)))
  • 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))
2.          

CTE constants

3. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=44) (actual rows= loops=)

4.          

CTE approval

5. 0.000 0.000 ↓ 0.0

Sort (cost=7,793.89..7,805.22 rows=4,530 width=292) (actual rows= loops=)

  • Sort Key: apv_1.t616_vbu_nbr, apv_1.t617_fnc_typ_cd, apv_1.t8071_cai_ivo_id, apv_1.t8071_add_dm
6. 0.000 0.000 ↓ 0.0

WindowAgg (cost=0.00..7,518.80 rows=4,530 width=292) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..7,450.85 rows=4,530 width=72) (actual rows= loops=)

  • 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 =
8. 0.000 0.000 ↓ 0.0

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

9. 0.000 0.000 ↓ 0.0

Seq Scan on t8119_cai_ivo_apv_wfl apv_1 (cost=0.00..268.18 rows=9,818 width=72) (actual rows= loops=)

10.          

SubPlan (for Nested Loop)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..3,913.17 rows=9,507 width=0) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on t8071_cai_ivo_hdr hdr (cost=0.00..457.98 rows=9,760 width=37) (actual rows= loops=)

  • 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.t8070_stgap, con.t8070_cmeim, con.t8070_pndrv, con.t8070_delet, con.t8070_cncld]))
13. 0.000 0.000 ↓ 0.0

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

  • 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))
14.          

CTE maxapproval

15. 0.000 0.000 ↓ 0.0

Sort (cost=149.09..150.22 rows=453 width=12) (actual rows= loops=)

  • Sort Key: apv_2.joinkey
16. 0.000 0.000 ↓ 0.0

HashAggregate (cost=124.58..129.11 rows=453 width=12) (actual rows= loops=)

  • Group Key: apv_2.joinkey, apv_2.t8119_apv_seq_nbr
17. 0.000 0.000 ↓ 0.0

CTE Scan on approval apv_2 (cost=0.00..90.60 rows=4,530 width=10) (actual rows= loops=)

18.          

CTE header

19. 0.000 0.000 ↓ 0.0

Limit (cost=508.37..649.77 rows=1 width=618) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=508.37..649.77 rows=1 width=618) (actual rows= loops=)

  • 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)
21. 0.000 0.000 ↓ 0.0

Hash Join (cost=508.37..646.53 rows=1 width=126) (actual rows= loops=)

  • 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 (apv_3.t8071_add_dm = hdr_1.t8071_add_dm))
22. 0.000 0.000 ↓ 0.0

CTE Scan on approval apv_3 (cost=0.00..90.60 rows=4,530 width=114) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=306.79..306.79 rows=10,079 width=118) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on t8071_cai_ivo_hdr hdr_1 (cost=0.00..306.79 rows=10,079 width=118) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

CTE Scan on constants con_1 (cost=0.00..0.02 rows=1 width=24) (actual rows= loops=)