explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kmj2 : 03_対策3 SQL(UNION削除)

Settings
# exclusive inclusive rows x rows loops node
1. 6,964.222 24,940.179 ↓ 6.7 425,637 1

Sort (cost=260,396.09..260,554.25 rows=63,264 width=839) (actual time=23,700.496..24,940.179 rows=425,637 loops=1)

  • Sort Key: wf_hz.matter_number, his.end_date
  • Sort Method: external merge Disk: 127080kB
2. 932.847 17,975.957 ↓ 6.7 425,637 1

Merge Left Join (cost=221,909.40..231,995.57 rows=63,264 width=839) (actual time=15,514.501..17,975.957 rows=425,637 loops=1)

  • Merge Cond: ((wf_hz.system_matter_id)::text = (pos.system_matter_id)::text)
3. 345.860 15,978.105 ↓ 6.7 425,637 1

Merge Left Join (cost=206,618.21..213,541.14 rows=63,264 width=787) (actual time=14,449.813..15,978.105 rows=425,637 loops=1)

  • Merge Cond: ((wf_hz.system_matter_id)::text = (umttr.system_matter_id)::text)
4. 638.919 1,245.755 ↓ 1.0 63,300 1

Sort (cost=39,117.55..39,275.71 rows=63,264 width=128) (actual time=1,133.003..1,245.755 rows=63,300 loops=1)

  • Sort Key: wf_hz.system_matter_id
  • Sort Method: external merge Disk: 8904kB
5. 106.870 606.836 ↓ 1.0 63,300 1

Hash Join (cost=17,163.56..29,963.53 rows=63,264 width=128) (actual time=315.349..606.836 rows=63,300 loops=1)

  • Hash Cond: (((hz.ks_cd)::text = (kh.ks_cd)::text) AND ((hz.data_kr_no)::text = (kh.data_kr_no)::text))
6. 81.352 432.925 ↓ 1.0 63,300 1

Hash Join (cost=13,252.33..22,255.18 rows=63,264 width=95) (actual time=248.044..432.925 rows=63,300 loops=1)

  • Hash Cond: (((hz.ks_cd)::text = (ss_kh.ks_cd)::text) AND ((hz.data_no)::text = (ss_kh.data_no)::text))
7. 72.089 153.644 ↓ 1.0 63,300 1

Hash Join (cost=3,102.23..8,436.96 rows=63,264 width=95) (actual time=49.828..153.644 rows=63,300 loops=1)

  • Hash Cond: (((wf_hz.ks_cd)::text = (hz.ks_cd)::text) AND ((wf_hz.data_no)::text = (hz.data_no)::text))
8. 32.138 32.138 ↓ 1.0 63,300 1

Seq Scan on nwa7_wf_hz_kth_ss wf_hz (cost=0.00..2,352.61 rows=63,264 width=61) (actual time=0.016..32.138 rows=63,300 loops=1)

  • Filter: ((ki_wf_st)::text = ANY ('{90,91}'::text[]))
  • Rows Removed by Filter: 2510
9. 30.648 49.417 ↓ 1.0 65,810 1

Hash (cost=1,600.09..1,600.09 rows=65,809 width=34) (actual time=49.417..49.417 rows=65,810 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2636kB
10. 18.769 18.769 ↓ 1.0 65,810 1

Seq Scan on nwbb_t_kth_ss_hz hz (cost=0.00..1,600.09 rows=65,809 width=34) (actual time=0.007..18.769 rows=65,810 loops=1)

11. 39.696 197.929 ↓ 1.0 65,810 1

Hash (cost=8,455.97..8,455.97 rows=65,809 width=64) (actual time=197.929..197.929 rows=65,810 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3674kB
12. 68.145 158.233 ↓ 1.0 65,810 1

Hash Join (cost=3,228.23..8,455.97 rows=65,809 width=64) (actual time=54.846..158.233 rows=65,810 loops=1)

  • Hash Cond: (((ks_sk.ks_cd)::text = (ss_kh.ks_cd)::text) AND ((ks_sk.data_no)::text = (ss_kh.data_no)::text))
13. 35.531 35.531 ↓ 1.0 65,810 1

Seq Scan on nwa8_t_ks_sk_kth_ss ks_sk (cost=0.00..2,789.09 rows=65,809 width=24) (actual time=0.005..35.531 rows=65,810 loops=1)

14. 31.821 54.557 ↓ 1.0 65,810 1

Hash (cost=1,726.09..1,726.09 rows=65,809 width=40) (actual time=54.557..54.557 rows=65,810 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2896kB
15. 22.736 22.736 ↓ 1.0 65,810 1

Seq Scan on nwbc_t_kth_ss_kh ss_kh (cost=0.00..1,726.09 rows=65,809 width=40) (actual time=0.006..22.736 rows=65,810 loops=1)

16. 44.099 67.041 ↓ 1.0 65,810 1

Hash (cost=2,088.09..2,088.09 rows=65,809 width=78) (actual time=67.041..67.041 rows=65,810 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2311kB
17. 22.942 22.942 ↓ 1.0 65,810 1

Seq Scan on nwba_t_kth_kh kh (cost=0.00..2,088.09 rows=65,809 width=78) (actual time=0.007..22.942 rows=65,810 loops=1)

18. 334.576 14,386.490 ↓ 6.7 425,663 1

Materialize (cost=167,500.66..173,340.53 rows=63,298 width=678) (actual time=13,316.802..14,386.490 rows=425,663 loops=1)

19. 403.288 14,051.914 ↓ 6.7 425,663 1

Merge Left Join (cost=167,500.66..173,182.28 rows=63,298 width=678) (actual time=13,316.795..14,051.914 rows=425,663 loops=1)

  • Merge Cond: ((umttr.system_matter_id)::text = (his.system_matter_id)::text)
20. 112.354 112.354 ↓ 1.0 63,302 1

Index Scan using imw_t_cpl_matter_pkey on imw_t_cpl_matter umttr (cost=0.41..5,500.04 rows=63,298 width=60) (actual time=0.023..112.354 rows=63,302 loops=1)

21. 4,957.005 13,536.272 ↓ 269.9 427,237 1

Sort (cost=167,500.25..167,504.21 rows=1,583 width=644) (actual time=13,316.763..13,536.272 rows=427,237 loops=1)

  • Sort Key: his.system_matter_id
  • Sort Method: external sort Disk: 41616kB
22. 185.716 8,579.267 ↓ 269.9 427,237 1

Subquery Scan on his (cost=85,694.72..167,416.12 rows=1,583 width=644) (actual time=6,521.501..8,579.267 rows=427,237 loops=1)

23. 156.246 8,393.551 ↓ 269.9 427,237 1

Append (cost=85,694.72..167,400.29 rows=1,583 width=97) (actual time=6,521.500..8,393.551 rows=427,237 loops=1)

24. 430.303 7,774.938 ↓ 60,809.0 425,663 1

Merge Right Join (cost=85,694.72..157,826.80 rows=7 width=136) (actual time=6,521.498..7,774.938 rows=425,663 loops=1)

  • Merge Cond: ((ct.system_matter_id)::text = (cpl_user.system_matter_id)::text)
25. 205.198 647.161 ↓ 1.4 63,302 1

GroupAggregate (cost=0.42..71,558.79 rows=45,887 width=27) (actual time=0.041..647.161 rows=63,302 loops=1)

  • Group Key: ct.system_matter_id
26. 441.963 441.963 ↓ 1.0 425,663 1

Index Scan using imw_t_cpl_matter_task_pkey on imw_t_cpl_matter_task ct (cost=0.42..68,977.20 rows=424,545 width=27) (actual time=0.021..441.963 rows=425,663 loops=1)

  • Filter: ((status)::text = ANY ('{apply,reapply,discontinue,pullback,sendbacktopullback,approve,sendback}'::text[]))
  • Rows Removed by Filter: 143068
27. 4,736.450 6,697.474 ↓ 60,809.0 425,663 1

Sort (cost=85,694.30..85,694.32 rows=7 width=109) (actual time=6,521.423..6,697.474 rows=425,663 loops=1)

  • Sort Key: cpl_user.system_matter_id
  • Sort Method: external sort Disk: 54288kB
28. 1,157.231 1,961.024 ↓ 60,809.0 425,663 1

Hash Join (cost=39,673.03..85,694.20 rows=7 width=109) (actual time=564.500..1,961.024 rows=425,663 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))
29. 239.671 239.671 ↓ 1.0 568,731 1

Seq Scan on imw_t_cpl_matter_user cpl_user (cost=0.00..23,730.81 rows=568,705 width=61) (actual time=0.010..239.671 rows=568,731 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
30. 292.888 564.122 ↓ 1.0 425,663 1

Hash (cost=27,499.86..27,499.86 rows=424,545 width=86) (actual time=564.122..564.122 rows=425,663 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 3423kB
31. 271.234 271.234 ↓ 1.0 425,663 1

Seq Scan on imw_t_cpl_matter_task cplt (cost=0.00..27,499.86 rows=424,545 width=86) (actual time=0.009..271.234 rows=425,663 loops=1)

  • Filter: ((status)::text = ANY ('{apply,reapply,discontinue,pullback,sendbacktopullback,approve,sendback}'::text[]))
  • Rows Removed by Filter: 143068
32. 0.965 462.367 ↑ 1.0 1,574 1

Subquery Scan on *SELECT* 2 (cost=9,055.98..9,573.42 rows=1,576 width=97) (actual time=413.698..462.367 rows=1,574 loops=1)

33. 2.593 461.402 ↑ 1.0 1,574 1

Hash Right Join (cost=9,055.98..9,557.66 rows=1,576 width=97) (actual time=413.696..461.402 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))
34. 2.725 457.634 ↓ 21.2 1,524 1

Merge Join (cost=8,973.58..9,451.07 rows=72 width=67) (actual time=412.473..457.634 rows=1,524 loops=1)

  • Merge Cond: ((exex_user_numbering.system_matter_id)::text = (exex_user_count.system_matter_id)::text)
35. 7.068 389.448 ↓ 21.2 1,524 1

Subquery Scan on exex_user_numbering (cost=4,892.49..5,361.33 rows=72 width=59) (actual time=347.436..389.448 rows=1,524 loops=1)

  • Filter: (exex_user_numbering.row_num = 1)
  • Rows Removed by Filter: 29833
36. 21.022 382.380 ↓ 2.2 31,357 1

WindowAgg (cost=4,892.49..5,181.01 rows=14,426 width=59) (actual time=347.418..382.380 rows=31,357 loops=1)

37. 314.018 361.358 ↓ 2.2 31,357 1

Sort (cost=4,892.49..4,928.55 rows=14,426 width=59) (actual time=347.405..361.358 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
38. 19.756 47.340 ↓ 2.2 31,357 1

Merge Join (cost=0.97..3,895.91 rows=14,426 width=59) (actual time=0.084..47.340 rows=31,357 loops=1)

  • Merge Cond: ((us.user_cd)::text = (imw_t_actv_executable_user.auth_user_code)::text)
39. 3.873 3.873 ↓ 1.3 3,563 1

Index Only Scan using imm_user_idx_ref_del on imm_user us (cost=0.29..1,057.14 rows=2,695 width=7) (actual time=0.059..3.873 rows=3,563 loops=1)

  • Index Cond: ((start_date <= now()) AND (end_date > now()) AND (delete_flag = '0'::text))
  • Heap Fetches: 1749
40. 23.711 23.711 ↑ 1.0 31,609 1

Index Scan using idx_imw_t_actv_exe_user on imw_t_actv_executable_user (cost=0.29..2,820.43 rows=31,867 width=59) (actual time=0.013..23.711 rows=31,609 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
41. 6.676 65.461 ↑ 1.0 1,524 1

Sort (cost=4,081.10..4,084.97 rows=1,549 width=27) (actual time=65.031..65.461 rows=1,524 loops=1)

  • Sort Key: exex_user_count.system_matter_id
  • Sort Method: quicksort Memory: 168kB
42. 0.543 58.785 ↑ 1.0 1,524 1

Subquery Scan on exex_user_count (cost=3,968.04..3,999.02 rows=1,549 width=27) (actual time=57.808..58.785 rows=1,524 loops=1)

43. 13.539 58.242 ↑ 1.0 1,524 1

HashAggregate (cost=3,968.04..3,983.53 rows=1,549 width=26) (actual time=57.807..58.242 rows=1,524 loops=1)

  • Group Key: imw_t_actv_executable_user_1.system_matter_id
44. 18.444 44.703 ↓ 2.2 31,357 1

Merge Join (cost=0.97..3,895.91 rows=14,426 width=26) (actual time=0.087..44.703 rows=31,357 loops=1)

  • Merge Cond: ((us_1.user_cd)::text = (imw_t_actv_executable_user_1.auth_user_code)::text)
45. 3.857 3.857 ↓ 1.3 3,563 1

Index Only Scan using imm_user_idx_ref_del on imm_user us_1 (cost=0.29..1,057.14 rows=2,695 width=7) (actual time=0.061..3.857 rows=3,563 loops=1)

  • Index Cond: ((start_date <= now()) AND (end_date > now()) AND (delete_flag = '0'::text))
  • Heap Fetches: 1749
46. 22.402 22.402 ↑ 1.0 31,609 1

Index Scan using idx_imw_t_actv_exe_user on imw_t_actv_executable_user imw_t_actv_executable_user_1 (cost=0.29..2,820.43 rows=31,867 width=26) (actual time=0.013..22.402 rows=31,609 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
47. 0.628 1.175 ↑ 1.0 1,574 1

Hash (cost=58.76..58.76 rows=1,576 width=65) (actual time=1.175..1.175 rows=1,574 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 166kB
48. 0.547 0.547 ↑ 1.0 1,574 1

Seq Scan on imw_t_actv_task task_1 (cost=0.00..58.76 rows=1,576 width=65) (actual time=0.005..0.547 rows=1,574 loops=1)

49. 4.074 1,065.005 ↓ 419.3 1,258 1

Sort (cost=15,291.18..15,291.19 rows=3 width=71) (actual time=1,064.655..1,065.005 rows=1,258 loops=1)

  • Sort Key: pos.system_matter_id
  • Sort Method: quicksort Memory: 226kB
50. 556.153 1,060.931 ↓ 419.3 1,258 1

Nested Loop (cost=82.40..15,291.16 rows=3 width=71) (actual time=13.438..1,060.931 rows=1,258 loops=1)

  • Join Filter: (((pos.ks_cd)::text = (imd.company_cd)::text) AND ((pos.approve_auth_dept_code)::text = (imd.department_cd)::text))
  • Rows Removed by Join Filter: 1586341
51. 125.807 211.431 ↓ 1.0 1,259 1

Hash Join (cost=82.40..15,015.42 rows=1,210 width=50) (actual time=12.955..211.431 rows=1,259 loops=1)

  • Hash Cond: (((pos.system_matter_id)::text = (task.system_matter_id)::text) AND ((pos.node_name)::text = (task.node_name)::text))
52. 84.435 84.435 ↓ 1.0 356,293 1

Seq Scan on nwur_t_akb_sn_tts_pos pos (cost=0.00..12,248.81 rows=356,281 width=71) (actual time=0.011..84.435 rows=356,293 loops=1)

53. 0.664 1.189 ↑ 1.0 1,574 1

Hash (cost=58.76..58.76 rows=1,576 width=37) (actual time=1.189..1.189 rows=1,574 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
54. 0.525 0.525 ↑ 1.0 1,574 1

Seq Scan on imw_t_actv_task task (cost=0.00..58.76 rows=1,576 width=37) (actual time=0.005..0.525 rows=1,574 loops=1)

55. 291.160 293.347 ↓ 315.2 1,261 1,259

Materialize (cost=0.00..191.05 rows=4 width=51) (actual time=0.000..0.233 rows=1,261 loops=1,259)

56. 2.187 2.187 ↓ 315.2 1,261 1

Seq Scan on imm_department imd (cost=0.00..191.03 rows=4 width=51) (actual time=0.015..2.187 rows=1,261 loops=1)

  • Filter: (((company_cd)::text = (department_set_cd)::text) AND ((locale_id)::text = 'ja'::text) AND ((delete_flag)::text = '0'::text) AND (start_date <= now()) AND (end_date > now()))
  • Rows Removed by Filter: 2122
Planning time : 10.375 ms
Execution time : 25,062.668 ms