explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5osj

Settings
# exclusive inclusive rows x rows loops node
1. 1.585 1,985.170 ↑ 1.0 1 1

Aggregate (cost=14,489.51..14,489.52 rows=1 width=0) (actual time=1,985.169..1,985.170 rows=1 loops=1)

2. 3.164 1,983.585 ↓ 75.4 4,750 1

Nested Loop Left Join (cost=4,032.25..14,489.35 rows=63 width=0) (actual time=1,149.772..1,983.585 rows=4,750 loops=1)

3. 6.023 1,581.421 ↓ 75.4 4,750 1

Hash Right Join (cost=4,031.26..13,909.13 rows=63 width=19) (actual time=1,149.667..1,581.421 rows=4,750 loops=1)

  • Hash Cond: (((cpl_user.system_matter_id)::text || ''::text) = ((umttr.system_matter_id)::text || ''::text))
4. 2.594 438.083 ↓ 4.2 6,571 1

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

5. 4.475 25.958 ↓ 1,249.2 4,997 1

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

  • Hash Cond: ((cpl_user.system_matter_id)::text = (ct.system_matter_id)::text)
6. 5.703 15.314 ↓ 1,249.2 4,997 1

Hash Join (cost=460.11..846.11 rows=4 width=108) (actual time=6.140..15.314 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))
7. 3.539 3.539 ↑ 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.015..3.539 rows=7,848 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
8. 2.586 6.072 ↑ 1.0 4,997 1

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

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

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

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 115kB
11. 2.647 5.645 ↓ 1.6 1,574 1

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

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

  • Filter: ((status)::text = ANY ('{apply,reapply,discontinue,pullback,sendbacktopullback,approve,sendback}'::text[]))
  • Rows Removed by Filter: 2851
13. 1.006 409.531 ↑ 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.673..409.531 rows=1,574 loops=1)

14. 3.150 408.525 ↑ 1.0 1,574 1

Hash Right Join (cost=8,951.40..9,449.26 rows=1,574 width=97) (actual time=359.670..408.525 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))
15. 3.163 404.084 ↓ 21.2 1,524 1

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

  • Merge Cond: ((exex_user_numbering.system_matter_id)::text = (exex_user_count.system_matter_id)::text)
16. 7.139 345.863 ↓ 21.2 1,524 1

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

  • Filter: (exex_user_numbering.row_num = 1)
  • Rows Removed by Filter: 29833
17. 21.588 338.724 ↓ 2.2 31,357 1

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

18. 280.232 317.136 ↓ 2.2 31,357 1

Sort (cost=4,835.97..4,871.74 rows=14,309 width=59) (actual time=303.873..317.136 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
19. 17.648 36.904 ↓ 2.2 31,357 1

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

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

  • Filter: ((locale_id)::text = 'ja'::text)
21. 1.472 7.916 ↓ 1.6 4,316 1

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

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

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

  • Filter: (((delete_flag)::text = '0'::text) AND (start_date <= now()) AND (end_date > now()))
  • Rows Removed by Filter: 9293
23. 6.772 55.058 ↑ 1.0 1,524 1

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

  • Sort Key: exex_user_count.system_matter_id
  • Sort Method: quicksort Memory: 168kB
24. 0.558 48.286 ↑ 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=47.306..48.286 rows=1,524 loops=1)

25. 11.872 47.728 ↑ 1.0 1,524 1

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

  • Group Key: imw_t_actv_executable_user_1.system_matter_id
26. 17.197 35.856 ↓ 2.2 31,357 1

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

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

  • Filter: ((locale_id)::text = 'ja'::text)
28. 1.346 7.871 ↓ 1.6 4,316 1

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

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

  • Filter: (((delete_flag)::text = '0'::text) AND (start_date <= now()) AND (end_date > now()))
  • Rows Removed by Filter: 9293
30. 0.750 1.291 ↑ 1.0 1,574 1

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

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

32. 1.540 1,137.315 ↓ 133.5 1,068 1

Hash (cost=3,128.32..3,128.32 rows=8 width=38) (actual time=1,137.315..1,137.315 rows=1,068 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 90kB
33. 660.514 1,135.775 ↓ 133.5 1,068 1

Nested Loop Left Join (cost=293.11..3,128.32 rows=8 width=38) (actual time=27.553..1,135.775 rows=1,068 loops=1)

  • Join Filter: (((wf_hz.system_matter_id)::text || ''::text) = ((umttr.system_matter_id)::text || ''::text))
  • Rows Removed by Join Filter: 1679964
34. 1.935 85.441 ↓ 1,068.0 1,068 1

Nested Loop (cost=293.11..3,015.04 rows=1 width=19) (actual time=27.319..85.441 rows=1,068 loops=1)

  • Join Filter: ((ss_kh.data_no)::text = (ks_sk.data_no)::text)
35. 2.384 70.690 ↓ 1,068.0 1,068 1

Nested Loop (cost=292.70..3,014.44 rows=1 width=79) (actual time=27.294..70.690 rows=1,068 loops=1)

36. 3.714 56.558 ↓ 1,068.0 1,068 1

Nested Loop (cost=292.28..3,013.88 rows=1 width=91) (actual time=27.271..56.558 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))
37. 20.838 36.824 ↓ 1.0 1,068 1

Hash Join (cost=291.86..2,396.18 rows=1,064 width=70) (actual time=27.232..36.824 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))
38. 14.829 14.829 ↑ 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..14.829 rows=65,810 loops=1)

39. 0.519 1.157 ↓ 1.0 1,068 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 70kB
40. 0.638 0.638 ↓ 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.012..0.638 rows=1,068 loops=1)

  • Index Cond: (tkk_jr_yth = '2019-10-10'::date)
41. 16.020 16.020 ↑ 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=41) (actual time=0.015..0.015 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[]))
42. 11.748 11.748 ↑ 1.0 1 1,068

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=1,068)

  • Index Cond: ((ks_cd = (hz.ks_cd)::text) AND (data_kr_no = (hz.data_kr_no)::text))
  • Heap Fetches: 1068
43. 12.816 12.816 ↑ 1.0 1 1,068

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.012 rows=1 loops=1,068)

  • Index Cond: ((ks_cd = (hz.ks_cd)::text) AND (data_no = (hz.data_no)::text))
  • Heap Fetches: 1068
44. 389.820 389.820 ↑ 1.0 1,574 1,068

Seq Scan on imw_t_actv_matter umttr (cost=0.00..85.74 rows=1,574 width=19) (actual time=0.001..0.365 rows=1,574 loops=1,068)

45. 9.500 399.000 ↑ 1.0 1 4,750

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

46. 33.940 351.500 ↑ 1.0 1 4,750

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

47. 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)
48. 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
49. 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.007..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.203 ms
Execution time : 1,986.899 ms