explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VhsZ : WITH句のみ

Settings
# exclusive inclusive rows x rows loops node
1. 161.364 9,443.931 ↓ 6.7 432,234 1

Append (cost=1,011.33..185,190.98 rows=64,878 width=804) (actual time=14.206..9,443.931 rows=432,234 loops=1)

2. 4.578 492.118 ↓ 4.2 6,571 1

Hash Right Join (cost=1,011.33..11,010.11 rows=1,580 width=771) (actual time=14.204..492.118 rows=6,571 loops=1)

  • Hash Cond: ((cpl_user.system_matter_id)::text = (umttr.system_matter_id)::text)
3. 2.366 486.147 ↓ 4.2 6,571 1

Append (cost=905.87..10,867.12 rows=1,580 width=97) (actual time=12.795..486.147 rows=6,571 loops=1)

4. 3.683 24.013 ↓ 1,249.2 4,997 1

Hash Left Join (cost=905.87..1,293.66 rows=4 width=135) (actual time=12.794..24.013 rows=4,997 loops=1)

  • Hash Cond: ((cpl_user.system_matter_id)::text = (ct.system_matter_id)::text)
5. 4.403 14.072 ↓ 1,249.2 4,997 1

Hash Join (cost=461.71..848.93 rows=4 width=108) (actual time=6.519..14.072 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))
6. 3.209 3.209 ↑ 1.0 7,848 1

Seq Scan on imw_t_cpl_user cpl_user (cost=0.00..327.86 rows=7,909 width=61) (actual time=0.012..3.209 rows=7,848 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
7. 2.651 6.460 ↑ 1.0 4,997 1

Hash (cost=386.26..386.26 rows=5,030 width=85) (actual time=6.460..6.460 rows=4,997 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 649kB
8. 3.809 3.809 ↑ 1.0 4,997 1

Seq Scan on imw_t_cpl_task cplt (cost=0.00..386.26 rows=5,030 width=85) (actual time=0.014..3.809 rows=4,997 loops=1)

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

Hash (cost=431.57..431.57 rows=1,008 width=27) (actual time=6.258..6.258 rows=1,574 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 115kB
10. 2.690 5.716 ↓ 1.6 1,574 1

HashAggregate (cost=411.41..421.49 rows=1,008 width=27) (actual time=5.308..5.716 rows=1,574 loops=1)

  • Group Key: ct.system_matter_id
11. 3.026 3.026 ↑ 1.0 4,997 1

Seq Scan on imw_t_cpl_task ct (cost=0.00..386.26 rows=5,030 width=27) (actual time=0.007..3.026 rows=4,997 loops=1)

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

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

13. 2.407 458.931 ↑ 1.0 1,574 1

Hash Right Join (cost=9,055.98..9,557.66 rows=1,576 width=97) (actual time=410.867..458.931 rows=1,574 loops=1)

  • Hash Cond: (((exex_user_numbering.system_matter_id)::text = (task.system_matter_id)::text) AND ((exex_user_numbering.node_id)::text = (task.node_id)::text))
14. 2.612 455.255 ↓ 21.2 1,524 1

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

  • Merge Cond: ((exex_user_numbering.system_matter_id)::text = (exex_user_count.system_matter_id)::text)
15. 7.012 386.850 ↓ 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.170..386.850 rows=1,524 loops=1)

  • Filter: (exex_user_numbering.row_num = 1)
  • Rows Removed by Filter: 29833
16. 20.832 379.838 ↓ 2.2 31,357 1

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

17. 311.995 359.006 ↓ 2.2 31,357 1

Sort (cost=4,892.49..4,928.55 rows=14,426 width=59) (actual time=344.139..359.006 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
18. 19.555 47.011 ↓ 2.2 31,357 1

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

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

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

  • Filter: ((locale_id)::text = 'ja'::text)
21. 6.677 65.793 ↑ 1.0 1,524 1

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

  • Sort Key: exex_user_count.system_matter_id
  • Sort Method: quicksort Memory: 168kB
22. 0.575 59.116 ↑ 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.106..59.116 rows=1,524 loops=1)

23. 13.752 58.541 ↑ 1.0 1,524 1

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

  • Group Key: imw_t_actv_executable_user_1.system_matter_id
24. 18.379 44.789 ↓ 2.2 31,357 1

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

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

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

  • Filter: ((locale_id)::text = 'ja'::text)
27. 0.713 1.269 ↑ 1.0 1,574 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 166kB
28. 0.556 0.556 ↑ 1.0 1,574 1

Seq Scan on imw_t_actv_task task (cost=0.00..58.76 rows=1,576 width=65) (actual time=0.004..0.556 rows=1,574 loops=1)

29. 0.629 1.393 ↑ 1.0 1,574 1

Hash (cost=85.76..85.76 rows=1,576 width=67) (actual time=1.393..1.393 rows=1,574 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 176kB
30. 0.764 0.764 ↑ 1.0 1,574 1

Seq Scan on imw_t_actv_matter umttr (cost=0.00..85.76 rows=1,576 width=67) (actual time=0.011..0.764 rows=1,574 loops=1)

31. 564.935 8,790.449 ↓ 6.7 425,663 1

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

  • Hash Cond: ((his.system_matter_id)::text = (umttr_1.system_matter_id)::text)
32. 174.363 8,160.259 ↓ 269.9 427,237 1

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

33. 151.686 7,985.896 ↓ 269.9 427,237 1

Append (cost=85,694.72..167,400.29 rows=1,583 width=97) (actual time=6,161.556..7,985.896 rows=427,237 loops=1)

34. 397.988 7,326.947 ↓ 60,809.0 425,663 1

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

  • Merge Cond: ((ct_1.system_matter_id)::text = (cpl_user_1.system_matter_id)::text)
35. 193.531 602.814 ↓ 1.4 63,302 1

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

  • Group Key: ct_1.system_matter_id
36. 409.283 409.283 ↓ 1.0 425,663 1

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

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

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

  • Sort Key: cpl_user_1.system_matter_id
  • Sort Method: external sort Disk: 54288kB
38. 938.785 1,726.209 ↓ 60,809.0 425,663 1

Hash Join (cost=39,673.03..85,694.20 rows=7 width=109) (actual time=564.023..1,726.209 rows=425,663 loops=1)

  • Hash Cond: (((cpl_user_1.system_matter_id)::text = (cplt_1.system_matter_id)::text) AND ((cpl_user_1.task_id)::text = (cplt_1.task_id)::text))
39. 223.866 223.866 ↓ 1.0 568,731 1

Seq Scan on imw_t_cpl_matter_user cpl_user_1 (cost=0.00..23,730.81 rows=568,705 width=61) (actual time=0.011..223.866 rows=568,731 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
40. 292.014 563.558 ↓ 1.0 425,663 1

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

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

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

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

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

43. 3.356 506.139 ↑ 1.0 1,574 1

Hash Right Join (cost=9,055.98..9,557.66 rows=1,576 width=97) (actual time=445.950..506.139 rows=1,574 loops=1)

  • Hash Cond: (((exex_user_numbering_1.system_matter_id)::text = (task_1.system_matter_id)::text) AND ((exex_user_numbering_1.node_id)::text = (task_1.node_id)::text))
44. 3.754 501.507 ↓ 21.2 1,524 1

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

  • Merge Cond: ((exex_user_numbering_1.system_matter_id)::text = (exex_user_count_1.system_matter_id)::text)
45. 8.588 415.030 ↓ 21.2 1,524 1

Subquery Scan on exex_user_numbering_1 (cost=4,892.49..5,361.33 rows=72 width=59) (actual time=362.413..415.030 rows=1,524 loops=1)

  • Filter: (exex_user_numbering_1.row_num = 1)
  • Rows Removed by Filter: 29833
46. 25.127 406.442 ↓ 2.2 31,357 1

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

47. 332.525 381.315 ↓ 2.2 31,357 1

Sort (cost=4,892.49..4,928.55 rows=14,426 width=59) (actual time=362.381..381.315 rows=31,357 loops=1)

  • Sort Key: imw_t_actv_executable_user_2.system_matter_id, imw_t_actv_executable_user_2.auth_user_code
  • Sort Method: external merge Disk: 2136kB
48. 20.103 48.790 ↓ 2.2 31,357 1

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

  • Merge Cond: ((us_2.user_cd)::text = (imw_t_actv_executable_user_2.auth_user_code)::text)
49. 4.137 4.137 ↓ 1.3 3,563 1

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

  • Index Cond: ((start_date <= now()) AND (end_date > now()) AND (delete_flag = '0'::text))
  • Heap Fetches: 1749
50. 24.550 24.550 ↑ 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_2 (cost=0.29..2,820.43 rows=31,867 width=59) (actual time=0.016..24.550 rows=31,609 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
51. 11.643 82.723 ↑ 1.0 1,524 1

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

  • Sort Key: exex_user_count_1.system_matter_id
  • Sort Method: quicksort Memory: 168kB
52. 0.876 71.080 ↑ 1.0 1,524 1

Subquery Scan on exex_user_count_1 (cost=3,968.04..3,999.02 rows=1,549 width=27) (actual time=69.522..71.080 rows=1,524 loops=1)

53. 16.681 70.204 ↑ 1.0 1,524 1

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

  • Group Key: imw_t_actv_executable_user_3.system_matter_id
54. 21.452 53.523 ↓ 2.2 31,357 1

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

  • Merge Cond: ((us_3.user_cd)::text = (imw_t_actv_executable_user_3.auth_user_code)::text)
55. 4.112 4.112 ↓ 1.3 3,563 1

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

  • Index Cond: ((start_date <= now()) AND (end_date > now()) AND (delete_flag = '0'::text))
  • Heap Fetches: 1749
56. 27.959 27.959 ↑ 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_3 (cost=0.29..2,820.43 rows=31,867 width=26) (actual time=0.014..27.959 rows=31,609 loops=1)

  • Filter: ((locale_id)::text = 'ja'::text)
57. 0.708 1.276 ↑ 1.0 1,574 1

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

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

59. 33.844 65.255 ↓ 1.0 63,302 1

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

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

Seq Scan on imw_t_cpl_matter umttr_1 (cost=0.00..3,510.98 rows=63,298 width=68) (actual time=0.008..31.411 rows=63,302 loops=1)

Planning time : 6.838 ms
Execution time : 9,739.505 ms