explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4dke : 01

Settings
# exclusive inclusive rows x rows loops node
1. 0.942 1,067.856 ↑ 1.0 1 1

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

2. 33.761 1,066.914 ↓ 190.0 4,750 1

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

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

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

4. 8.080 623.426 ↓ 190.0 4,750 1

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

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

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

6. 5.667 503.039 ↓ 190.0 4,750 1

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

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

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

8. 5.107 27.478 ↓ 1,249.2 4,997 1

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

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

Hash Join (cost=460.11..846.11 rows=4 width=108) (actual time=6.044..16.226 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.970 3.970 ↑ 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.970 rows=7,848 loops=1)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 649kB
12. 3.472 3.472 ↑ 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.013..3.472 rows=4,997 loops=1)

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

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

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

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

  • Group Key: ct.system_matter_id
15. 3.004 3.004 ↑ 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..3.004 rows=4,997 loops=1)

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

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

17. 3.354 408.494 ↑ 1.0 1,574 1

Hash Right Join (cost=8,951.40..9,449.26 rows=1,574 width=97) (actual time=359.080..408.494 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.168 403.900 ↓ 21.2 1,524 1

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

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

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

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

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

21. 281.322 317.949 ↓ 2.2 31,357 1

Sort (cost=4,835.97..4,871.74 rows=14,309 width=59) (actual time=304.505..317.949 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.446 36.627 ↓ 2.2 31,357 1

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

  • Hash Cond: ((imw_t_actv_executable_user.auth_user_code)::text = (us.user_cd)::text)
23. 11.354 11.354 ↑ 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.009..11.354 rows=31,609 loops=1)

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

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 230kB
25. 6.402 6.402 ↓ 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.402 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.755 53.904 ↑ 1.0 1,524 1

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

  • Sort Key: exex_user_count.system_matter_id
  • Sort Method: quicksort Memory: 168kB
27. 0.536 47.149 ↑ 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.150..47.149 rows=1,524 loops=1)

28. 11.559 46.613 ↑ 1.0 1,524 1

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

  • Group Key: imw_t_actv_executable_user_1.system_matter_id
29. 16.499 35.054 ↓ 2.2 31,357 1

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

  • Hash Cond: ((imw_t_actv_executable_user_1.auth_user_code)::text = (us_1.user_cd)::text)
30. 10.801 10.801 ↑ 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.013..10.801 rows=31,609 loops=1)

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

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 230kB
32. 6.423 6.423 ↓ 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.015..6.423 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.693 1.240 ↑ 1.0 1,574 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 166kB
34. 0.547 0.547 ↑ 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.547 rows=1,574 loops=1)

35. 0.975 57.635 ↓ 42.7 1,068 1

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

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

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

37. 1.632 48.723 ↓ 42.7 1,068 1

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

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

Hash Join (cost=291.86..2,396.18 rows=1,064 width=53) (actual time=26.991..35.343 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.561 14.561 ↑ 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.007..14.561 rows=65,810 loops=1)

40. 0.516 1.084 ↓ 1.0 1,068 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 70kB
41. 0.568 0.568 ↓ 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.014..0.568 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.533 ms
Execution time : 1,069.427 ms