explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bAyF : 01_onlyselect

Settings
# exclusive inclusive rows x rows loops node
1. 1.296 4,950.813 ↑ 1.0 1 1

Aggregate (cost=22,509.07..22,509.08 rows=1 width=0) (actual time=4,950.812..4,950.813 rows=1 loops=1)

2. 46.649 4,949.517 ↓ 4.3 6,545 1

Sort (cost=22,486.08..22,489.91 rows=1,533 width=24) (actual time=4,948.109..4,949.517 rows=6,545 loops=1)

  • Sort Key: wf_hz.matter_number, cplt.end_date
  • Sort Method: quicksort Memory: 704kB
3. 2,063.240 4,902.868 ↓ 4.3 6,545 1

Nested Loop Left Join (cost=5,602.02..22,404.97 rows=1,533 width=24) (actual time=568.967..4,902.868 rows=6,545 loops=1)

  • Join Filter: ((wf_hz.system_matter_id)::text = (pos.system_matter_id)::text)
  • Rows Removed by Join Filter: 8228406
4. 11.454 797.588 ↓ 4.3 6,545 1

Nested Loop (cost=5,519.25..17,988.23 rows=1,533 width=43) (actual time=64.991..797.588 rows=6,545 loops=1)

5. 10.486 707.594 ↓ 4.3 6,545 1

Nested Loop (cost=5,518.83..17,171.95 rows=1,533 width=103) (actual time=64.951..707.594 rows=6,545 loops=1)

6. 14.577 618.568 ↓ 4.3 6,545 1

Nested Loop (cost=5,518.41..16,256.63 rows=1,533 width=84) (actual time=64.926..618.568 rows=6,545 loops=1)

7. 7.671 512.361 ↓ 4.3 6,545 1

Hash Right Join (cost=5,517.99..15,406.62 rows=1,533 width=96) (actual time=64.888..512.361 rows=6,545 loops=1)

  • Hash Cond: ((cpl_user.system_matter_id)::text = (umttr.system_matter_id)::text)
8. 3.480 452.025 ↓ 4.2 6,571 1

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

9. 6.615 31.410 ↓ 1,249.2 4,997 1

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

  • Hash Cond: ((cpl_user.system_matter_id)::text = (ct.system_matter_id)::text)
10. 8.172 18.653 ↓ 1,249.2 4,997 1

Hash Join (cost=460.11..846.11 rows=4 width=108) (actual time=6.048..18.653 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))
11. 4.493 4.493 ↑ 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.009..4.493 rows=7,848 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
12. 2.578 5.988 ↑ 1.0 4,997 1

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

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

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

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 115kB
15. 2.650 5.618 ↓ 1.6 1,574 1

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

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

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

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

18. 4.499 415.966 ↑ 1.0 1,574 1

Hash Right Join (cost=8,951.40..9,449.26 rows=1,574 width=97) (actual time=362.322..415.966 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))
19. 3.939 410.214 ↓ 21.2 1,524 1

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

  • Merge Cond: ((exex_user_numbering.system_matter_id)::text = (exex_user_count.system_matter_id)::text)
20. 7.409 352.008 ↓ 21.2 1,524 1

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

  • Filter: (exex_user_numbering.row_num = 1)
  • Rows Removed by Filter: 29833
21. 22.568 344.599 ↓ 2.2 31,357 1

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

22. 285.142 322.031 ↓ 2.2 31,357 1

Sort (cost=4,835.97..4,871.74 rows=14,309 width=59) (actual time=307.560..322.031 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
23. 17.564 36.889 ↓ 2.2 31,357 1

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

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

  • Filter: ((locale_id)::text = 'ja'::text)
25. 1.435 8.005 ↓ 1.6 4,316 1

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

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

  • Filter: (((delete_flag)::text = '0'::text) AND (start_date <= now()) AND (end_date > now()))
  • Rows Removed by Filter: 9279
27. 6.995 54.267 ↑ 1.0 1,524 1

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

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

29. 11.523 46.721 ↑ 1.0 1,524 1

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

  • Group Key: imw_t_actv_executable_user_1.system_matter_id
30. 16.474 35.198 ↓ 2.2 31,357 1

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

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

  • Filter: ((locale_id)::text = 'ja'::text)
32. 1.436 7.947 ↓ 1.6 4,316 1

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

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

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

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

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

36. 0.897 52.665 ↓ 1.0 1,567 1

Hash (cost=4,596.04..4,596.04 rows=1,529 width=107) (actual time=52.665..52.665 rows=1,567 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 229kB
37. 0.968 51.768 ↓ 1.0 1,567 1

Hash Left Join (cost=2,480.97..4,596.04 rows=1,529 width=107) (actual time=16.250..51.768 rows=1,567 loops=1)

  • Hash Cond: ((wf_hz.system_matter_id)::text = (umttr.system_matter_id)::text)
38. 20.258 49.840 ↓ 1.0 1,567 1

Hash Join (cost=2,375.56..4,484.52 rows=1,529 width=88) (actual time=15.275..49.840 rows=1,567 loops=1)

  • Hash Cond: (((hz.ks_cd)::text = (wf_hz.ks_cd)::text) AND ((hz.data_no)::text = (wf_hz.data_no)::text))
39. 14.559 14.559 ↑ 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.559 rows=65,810 loops=1)

40. 0.727 15.023 ↓ 1.0 1,567 1

Hash (cost=2,352.62..2,352.62 rows=1,529 width=54) (actual time=15.023..15.023 rows=1,567 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 148kB
41. 14.296 14.296 ↓ 1.0 1,567 1

Seq Scan on nwa7_wf_hz_kth_ss wf_hz (cost=0.00..2,352.62 rows=1,529 width=54) (actual time=0.019..14.296 rows=1,567 loops=1)

  • Filter: ((ki_wf_st)::text = ANY ('{05,10}'::text[]))
  • Rows Removed by Filter: 64243
42. 0.451 0.960 ↑ 1.0 1,574 1

Hash (cost=85.74..85.74 rows=1,574 width=19) (actual time=0.960..0.960 rows=1,574 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 95kB
43. 0.509 0.509 ↑ 1.0 1,574 1

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

44. 91.630 91.630 ↑ 1.0 1 6,545

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.013..0.014 rows=1 loops=6,545)

  • Index Cond: ((ks_cd = (hz.ks_cd)::text) AND (data_kr_no = (hz.data_kr_no)::text))
  • Heap Fetches: 6545
45. 78.540 78.540 ↑ 1.0 1 6,545

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

  • Index Cond: ((ks_cd = (hz.ks_cd)::text) AND (data_no = (hz.data_no)::text))
  • Heap Fetches: 6545
46. 78.540 78.540 ↑ 1.0 1 6,545

Index Only Scan using pk_nwbc_t_kth_ss_kh on nwbc_t_kth_ss_kh ss_kh (cost=0.42..0.52 rows=1 width=19) (actual time=0.012..0.012 rows=1 loops=6,545)

  • Index Cond: ((ks_cd = (hz.ks_cd)::text) AND (data_no = (hz.data_no)::text))
  • Heap Fetches: 6545
47. 1,539.180 2,042.040 ↓ 419.3 1,258 6,545

Materialize (cost=82.77..4,347.76 rows=3 width=19) (actual time=0.001..0.312 rows=1,258 loops=6,545)

48. 102.637 502.860 ↓ 419.3 1,258 1

Hash Join (cost=82.77..4,347.74 rows=3 width=19) (actual time=2.097..502.860 rows=1,258 loops=1)

  • Hash Cond: (((pos.system_matter_id)::text = (task.system_matter_id)::text) AND ((pos.node_name)::text = (task.node_name)::text))
49. 174.346 399.000 ↓ 372.5 347,129 1

Nested Loop (cost=0.42..4,258.37 rows=932 width=40) (actual time=0.072..399.000 rows=347,129 loops=1)

50. 2.718 2.718 ↓ 315.2 1,261 1

Seq Scan on imm_department imd (cost=0.00..191.03 rows=4 width=16) (actual time=0.018..2.718 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
51. 221.936 221.936 ↑ 2.3 275 1,261

Index Scan using idx_nwur_t_akb_sn_tts_pos_02 on nwur_t_akb_sn_tts_pos pos (cost=0.42..1,010.64 rows=620 width=54) (actual time=0.010..0.176 rows=275 loops=1,261)

  • Index Cond: (((ks_cd)::text = (imd.company_cd)::text) AND ((approve_auth_dept_code)::text = (imd.department_cd)::text))
52. 0.652 1.223 ↑ 1.0 1,574 1

Hash (cost=58.74..58.74 rows=1,574 width=37) (actual time=1.223..1.223 rows=1,574 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
53. 0.571 0.571 ↑ 1.0 1,574 1

Seq Scan on imw_t_actv_task task (cost=0.00..58.74 rows=1,574 width=37) (actual time=0.004..0.571 rows=1,574 loops=1)

Planning time : 12.524 ms
Execution time : 4,952.457 ms