explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zesUH

Settings
# exclusive inclusive rows x rows loops node
1. 0.923 395,851.009 ↑ 1.0 1 1

Aggregate (cost=13,800.94..13,800.95 rows=1 width=0) (actual time=395,851.009..395,851.009 rows=1 loops=1)

2. 33.738 395,850.086 ↓ 4,750.0 4,750 1

Sort (cost=13,800.93..13,800.93 rows=1 width=24) (actual time=395,849.101..395,850.086 rows=4,750 loops=1)

  • Sort Key: wf_hz.matter_number, cplt.end_date
  • Sort Method: quicksort Memory: 564kB
3. 10.854 395,816.348 ↓ 4,750.0 4,750 1

Nested Loop Left Join (cost=1,197.45..13,800.92 rows=1 width=24) (actual time=46.210..395,816.348 rows=4,750 loops=1)

4. 14.556 395,311.494 ↓ 4,750.0 4,750 1

Nested Loop (cost=1,196.47..13,791.71 rows=1 width=43) (actual time=46.099..395,311.494 rows=4,750 loops=1)

  • Join Filter: ((ss_kh.data_no)::text = (ks_sk.data_no)::text)
5. 19.860 395,235.188 ↓ 4,750.0 4,750 1

Nested Loop (cost=1,196.05..13,791.11 rows=1 width=103) (actual time=46.072..395,235.188 rows=4,750 loops=1)

6. 1,077.557 395,125.078 ↓ 4,750.0 4,750 1

Nested Loop Left Join (cost=1,195.63..13,790.55 rows=1 width=115) (actual time=46.042..395,125.078 rows=4,750 loops=1)

7. 9.006 83.681 ↓ 1,068.0 1,068 1

Nested Loop (cost=292.28..3,013.88 rows=1 width=107) (actual time=31.877..83.681 rows=1,068 loops=1)

  • Join Filter: (((ss_kh.data_no)::text = (wf_hz.data_no)::text) AND (CASE ss_kh.sap_rk_cd WHEN 'MATTER_DELETE'::text THEN '削除済み'::text ELSE CASE wf_hz.ki_wf_st WHEN '05'::text THEN '再申請待ち'::text WHEN '10'::text THEN '審査中'::text WHEN '90'::text THEN '最終承認済'::text WHEN '91'::text THEN '取止め'::text ELSE NULL::text END END = '審査中'::text))
8. 26.614 45.839 ↓ 1.0 1,068 1

Hash Join (cost=291.86..2,396.18 rows=1,064 width=70) (actual time=31.818..45.839 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))
9. 18.066 18.066 ↑ 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.006..18.066 rows=65,810 loops=1)

10. 0.574 1.159 ↓ 1.0 1,068 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 70kB
11. 0.585 0.585 ↓ 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=36) (actual time=0.013..0.585 rows=1,068 loops=1)

  • Index Cond: (tkk_jr_yth = '2019-10-10'::date)
12. 28.836 28.836 ↑ 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=57) (actual time=0.024..0.027 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[]))
13. 1,860.456 393,963.840 ↓ 4.0 4 1,068

Hash Right Join (cost=903.35..10,776.66 rows=1 width=27) (actual time=3.872..368.880 rows=4 loops=1,068)

  • Hash Cond: ((cpl_user.system_matter_id)::text = (umttr.system_matter_id)::text)
14. 2,626.212 392,073.480 ↓ 4.2 6,571 1,068

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

15. 3,992.347 12,314.040 ↓ 1,249.2 4,997 1,068

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

  • Hash Cond: ((cpl_user.system_matter_id)::text = (ct.system_matter_id)::text)
16. 5,248.609 8,315.448 ↓ 1,249.2 4,997 1,068

Hash Join (cost=460.11..846.11 rows=4 width=108) (actual time=0.015..7.786 rows=4,997 loops=1,068)

  • Hash Cond: (((cpl_user.system_matter_id)::text = (cplt.system_matter_id)::text) AND ((cpl_user.task_id)::text = (cplt.task_id)::text))
17. 3,060.888 3,060.888 ↑ 1.0 7,848 1,068

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

  • Filter: ((locale_id)::text = 'ja'::text)
18. 2.520 5.951 ↑ 1.0 4,997 1

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

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

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

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 115kB
21. 2.644 5.721 ↓ 1.6 1,574 1

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

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

  • Filter: ((status)::text = ANY ('{apply,reapply,discontinue,pullback,sendbacktopullback,approve,sendback}'::text[]))
  • Rows Removed by Filter: 2851
23. 945.180 377,133.228 ↑ 1.0 1,574 1,068

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

24. 2,536.270 376,188.048 ↑ 1.0 1,574 1,068

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

  • 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))
25. 2,832.336 373,650.480 ↓ 21.2 1,524 1,068

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

  • Merge Cond: ((exex_user_numbering.system_matter_id)::text = (exex_user_count.system_matter_id)::text)
26. 7,622.316 370,258.512 ↓ 21.2 1,524 1,068

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

  • Filter: (exex_user_numbering.row_num = 1)
  • Rows Removed by Filter: 29833
27. 22,446.156 362,636.196 ↓ 2.2 31,357 1,068

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

28. 308,273.928 340,190.040 ↓ 2.2 31,357 1,068

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

  • Sort Key: imw_t_actv_executable_user.system_matter_id, imw_t_actv_executable_user.auth_user_code
  • Sort Method: external merge Disk: 2136kB
29. 19,368.828 31,916.112 ↓ 2.2 31,357 1,068

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

  • Hash Cond: ((imw_t_actv_executable_user.auth_user_code)::text = (us.user_cd)::text)
30. 12,539.388 12,539.388 ↑ 1.0 31,609 1,068

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

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

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

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

Seq Scan on imm_user us (cost=0.00..882.89 rows=2,695 width=7) (actual time=0.012..6.472 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. 512.389 559.632 ↑ 1.0 1,524 1,068

Sort (cost=4,033.08..4,036.96 rows=1,551 width=27) (actual time=0.051..0.524 rows=1,524 loops=1,068)

  • Sort Key: exex_user_count.system_matter_id
  • Sort Method: quicksort Memory: 168kB
34. 0.551 47.243 ↑ 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.270..47.243 rows=1,524 loops=1)

35. 11.567 46.692 ↑ 1.0 1,524 1

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

  • Group Key: imw_t_actv_executable_user_1.system_matter_id
36. 16.566 35.125 ↓ 2.2 31,357 1

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

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

  • Filter: ((locale_id)::text = 'ja'::text)
38. 1.365 7.855 ↓ 1.6 4,316 1

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 230kB
39. 6.490 6.490 ↓ 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.014..6.490 rows=4,316 loops=1)

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

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

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

42. 10.680 29.904 ↑ 1.0 1 1,068

Hash (cost=0.50..0.50 rows=1 width=19) (actual time=0.028..0.028 rows=1 loops=1,068)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 19.224 19.224 ↑ 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.017..0.018 rows=1 loops=1,068)

  • Index Cond: (system_matter_id = (wf_hz.system_matter_id)::text)
  • Heap Fetches: 1068
44. 90.250 90.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.018..0.019 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. 61.750 61.750 ↑ 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.013..0.013 rows=1 loops=4,750)

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

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

47. 33.128 418.000 ↑ 1.0 1 4,750

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

48. 66.500 66.500 ↑ 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.012..0.014 rows=6 loops=4,750)

  • Index Cond: ((wf_hz.system_matter_id)::text = (system_matter_id)::text)
49. 318.372 318.372 ↓ 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.012..0.012 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. 57.000 57.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.012..0.012 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.644 ms
Execution time : 395,852.692 ms