explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 827L : stage1_perf_sl_lo_master_idx2_idx3

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 35,606.988 ↓ 0.0 0 1

Subquery Scan on __unnamed_subquery_0 (cost=2,616,166.98..2,616,167.13 rows=2 width=570) (actual time=35,606.988..35,606.988 rows=0 loops=1)

2. 0.000 35,606.987 ↓ 0.0 0 1

Unique (cost=2,616,166.98..2,616,167.11 rows=2 width=578) (actual time=35,606.987..35,606.987 rows=0 loops=1)

3. 0.017 35,606.987 ↓ 0.0 0 1

Sort (cost=2,616,166.98..2,616,166.99 rows=2 width=578) (actual time=35,606.987..35,606.987 rows=0 loops=1)

  • Sort Key: __unnamed_subquery_0_1.loan_tran_code, __unnamed_subquery_0_1.tran_date, __unnamed_subquery_0_1.st_code, __unnamed_subquery_0_1.other_st_code, __unnamed_subquery_0_1.loan_code, __unnamed_subquery_0_1.product_category_cd, __unnamed_subquery_0_1.bo_code, __unnamed_subquery_0_1.tran_id, __unnamed_subquery_0_1.transaction_id_other, __unnamed_subquery_0_1.orig_tran_code, __unnamed_subquery_0_1.refi_loan_code, __unnamed_subquery_0_1.check_status_id, __unnamed_subquery_0_1.total_due, __unnamed_subquery_0_1.reversal_cd, __unnamed_subquery_0_1.due_date, __unnamed_subquery_0_1.created_by, __unnamed_subquery_0_1.date_created, __unnamed_subquery_0_1.dtl_capxaction, __unnamed_subquery_0_1.loan_status_id, __unnamed_subquery_0_1.change_amt, __unnamed_subquery_0_1.rnk, __unnamed_subquery_0_1.mig_loan_code, __unnamed_subquery_0_1.mig_loan_tran_code, __unnamed_subquery_0_1.instance_id, __unnamed_subquery_0_1.payment_gateway_type
  • Sort Method: quicksort Memory: 25kB
4. 0.003 35,606.970 ↓ 0.0 0 1

Append (cost=1,308,083.25..2,616,166.97 rows=2 width=578) (actual time=35,606.970..35,606.970 rows=0 loops=1)

5. 0.001 17,788.507 ↓ 0.0 0 1

Subquery Scan on __unnamed_subquery_0_1 (cost=1,308,083.25..1,308,083.54 rows=1 width=270) (actual time=17,788.507..17,788.507 rows=0 loops=1)

  • Filter: (__unnamed_subquery_0_1.rnk = 1)
6. 0.000 17,788.506 ↓ 0.0 0 1

WindowAgg (cost=1,308,083.25..1,308,083.49 rows=4 width=369) (actual time=17,788.506..17,788.506 rows=0 loops=1)

7. 0.005 17,788.506 ↓ 0.0 0 1

Sort (cost=1,308,083.25..1,308,083.26 rows=4 width=244) (actual time=17,788.506..17,788.506 rows=0 loops=1)

  • Sort Key: st_lo_trans.loan_code, st_lo_trans.loan_tran_code, st_lo_trans.dtl_capxtimestamp DESC, st_lo_trans.dtl_capxrestart1 DESC
  • Sort Method: quicksort Memory: 25kB
8. 0.004 17,788.501 ↓ 0.0 0 1

Merge Join (cost=1,164,510.73..1,308,083.21 rows=4 width=244) (actual time=17,788.501..17,788.501 rows=0 loops=1)

  • Merge Cond: (slm.loan_code = st_lo_trans.loan_code)
9. 0.004 17,788.412 ↑ 20,505.0 1 1

Subquery Scan on slm (cost=1,163,093.30..1,306,628.44 rows=20,505 width=28) (actual time=17,788.412..17,788.412 rows=1 loops=1)

  • Filter: (slm.r = 1)
10. 0.007 17,788.408 ↑ 4,101,004.0 1 1

WindowAgg (cost=1,163,093.30..1,255,365.89 rows=4,101,004 width=135) (actual time=17,788.408..17,788.408 rows=1 loops=1)

11. 13,028.357 17,788.401 ↑ 4,101,004.0 1 1

Sort (cost=1,163,093.30..1,173,345.81 rows=4,101,004 width=127) (actual time=17,788.401..17,788.401 rows=1 loops=1)

  • Sort Key: st_lo_master.loan_code, st_lo_master.dtl_capxtimestamp DESC, st_lo_master.dtl_capxrestart1 DESC
  • Sort Method: external merge Disk: 494056kB
12. 4,760.044 4,760.044 ↓ 1.0 4,101,011 1

Seq Scan on st_lo_master (cost=0.00..400,667.04 rows=4,101,004 width=127) (actual time=0.011..4,760.044 rows=4,101,011 loops=1)

13. 0.010 0.085 ↓ 0.0 0 1

Sort (cost=1,417.42..1,417.46 rows=16 width=222) (actual time=0.085..0.085 rows=0 loops=1)

  • Sort Key: st_lo_trans.loan_code
  • Sort Method: quicksort Memory: 25kB
14. 0.008 0.075 ↓ 0.0 0 1

Hash Right Join (cost=1,352.67..1,417.10 rows=16 width=222) (actual time=0.075..0.075 rows=0 loops=1)

  • Hash Cond: ((la.state_cd)::bpchar = ca_ss_store.state_id)
15. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.85..64.53 rows=425 width=62) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on center_master cm (cost=1.57..25.03 rows=24 width=8) (never executed)

  • Recheck Cond: ((center_cd)::text = 'ONLINE'::text)
17. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on center_master_center_cd_idx (cost=0.00..1.56 rows=24 width=0) (never executed)

  • Index Cond: ((center_cd)::text = 'ONLINE'::text)
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using location_address_idx_srch on location_address la (cost=0.28..1.47 rows=18 width=62) (never executed)

  • Index Cond: (location_id = cm.location_id)
  • Heap Fetches: 0
19. 0.001 0.067 ↓ 0.0 0 1

Hash (cost=1,350.74..1,350.74 rows=7 width=221) (actual time=0.067..0.067 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
20. 0.001 0.066 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.52..1,350.74 rows=7 width=221) (actual time=0.066..0.066 rows=0 loops=1)

21. 0.002 0.065 ↓ 0.0 0 1

Merge Right Join (cost=3.08..1,344.94 rows=2 width=215) (actual time=0.065..0.065 rows=0 loops=1)

  • Merge Cond: (ca_ss_store.st_code = st_lo_trans.other_st_code)
22. 0.000 0.020 ↑ 5,517.0 1 1

Unique (cost=0.29..1,273.15 rows=5,517 width=8) (actual time=0.020..0.020 rows=1 loops=1)

23. 0.020 0.020 ↑ 55,173.0 1 1

Index Only Scan using idx_ca_ss_store_1 on ca_ss_store (cost=0.29..997.28 rows=55,173 width=8) (actual time=0.020..0.020 rows=1 loops=1)

  • Heap Fetches: 0
24. 0.005 0.043 ↓ 0.0 0 1

Sort (cost=2.79..2.80 rows=1 width=217) (actual time=0.043..0.043 rows=0 loops=1)

  • Sort Key: st_lo_trans.other_st_code
  • Sort Method: quicksort Memory: 25kB
25. 0.038 0.038 ↓ 0.0 0 1

Index Scan using st_lo_trans_idx4 on st_lo_trans (cost=0.56..2.78 rows=1 width=217) (actual time=0.038..0.038 rows=0 loops=1)

  • Index Cond: ((etl_modified_dttm >= to_date('07-01-2019 04:18:08 PM'::character varying, 'MM/DD/YYYY HH:MI:SS AM'::character varying)) AND (etl_modified_dttm <= to_date('07-01-2019 04:23:42 PM'::character varying, 'MM/DD/YYYY HH:MI:SS AM'::character varying)))
  • Filter: ((tran_id)::text <> 'ACLR'::text)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using bank_ach_staging_1 on bank_ach_staging (cost=0.43..2.86 rows=4 width=12) (never executed)

  • Index Cond: (loan_tran_code = nvl(st_lo_trans.orig_tran_code, st_lo_trans.loan_tran_code))
27. 0.000 17,818.460 ↓ 0.0 0 1

Subquery Scan on __unnamed_subquery_0_2 (cost=1,308,083.33..1,308,083.41 rows=1 width=270) (actual time=17,818.460..17,818.460 rows=0 loops=1)

  • Filter: (__unnamed_subquery_0_2.rnk = 1)
28. 0.002 17,818.460 ↓ 0.0 0 1

WindowAgg (cost=1,308,083.33..1,308,083.40 rows=1 width=369) (actual time=17,818.460..17,818.460 rows=0 loops=1)

29. 0.007 17,818.458 ↓ 0.0 0 1

Sort (cost=1,308,083.33..1,308,083.34 rows=1 width=244) (actual time=17,818.458..17,818.458 rows=0 loops=1)

  • Sort Key: st_lo_trans_1.loan_code, st_lo_trans_1.loan_tran_code, st_lo_trans_1.dtl_capxtimestamp DESC, st_lo_trans_1.dtl_capxrestart1 DESC
  • Sort Method: quicksort Memory: 25kB
30. 0.001 17,818.451 ↓ 0.0 0 1

Nested Loop (cost=1,164,508.53..1,308,083.32 rows=1 width=244) (actual time=17,818.451..17,818.451 rows=0 loops=1)

31. 0.001 17,818.450 ↓ 0.0 0 1

Hash Left Join (cost=1,164,508.10..1,308,080.67 rows=1 width=238) (actual time=17,818.450..17,818.450 rows=0 loops=1)

  • Hash Cond: (ca_ss_store_1.state_id = (la_1.state_cd)::bpchar)
32. 0.004 17,818.449 ↓ 0.0 0 1

Merge Join (cost=1,164,438.25..1,308,010.67 rows=2 width=237) (actual time=17,818.449..17,818.449 rows=0 loops=1)

  • Merge Cond: (slm_1.loan_code = st_lo_trans_1.loan_code)
33. 0.003 17,818.367 ↑ 20,505.0 1 1

Subquery Scan on slm_1 (cost=1,163,093.30..1,306,628.44 rows=20,505 width=28) (actual time=17,818.367..17,818.367 rows=1 loops=1)

  • Filter: (slm_1.r = 1)
34. 0.007 17,818.364 ↑ 4,101,004.0 1 1

WindowAgg (cost=1,163,093.30..1,255,365.89 rows=4,101,004 width=135) (actual time=17,818.364..17,818.364 rows=1 loops=1)

35. 13,115.310 17,818.357 ↑ 4,101,004.0 1 1

Sort (cost=1,163,093.30..1,173,345.81 rows=4,101,004 width=127) (actual time=17,818.357..17,818.357 rows=1 loops=1)

  • Sort Key: st_lo_master_1.loan_code, st_lo_master_1.dtl_capxtimestamp DESC, st_lo_master_1.dtl_capxrestart1 DESC
  • Sort Method: external merge Disk: 494056kB
36. 4,703.047 4,703.047 ↓ 1.0 4,101,011 1

Seq Scan on st_lo_master st_lo_master_1 (cost=0.00..400,667.04 rows=4,101,004 width=127) (actual time=0.015..4,703.047 rows=4,101,011 loops=1)

37. 0.008 0.078 ↓ 0.0 0 1

Sort (cost=1,344.95..1,344.96 rows=2 width=215) (actual time=0.078..0.078 rows=0 loops=1)

  • Sort Key: st_lo_trans_1.loan_code
  • Sort Method: quicksort Memory: 25kB
38. 0.003 0.070 ↓ 0.0 0 1

Merge Right Join (cost=3.08..1,344.94 rows=2 width=215) (actual time=0.070..0.070 rows=0 loops=1)

  • Merge Cond: (ca_ss_store_1.st_code = st_lo_trans_1.other_st_code)
39. 0.002 0.020 ↑ 5,517.0 1 1

Unique (cost=0.29..1,273.15 rows=5,517 width=8) (actual time=0.020..0.020 rows=1 loops=1)

40. 0.018 0.018 ↑ 55,173.0 1 1

Index Only Scan using idx_ca_ss_store_1 on ca_ss_store ca_ss_store_1 (cost=0.29..997.28 rows=55,173 width=8) (actual time=0.018..0.018 rows=1 loops=1)

  • Heap Fetches: 0
41. 0.003 0.047 ↓ 0.0 0 1

Sort (cost=2.79..2.80 rows=1 width=217) (actual time=0.047..0.047 rows=0 loops=1)

  • Sort Key: st_lo_trans_1.other_st_code
  • Sort Method: quicksort Memory: 25kB
42. 0.044 0.044 ↓ 0.0 0 1

Index Scan using st_lo_trans_idx4 on st_lo_trans st_lo_trans_1 (cost=0.56..2.78 rows=1 width=217) (actual time=0.044..0.044 rows=0 loops=1)

  • Index Cond: ((etl_modified_dttm >= to_date('07-01-2019 04:18:08 PM'::character varying, 'MM/DD/YYYY HH:MI:SS AM'::character varying)) AND (etl_modified_dttm <= to_date('07-01-2019 04:23:42 PM'::character varying, 'MM/DD/YYYY HH:MI:SS AM'::character varying)))
  • Filter: ((tran_id)::text = 'ACLR'::text)
43. 0.000 0.000 ↓ 0.0 0

Hash (cost=64.53..64.53 rows=425 width=62) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.85..64.53 rows=425 width=62) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on center_master cm_1 (cost=1.57..25.03 rows=24 width=8) (never executed)

  • Recheck Cond: ((center_cd)::text = 'ONLINE'::text)
46. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on center_master_center_cd_idx (cost=0.00..1.56 rows=24 width=0) (never executed)

  • Index Cond: ((center_cd)::text = 'ONLINE'::text)
47. 0.000 0.000 ↓ 0.0 0

Index Only Scan using location_address_idx_srch on location_address la_1 (cost=0.28..1.47 rows=18 width=62) (never executed)

  • Index Cond: (location_id = cm_1.location_id)
  • Heap Fetches: 0
48. 0.000 0.000 ↓ 0.0 0

Index Scan using bank_ach_staging_7 on bank_ach_staging bas (cost=0.43..2.65 rows=1 width=13) (never executed)

  • Index Cond: (ach_clr_tran_code = st_lo_trans_1.loan_tran_code)
Planning time : 4.084 ms
Execution time : 35,793.493 ms