explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FXRu : 02_対策2 SQL(UNION削除)

Settings
# exclusive inclusive rows x rows loops node
1. 7,190.891 20,364.589 ↓ 6.7 425,637 1

Sort (cost=287,446.78..287,604.94 rows=63,264 width=1,314) (actual time=18,928.364..20,364.589 rows=425,637 loops=1)

  • Sort Key: wf_hz.matter_number, imw_matter.end_date
  • Sort Method: external merge Disk: 127128kB
2.          

CTE imw_matter

3. 502.621 9,160.962 ↓ 6.7 425,663 1

Hash Right Join (cost=90,738.93..173,532.10 rows=63,298 width=772) (actual time=6,511.817..9,160.962 rows=425,663 loops=1)

  • Hash Cond: ((his.system_matter_id)::text = (umttr.system_matter_id)::text)
4. 185.081 8,596.170 ↓ 269.9 427,237 1

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

5. 160.891 8,411.089 ↓ 269.9 427,237 1

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

6. 457.160 7,787.908 ↓ 60,809.0 425,663 1

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

  • Merge Cond: ((ct.system_matter_id)::text = (cpl_user.system_matter_id)::text)
7. 216.265 687.125 ↓ 1.4 63,302 1

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

  • Group Key: ct.system_matter_id
8. 470.860 470.860 ↓ 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.027..470.860 rows=425,663 loops=1)

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

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

  • Sort Key: cpl_user.system_matter_id
  • Sort Method: external sort Disk: 54288kB
10. 1,010.037 1,874.442 ↓ 60,809.0 425,663 1

Hash Join (cost=39,673.03..85,694.20 rows=7 width=109) (actual time=600.639..1,874.442 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))
11. 264.242 264.242 ↓ 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.011..264.242 rows=568,731 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
12. 295.329 600.163 ↓ 1.0 425,663 1

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

  • Buckets: 32768 Batches: 16 Memory Usage: 3423kB
13. 304.834 304.834 ↓ 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.014..304.834 rows=425,663 loops=1)

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

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

15. 2.584 461.319 ↑ 1.0 1,574 1

Hash Right Join (cost=9,055.98..9,557.66 rows=1,576 width=97) (actual time=411.191..461.319 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))
16. 2.890 457.501 ↓ 21.2 1,524 1

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

  • Merge Cond: ((exex_user_numbering.system_matter_id)::text = (exex_user_count.system_matter_id)::text)
17. 7.346 388.765 ↓ 21.2 1,524 1

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

  • Filter: (exex_user_numbering.row_num = 1)
  • Rows Removed by Filter: 29833
18. 21.596 381.419 ↓ 2.2 31,357 1

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

19. 311.098 359.823 ↓ 2.2 31,357 1

Sort (cost=4,892.49..4,928.55 rows=14,426 width=59) (actual time=344.494..359.823 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
20. 19.786 48.725 ↓ 2.2 31,357 1

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

  • Merge Cond: ((us.user_cd)::text = (imw_t_actv_executable_user.auth_user_code)::text)
21. 4.335 4.335 ↓ 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.078..4.335 rows=3,563 loops=1)

  • Index Cond: ((start_date <= now()) AND (end_date > now()) AND (delete_flag = '0'::text))
  • Heap Fetches: 1749
22. 24.604 24.604 ↑ 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.014..24.604 rows=31,609 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
23. 6.752 65.846 ↑ 1.0 1,524 1

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

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

25. 13.535 58.538 ↑ 1.0 1,524 1

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

  • Group Key: imw_t_actv_executable_user_1.system_matter_id
26. 18.484 45.003 ↓ 2.2 31,357 1

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

  • Merge Cond: ((us_1.user_cd)::text = (imw_t_actv_executable_user_1.auth_user_code)::text)
27. 3.903 3.903 ↓ 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.066..3.903 rows=3,563 loops=1)

  • Index Cond: ((start_date <= now()) AND (end_date > now()) AND (delete_flag = '0'::text))
  • Heap Fetches: 1749
28. 22.616 22.616 ↑ 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.014..22.616 rows=31,609 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
29. 0.688 1.234 ↑ 1.0 1,574 1

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

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

31. 31.250 62.171 ↓ 1.0 63,302 1

Hash (cost=3,510.98..3,510.98 rows=63,298 width=68) (actual time=62.171..62.171 rows=63,302 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3717kB
32. 30.921 30.921 ↓ 1.0 63,302 1

Seq Scan on imw_t_cpl_matter umttr (cost=0.00..3,510.98 rows=63,298 width=68) (actual time=0.015..30.921 rows=63,302 loops=1)

33. 803.736 13,173.698 ↓ 6.7 425,637 1

Hash Left Join (cost=47,219.53..72,539.66 rows=63,264 width=1,314) (actual time=8,284.277..13,173.698 rows=425,637 loops=1)

  • Hash Cond: ((wf_hz.system_matter_id)::text = (pos.system_matter_id)::text)
34. 693.743 11,252.749 ↓ 6.7 425,637 1

Hash Right Join (cost=31,928.33..54,006.16 rows=63,264 width=1,262) (actual time=7,167.015..11,252.749 rows=425,637 loops=1)

  • Hash Cond: ((imw_matter.system_matter_id)::text = (wf_hz.system_matter_id)::text)
35. 9,904.031 9,904.031 ↓ 6.7 425,663 1

CTE Scan on imw_matter (cost=0.00..1,265.96 rows=63,298 width=1,192) (actual time=6,511.823..9,904.031 rows=425,663 loops=1)

36. 52.875 654.975 ↓ 1.0 63,300 1

Hash (cost=29,963.53..29,963.53 rows=63,264 width=128) (actual time=654.975..654.975 rows=63,300 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 2816kB
37. 97.289 602.100 ↓ 1.0 63,300 1

Hash Join (cost=17,163.56..29,963.53 rows=63,264 width=128) (actual time=320.584..602.100 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))
38. 79.201 435.938 ↓ 1.0 63,300 1

Hash Join (cost=13,252.33..22,255.18 rows=63,264 width=95) (actual time=251.439..435.938 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))
39. 70.595 156.753 ↓ 1.0 63,300 1

Hash Join (cost=3,102.23..8,436.96 rows=63,264 width=95) (actual time=51.170..156.753 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))
40. 35.324 35.324 ↓ 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.019..35.324 rows=63,300 loops=1)

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

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

  • Buckets: 65536 Batches: 2 Memory Usage: 2636kB
42. 20.231 20.231 ↓ 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.009..20.231 rows=65,810 loops=1)

43. 37.892 199.984 ↓ 1.0 65,810 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3674kB
44. 66.197 162.092 ↓ 1.0 65,810 1

Hash Join (cost=3,228.23..8,455.97 rows=65,809 width=64) (actual time=56.625..162.092 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))
45. 39.544 39.544 ↓ 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.007..39.544 rows=65,810 loops=1)

46. 31.392 56.351 ↓ 1.0 65,810 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 2896kB
47. 24.959 24.959 ↓ 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.009..24.959 rows=65,810 loops=1)

48. 42.963 68.873 ↓ 1.0 65,810 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2311kB
49. 25.910 25.910 ↓ 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.013..25.910 rows=65,810 loops=1)

50. 1.224 1,117.213 ↓ 419.3 1,258 1

Hash (cost=15,291.16..15,291.16 rows=3 width=71) (actual time=1,117.213..1,117.213 rows=1,258 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 139kB
51. 581.891 1,115.989 ↓ 419.3 1,258 1

Nested Loop (cost=82.40..15,291.16 rows=3 width=71) (actual time=14.373..1,115.989 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
52. 126.190 229.420 ↓ 1.0 1,259 1

Hash Join (cost=82.40..15,015.42 rows=1,210 width=50) (actual time=13.868..229.420 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))
53. 101.973 101.973 ↓ 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.017..101.973 rows=356,293 loops=1)

54. 0.656 1.257 ↑ 1.0 1,574 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
55. 0.601 0.601 ↑ 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.007..0.601 rows=1,574 loops=1)

56. 302.328 304.678 ↓ 315.2 1,261 1,259

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

57. 2.350 2.350 ↓ 315.2 1,261 1

Seq Scan on imm_department imd (cost=0.00..191.03 rows=4 width=51) (actual time=0.023..2.350 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.088 ms
Execution time : 20,804.863 ms