explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H2dS

Settings
# exclusive inclusive rows x rows loops node
1. 0.937 1,071.522 ↑ 1.0 1 1

Aggregate (cost=14,047.53..14,047.54 rows=1 width=0) (actual time=1,071.522..1,071.522 rows=1 loops=1)

2. 33.957 1,070.585 ↓ 190.0 4,750 1

Sort (cost=14,047.15..14,047.21 rows=25 width=24) (actual time=1,069.575..1,070.585 rows=4,750 loops=1)

  • Sort Key: wf_hz.matter_number, cplt.end_date
  • Sort Method: quicksort Memory: 564kB
3. 8.389 1,036.628 ↓ 190.0 4,750 1

Nested Loop Left Join (cost=3,915.75..14,046.57 rows=25 width=24) (actual time=70.095..1,036.628 rows=4,750 loops=1)

4. 8.503 624.489 ↓ 190.0 4,750 1

Nested Loop (cost=3,914.77..13,816.33 rows=25 width=43) (actual time=70.000..624.489 rows=4,750 loops=1)

  • Join Filter: ((ss_kh.data_no)::text = (ks_sk.data_no)::text)
5. 7.793 563.736 ↓ 190.0 4,750 1

Nested Loop (cost=3,914.35..13,801.34 rows=25 width=103) (actual time=69.976..563.736 rows=4,750 loops=1)

6. 5.338 503.693 ↓ 190.0 4,750 1

Hash Right Join (cost=3,913.93..13,787.47 rows=25 width=115) (actual time=69.950..503.693 rows=4,750 loops=1)

  • Hash Cond: ((cpl_user.system_matter_id)::text = (umttr.system_matter_id)::text)
7. 2.673 440.689 ↓ 4.2 6,571 1

Append (cost=902.84..10,754.43 rows=1,578 width=97) (actual time=12.268..440.689 rows=6,571 loops=1)

8. 4.988 27.278 ↓ 1,249.2 4,997 1

Hash Left Join (cost=902.84..1,289.40 rows=4 width=135) (actual time=12.267..27.278 rows=4,997 loops=1)

  • Hash Cond: ((cpl_user.system_matter_id)::text = (ct.system_matter_id)::text)
9. 6.298 16.096 ↓ 1,249.2 4,997 1

Hash Join (cost=460.11..846.11 rows=4 width=108) (actual time=6.055..16.096 rows=4,997 loops=1)

  • Hash Cond: (((cpl_user.system_matter_id)::text = (cplt.system_matter_id)::text) AND ((cpl_user.task_id)::text = (cplt.task_id)::text))
10. 3.807 3.807 ↑ 1.0 7,848 1

Seq Scan on imw_t_cpl_user cpl_user (cost=0.00..327.10 rows=7,848 width=61) (actual time=0.012..3.807 rows=7,848 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
11. 2.544 5.991 ↑ 1.0 4,997 1

Hash (cost=385.15..385.15 rows=4,997 width=85) (actual time=5.991..5.991 rows=4,997 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 649kB
12. 3.447 3.447 ↑ 1.0 4,997 1

Seq Scan on imw_t_cpl_task cplt (cost=0.00..385.15 rows=4,997 width=85) (actual time=0.009..3.447 rows=4,997 loops=1)

  • Filter: ((status)::text = ANY ('{apply,reapply,discontinue,pullback,sendbacktopullback,approve,sendback}'::text[]))
  • Rows Removed by Filter: 2851
13. 0.533 6.194 ↓ 1.6 1,574 1

Hash (cost=430.19..430.19 rows=1,003 width=27) (actual time=6.194..6.194 rows=1,574 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 115kB
14. 2.776 5.661 ↓ 1.6 1,574 1

HashAggregate (cost=410.14..420.17 rows=1,003 width=27) (actual time=5.230..5.661 rows=1,574 loops=1)

  • Group Key: ct.system_matter_id
15. 2.885 2.885 ↑ 1.0 4,997 1

Seq Scan on imw_t_cpl_task ct (cost=0.00..385.15 rows=4,997 width=27) (actual time=0.006..2.885 rows=4,997 loops=1)

  • Filter: ((status)::text = ANY ('{apply,reapply,discontinue,pullback,sendbacktopullback,approve,sendback}'::text[]))
  • Rows Removed by Filter: 2851
16. 1.009 410.738 ↑ 1.0 1,574 1

Subquery Scan on *SELECT* 2 (cost=8,951.40..9,465.00 rows=1,574 width=97) (actual time=360.035..410.738 rows=1,574 loops=1)

17. 3.323 409.729 ↑ 1.0 1,574 1

Hash Right Join (cost=8,951.40..9,449.26 rows=1,574 width=97) (actual time=360.034..409.729 rows=1,574 loops=1)

  • Hash Cond: (((exex_user_numbering.system_matter_id)::text = (task_1.system_matter_id)::text) AND ((exex_user_numbering.node_id)::text = (task_1.node_id)::text))
18. 3.211 405.140 ↓ 21.2 1,524 1

Merge Join (cost=8,869.05..9,342.75 rows=72 width=67) (actual time=358.722..405.140 rows=1,524 loops=1)

  • Merge Cond: ((exex_user_numbering.system_matter_id)::text = (exex_user_count.system_matter_id)::text)
19. 7.339 347.725 ↓ 21.2 1,524 1

Subquery Scan on exex_user_numbering (cost=4,835.97..5,301.01 rows=72 width=59) (actual time=305.179..347.725 rows=1,524 loops=1)

  • Filter: (exex_user_numbering.row_num = 1)
  • Rows Removed by Filter: 29833
20. 21.737 340.386 ↓ 2.2 31,357 1

WindowAgg (cost=4,835.97..5,122.15 rows=14,309 width=59) (actual time=305.168..340.386 rows=31,357 loops=1)

21. 282.017 318.649 ↓ 2.2 31,357 1

Sort (cost=4,835.97..4,871.74 rows=14,309 width=59) (actual time=305.154..318.649 rows=31,357 loops=1)

  • Sort Key: imw_t_actv_executable_user.system_matter_id, imw_t_actv_executable_user.auth_user_code
  • Sort Method: external merge Disk: 2136kB
22. 17.488 36.632 ↓ 2.2 31,357 1

Hash Join (cost=916.58..3,848.32 rows=14,309 width=59) (actual time=7.891..36.632 rows=31,357 loops=1)

  • Hash Cond: ((imw_t_actv_executable_user.auth_user_code)::text = (us.user_cd)::text)
23. 11.295 11.295 ↑ 1.0 31,609 1

Seq Scan on imw_t_actv_executable_user (cost=0.00..892.11 rows=31,609 width=59) (actual time=0.013..11.295 rows=31,609 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
24. 1.412 7.849 ↓ 1.6 4,316 1

Hash (cost=882.89..882.89 rows=2,695 width=7) (actual time=7.849..7.849 rows=4,316 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 230kB
25. 6.437 6.437 ↓ 1.6 4,316 1

Seq Scan on imm_user us (cost=0.00..882.89 rows=2,695 width=7) (actual time=0.013..6.437 rows=4,316 loops=1)

  • Filter: (((delete_flag)::text = '0'::text) AND (start_date <= now()) AND (end_date > now()))
  • Rows Removed by Filter: 9279
26. 6.753 54.204 ↑ 1.0 1,524 1

Sort (cost=4,033.08..4,036.96 rows=1,551 width=27) (actual time=53.536..54.204 rows=1,524 loops=1)

  • Sort Key: exex_user_count.system_matter_id
  • Sort Method: quicksort Memory: 168kB
27. 0.588 47.451 ↑ 1.0 1,524 1

Subquery Scan on exex_user_count (cost=3,919.86..3,950.88 rows=1,551 width=27) (actual time=46.438..47.451 rows=1,524 loops=1)

28. 11.701 46.863 ↑ 1.0 1,524 1

HashAggregate (cost=3,919.86..3,935.37 rows=1,551 width=26) (actual time=46.438..46.863 rows=1,524 loops=1)

  • Group Key: imw_t_actv_executable_user_1.system_matter_id
29. 16.593 35.162 ↓ 2.2 31,357 1

Hash Join (cost=916.58..3,848.32 rows=14,309 width=26) (actual time=7.856..35.162 rows=31,357 loops=1)

  • Hash Cond: ((imw_t_actv_executable_user_1.auth_user_code)::text = (us_1.user_cd)::text)
30. 10.748 10.748 ↑ 1.0 31,609 1

Seq Scan on imw_t_actv_executable_user imw_t_actv_executable_user_1 (cost=0.00..892.11 rows=31,609 width=26) (actual time=0.014..10.748 rows=31,609 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
31. 1.353 7.821 ↓ 1.6 4,316 1

Hash (cost=882.89..882.89 rows=2,695 width=7) (actual time=7.821..7.821 rows=4,316 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 230kB
32. 6.468 6.468 ↓ 1.6 4,316 1

Seq Scan on imm_user us_1 (cost=0.00..882.89 rows=2,695 width=7) (actual time=0.013..6.468 rows=4,316 loops=1)

  • Filter: (((delete_flag)::text = '0'::text) AND (start_date <= now()) AND (end_date > now()))
  • Rows Removed by Filter: 9279
33. 0.702 1.266 ↑ 1.0 1,574 1

Hash (cost=58.74..58.74 rows=1,574 width=65) (actual time=1.266..1.266 rows=1,574 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 166kB
34. 0.564 0.564 ↑ 1.0 1,574 1

Seq Scan on imw_t_actv_task task_1 (cost=0.00..58.74 rows=1,574 width=65) (actual time=0.003..0.564 rows=1,574 loops=1)

35. 0.957 57.666 ↓ 42.7 1,068 1

Hash (cost=3,010.78..3,010.78 rows=25 width=126) (actual time=57.666..57.666 rows=1,068 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 181kB
36. 1.563 56.709 ↓ 42.7 1,068 1

Nested Loop Left Join (cost=292.56..3,010.78 rows=25 width=126) (actual time=27.002..56.709 rows=1,068 loops=1)

37. 1.655 48.738 ↓ 42.7 1,068 1

Nested Loop (cost=292.28..2,997.92 rows=25 width=107) (actual time=26.983..48.738 rows=1,068 loops=1)

  • Join Filter: ((ss_kh.data_no)::text = (wf_hz.data_no)::text)
38. 19.737 35.335 ↓ 1.0 1,068 1

Hash Join (cost=291.86..2,396.18 rows=1,064 width=53) (actual time=26.944..35.335 rows=1,068 loops=1)

  • Hash Cond: (((hz.ks_cd)::text = (ss_kh.ks_cd)::text) AND ((hz.data_no)::text = (ss_kh.data_no)::text))
39. 14.512 14.512 ↑ 1.0 65,810 1

Seq Scan on nwbb_t_kth_ss_hz hz (cost=0.00..1,600.10 rows=65,810 width=34) (actual time=0.005..14.512 rows=65,810 loops=1)

40. 0.514 1.086 ↓ 1.0 1,068 1

Hash (cost=275.90..275.90 rows=1,064 width=19) (actual time=1.086..1.086 rows=1,068 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 70kB
41. 0.572 0.572 ↓ 1.0 1,068 1

Index Scan using idx_nwbc_t_kth_ss_kh_00 on nwbc_t_kth_ss_kh ss_kh (cost=0.29..275.90 rows=1,064 width=19) (actual time=0.013..0.572 rows=1,068 loops=1)

  • Index Cond: (tkk_jr_yth = '2019-10-10'::date)
42. 11.748 11.748 ↑ 1.0 1 1,068

Index Scan using pk_nwa7_wf_hz_kth_ss on nwa7_wf_hz_kth_ss wf_hz (cost=0.42..0.55 rows=1 width=54) (actual time=0.011..0.011 rows=1 loops=1,068)

  • Index Cond: (((ks_cd)::text = (hz.ks_cd)::text) AND ((data_no)::text = (hz.data_no)::text))
  • Filter: ((ki_wf_st)::text = ANY ('{05,10}'::text[]))
43. 6.408 6.408 ↑ 1.0 1 1,068

Index Only Scan using imw_t_actv_matter_pkey on imw_t_actv_matter umttr (cost=0.28..0.50 rows=1 width=19) (actual time=0.006..0.006 rows=1 loops=1,068)

  • Index Cond: (system_matter_id = (wf_hz.system_matter_id)::text)
  • Heap Fetches: 1068
44. 52.250 52.250 ↑ 1.0 1 4,750

Index Only Scan using pk_nwba_t_kth_kh on nwba_t_kth_kh kh (cost=0.42..0.54 rows=1 width=18) (actual time=0.011..0.011 rows=1 loops=4,750)

  • Index Cond: ((ks_cd = (hz.ks_cd)::text) AND (data_kr_no = (hz.data_kr_no)::text))
  • Heap Fetches: 4750
45. 52.250 52.250 ↑ 1.0 1 4,750

Index Only Scan using pk_nwa8_t_ks_sk_kth_ss on nwa8_t_ks_sk_kth_ss ks_sk (cost=0.42..0.59 rows=1 width=19) (actual time=0.011..0.011 rows=1 loops=4,750)

  • Index Cond: ((ks_cd = (hz.ks_cd)::text) AND (data_no = (hz.data_no)::text))
  • Heap Fetches: 4750
46. 9.500 403.750 ↑ 1.0 1 4,750

Nested Loop (cost=0.98..9.20 rows=1 width=19) (actual time=0.052..0.085 rows=1 loops=4,750)

47. 38.690 356.250 ↑ 1.0 1 4,750

Nested Loop (cost=0.70..8.85 rows=1 width=33) (actual time=0.043..0.075 rows=1 loops=4,750)

48. 52.250 52.250 ↑ 1.0 6 4,750

Index Scan using idx_nwur_t_akb_sn_tts_pos_00 on nwur_t_akb_sn_tts_pos pos (cost=0.42..7.00 rows=6 width=54) (actual time=0.009..0.011 rows=6 loops=4,750)

  • Index Cond: ((wf_hz.system_matter_id)::text = (system_matter_id)::text)
49. 265.310 265.310 ↓ 0.0 0 26,531

Index Only Scan using idx_imw_t_actv_task_00 on imw_t_actv_task task (cost=0.28..0.30 rows=1 width=37) (actual time=0.010..0.010 rows=0 loops=26,531)

  • Index Cond: ((system_matter_id = (pos.system_matter_id)::text) AND (node_name = (pos.node_name)::text))
  • Heap Fetches: 4750
50. 38.000 38.000 ↑ 1.0 1 4,750

Index Scan using imm_dept_idx_ref_del on imm_department imd (cost=0.28..0.34 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=4,750)

  • Index Cond: (((company_cd)::text = (pos.ks_cd)::text) AND ((department_set_cd)::text = (pos.ks_cd)::text) AND ((department_cd)::text = (pos.approve_auth_dept_code)::text) AND (start_date <= now()) AND (end_date > now()) AND ((delete_flag)::text = '0'::text))
  • Filter: (((company_cd)::text = (department_set_cd)::text) AND ((locale_id)::text = 'ja'::text))
Planning time : 12.322 ms
Execution time : 1,073.150 ms