explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1icw

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 9,707.133 ↓ 1.4 7 1

Limit (cost=1,116.20..1,663.60 rows=5 width=660) (actual time=9,706.861..9,707.133 rows=7 loops=1)

  • Buffers: shared hit=4284483
2. 0.080 9,707.132 ↓ 1.4 7 1

WindowAgg (cost=1,116.20..1,663.60 rows=5 width=660) (actual time=9,706.861..9,707.132 rows=7 loops=1)

  • Buffers: shared hit=4284483
3. 0.019 9,706.688 ↓ 1.4 7 1

Sort (cost=1,116.20..1,116.21 rows=5 width=502) (actual time=9,706.687..9,706.688 rows=7 loops=1)

  • Sort Key: a.edassnbr
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=4284390
4. 0.006 9,706.669 ↓ 1.4 7 1

Nested Loop (cost=996.24..1,116.14 rows=5 width=502) (actual time=9,706.504..9,706.669 rows=7 loops=1)

  • Buffers: shared hit=4284390
5. 0.000 0.103 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.10..47.34 rows=1 width=245) (actual time=0.091..0.103 rows=1 loops=1)

  • Join Filter: (((a.assnbri)::text = (abom.assnbri)::text) AND ((a.reg_name)::text = (abom.reg_name)::text))
  • Buffers: shared hit=22
6. 0.005 0.098 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.10..46.29 rows=1 width=230) (actual time=0.086..0.098 rows=1 loops=1)

  • Join Filter: (((a.assnbri)::text = (rpt.assnbri)::text) AND ((a.reg_name)::text = (rpt.reg_name)::text))
  • Buffers: shared hit=21
7. 0.006 0.081 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.69..37.84 rows=1 width=211) (actual time=0.072..0.081 rows=1 loops=1)

  • Join Filter: (((a.reg_name)::text = (au.reg_name)::text) AND ((a.assnbri)::text = (au.assnbri)::text))
  • Buffers: shared hit=17
8. 0.006 0.063 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.27..33.39 rows=1 width=204) (actual time=0.057..0.063 rows=1 loops=1)

  • Join Filter: ((a.asset_type)::text = (f1a_asset_type_sc0.code_id)::text)
  • Buffers: shared hit=13
9. 0.003 0.051 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.98..25.07 rows=1 width=187) (actual time=0.047..0.051 rows=1 loops=1)

  • Buffers: shared hit=10
10. 0.005 0.038 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.70..16.75 rows=1 width=170) (actual time=0.035..0.038 rows=1 loops=1)

  • Buffers: shared hit=7
11. 0.021 0.021 ↑ 1.0 1 1

Index Scan using f1fara_idx1 on f1asr_reg_asset a (cost=0.41..8.44 rows=1 width=153) (actual time=0.019..0.021 rows=1 loops=1)

  • Index Cond: (((reg_name)::text = 'ASSET'::text) AND ((assnbri)::text = '0000010'::text))
  • Filter: ((asset_type)::text = 'S'::text)
  • Buffers: shared hit=4
12. 0.012 0.012 ↑ 1.0 1 1

Index Scan using f1fstc_idx1 on f1stc_code f1a_oper_asset_status_sc2 (cost=0.29..8.30 rows=1 width=21) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (((code_type)::text = 'F1A_OPER_ASSET_STATUS'::text) AND ((a.operating_asset_status)::text = (code_id)::text))
  • Buffers: shared hit=3
13. 0.010 0.010 ↑ 1.0 1 1

Index Scan using f1fstc_idx1 on f1stc_code f1a_asset_status_sc1 (cost=0.29..8.30 rows=1 width=21) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (((code_type)::text = 'F1A_ASSET_STATUS'::text) AND ((a.asset_status)::text = (code_id)::text))
  • Buffers: shared hit=3
14. 0.006 0.006 ↑ 1.0 1 1

Index Scan using f1fstc_idx1 on f1stc_code f1a_asset_type_sc0 (cost=0.29..8.30 rows=1 width=21) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (((code_type)::text = 'F1A_ASSET_TYPE'::text) AND ((code_id)::text = 'S'::text))
  • Buffers: shared hit=3
15. 0.012 0.012 ↑ 1.0 1 1

Index Only Scan using f1farauf_idx1 on f1asr_reg_asset_uf au (cost=0.41..4.43 rows=1 width=15) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: ((reg_name = 'ASSET'::text) AND (assnbri = '0000010'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=4
16. 0.012 0.012 ↑ 1.0 1 1

Index Scan using amrpt_a_idx1 on amrpt_asset rpt (cost=0.41..8.43 rows=1 width=35) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (((reg_name)::text = 'ASSET'::text) AND ((assnbri)::text = '0000010'::text))
  • Buffers: shared hit=4
17. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on f1asr_reg_asset_bom abom (cost=0.00..1.03 rows=1 width=29) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (((assnbri)::text = '0000010'::text) AND (seqnbr = 1) AND ((reg_name)::text = 'ASSET'::text))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
18. 0.018 9,706.560 ↓ 1.4 7 1

Merge Left Join (cost=994.14..1,068.76 rows=5 width=257) (actual time=9,706.410..9,706.560 rows=7 loops=1)

  • Merge Cond: ((msal.mnt_shd_code)::text = (msal_2.mnt_shd_code)::text)
  • Join Filter: (((msal_2.reg_name)::text = (msal.reg_name)::text) AND ((msal_2.assnbri)::text = (msal.assnbri)::text))
  • Buffers: shared hit=4284368
19. 0.017 6,397.378 ↓ 1.4 7 1

Nested Loop Left Join (cost=283.08..349.60 rows=5 width=233) (actual time=6,397.243..6,397.378 rows=7 loops=1)

  • Join Filter: (((next.reg_name)::text = (msal.reg_name)::text) AND ((next.assnbri)::text = (msal.assnbri)::text) AND ((next.mnt_shd_code)::text = (msal.mnt_shd_code)::text))
  • Rows Removed by Join Filter: 6
  • Buffers: shared hit=2872279
20. 0.011 0.173 ↓ 1.4 7 1

Nested Loop Left Join (cost=1.13..59.50 rows=5 width=217) (actual time=0.063..0.173 rows=7 loops=1)

  • Join Filter: ((msd.mnt_shd_basis_optn)::text = (aso_mnt_shd_basis_sc7.code_id)::text)
  • Rows Removed by Join Filter: 7
  • Buffers: shared hit=22
21. 0.012 0.148 ↓ 1.4 7 1

Nested Loop Left Join (cost=0.85..44.84 rows=5 width=200) (actual time=0.050..0.148 rows=7 loops=1)

  • Join Filter: ((msd.mnt_shd_type)::text = (aso_mnt_shd_type_sc6.code_id)::text)
  • Rows Removed by Join Filter: 21
  • Buffers: shared hit=19
22. 0.010 0.115 ↓ 1.4 7 1

Nested Loop Left Join (cost=0.56..30.18 rows=5 width=183) (actual time=0.040..0.115 rows=7 loops=1)

  • Join Filter: ((msal.mnt_shd_asset_link_status)::text = (aso_mnt_shd_asset_link44f21c76.code_id)::text)
  • Buffers: shared hit=14
23. 0.034 0.091 ↓ 1.4 7 1

Nested Loop (cost=0.28..21.80 rows=5 width=166) (actual time=0.024..0.091 rows=7 loops=1)

  • Join Filter: ((msal.mnt_shd_code)::text = (msd.mnt_shd_code)::text)
  • Rows Removed by Join Filter: 119
  • Buffers: shared hit=11
24. 0.022 0.022 ↓ 1.2 7 1

Index Scan using f1asomsal_idx1 on f1aso_mnt_shd_asset_link msal (cost=0.28..17.40 rows=6 width=131) (actual time=0.010..0.022 rows=7 loops=1)

  • Index Cond: (((reg_name)::text = 'ASSET'::text) AND ((assnbri)::text = '0000010'::text))
  • Filter: ((mnt_shd_asset_link_status)::text = 'A'::text)
  • Buffers: shared hit=9
25. 0.020 0.035 ↑ 1.2 18 7

Materialize (cost=0.00..2.47 rows=22 width=35) (actual time=0.001..0.005 rows=18 loops=7)

  • Buffers: shared hit=2
26. 0.015 0.015 ↑ 1.2 18 1

Seq Scan on f1aso_mnt_shd_ctl msd (cost=0.00..2.36 rows=22 width=35) (actual time=0.003..0.015 rows=18 loops=1)

  • Filter: ((reg_name)::text = 'ASSET'::text)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=2
27. 0.000 0.014 ↑ 1.0 1 7

Materialize (cost=0.29..8.31 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=7)

  • Buffers: shared hit=3
28. 0.014 0.014 ↑ 1.0 1 1

Index Scan using f1fstc_idx1 on f1stc_code aso_mnt_shd_asset_link44f21c76 (cost=0.29..8.30 rows=1 width=21) (actual time=0.012..0.014 rows=1 loops=1)

  • Index Cond: (((code_type)::text = 'ASO_MNT_SHD_ASSET_LINK_STATUS'::text) AND ((code_id)::text = 'A'::text))
  • Buffers: shared hit=3
29. 0.009 0.021 ↑ 1.0 4 7

Materialize (cost=0.29..14.37 rows=4 width=21) (actual time=0.001..0.003 rows=4 loops=7)

  • Buffers: shared hit=5
30. 0.012 0.012 ↑ 1.0 4 1

Index Scan using f1fstc_idx1 on f1stc_code aso_mnt_shd_type_sc6 (cost=0.29..14.35 rows=4 width=21) (actual time=0.007..0.012 rows=4 loops=1)

  • Index Cond: ((code_type)::text = 'ASO_MNT_SHD_TYPE'::text)
  • Buffers: shared hit=5
31. 0.006 0.014 ↑ 2.0 2 7

Materialize (cost=0.29..14.37 rows=4 width=21) (actual time=0.001..0.002 rows=2 loops=7)

  • Buffers: shared hit=3
32. 0.008 0.008 ↑ 2.0 2 1

Index Scan using f1fstc_idx1 on f1stc_code aso_mnt_shd_basis_sc7 (cost=0.29..14.35 rows=4 width=21) (actual time=0.007..0.008 rows=2 loops=1)

  • Index Cond: ((code_type)::text = 'ASO_MNT_SHD_BASIS'::text)
  • Buffers: shared hit=3
33. 0.001 6,397.188 ↑ 1.0 1 7

Materialize (cost=281.95..290.01 rows=1 width=37) (actual time=913.882..913.884 rows=1 loops=7)

  • Buffers: shared hit=2872257
34. 0.008 6,397.187 ↑ 1.0 1 1

Nested Loop Left Join (cost=281.95..290.00 rows=1 width=37) (actual time=6,397.172..6,397.187 rows=1 loops=1)

  • Buffers: shared hit=2872257
35. 0.005 6,397.170 ↑ 1.0 1 1

Subquery Scan on next (cost=281.67..281.69 rows=1 width=38) (actual time=6,397.157..6,397.170 rows=1 loops=1)

  • Filter: (((next.reg_name)::text = 'ASSET'::text) AND ((next.assnbri)::text = '0000010'::text))
  • Buffers: shared hit=2872254
36. 0.013 6,397.165 ↑ 1.0 1 1

Limit (cost=281.67..281.68 rows=1 width=46) (actual time=6,397.153..6,397.165 rows=1 loops=1)

  • Buffers: shared hit=2872254
37. 0.032 6,397.152 ↑ 1.0 1 1

Sort (cost=281.67..281.68 rows=1 width=46) (actual time=6,397.152..6,397.152 rows=1 loops=1)

  • Sort Key: msal_1.reg_name, msal_1.assnbri, msal_1.mnt_shd_code, tc.task_rid
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=2872254
38. 2.726 6,397.120 ↓ 11.0 11 1

Nested Loop (cost=0.97..281.66 rows=1 width=46) (actual time=8.206..6,397.120 rows=11 loops=1)

  • Join Filter: ((SubPlan 8) = tc.est_start_datei)
  • Rows Removed by Join Filter: 523
  • Buffers: shared hit=2872254
39. 119.387 246.986 ↓ 534.0 534 1

Nested Loop (cost=0.69..230.07 rows=1 width=39) (actual time=0.074..246.986 rows=534 loops=1)

  • Join Filter: (((msc.reg_name)::text = (ta.reg_name)::text) AND (mh.unique_id = ta.mnt_hist_unique_id))
  • Rows Removed by Join Filter: 346168
  • Buffers: shared hit=26409
40. 2.067 16.440 ↓ 67.1 537 1

Nested Loop (cost=0.42..226.28 rows=8 width=51) (actual time=0.043..16.440 rows=537 loops=1)

  • Join Filter: (((msc.reg_name)::text = (msal_1.reg_name)::text) AND ((msc.mnt_shd_code)::text = (msal_1.mnt_shd_code)::text))
  • Rows Removed by Join Filter: 3179
  • Buffers: shared hit=4810
41. 1.752 10.029 ↓ 9.9 543 1

Nested Loop (cost=0.14..196.93 rows=55 width=52) (actual time=0.026..10.029 rows=543 loops=1)

  • Buffers: shared hit=3710
42. 0.297 0.297 ↓ 1.0 532 1

Seq Scan on f1asr_reg_asset_mh mh (cost=0.00..35.16 rows=516 width=35) (actual time=0.001..0.297 rows=532 loops=1)

  • Buffers: shared hit=30
43. 7.980 7.980 ↑ 1.0 1 532

Index Scan using f1asomsc_idx1 on f1aso_mnt_shd_ctl msc (cost=0.14..0.30 rows=1 width=17) (actual time=0.011..0.015 rows=1 loops=532)

  • Index Cond: ((reg_name)::text = (mh.reg_name)::text)
  • Filter: ((CASE WHEN ((mnt_shd_type)::text = 'N'::text) THEN mh.aso_nest_mnt_shd_code ELSE mh.aso_mnt_shd_code END)::text = (mnt_shd_code)::text)
  • Rows Removed by Filter: 17
  • Buffers: shared hit=3680
44. 4.344 4.344 ↓ 3.5 7 543

Index Only Scan using f1asomsal_idx1 on f1aso_mnt_shd_asset_link msal_1 (cost=0.28..0.50 rows=2 width=21) (actual time=0.006..0.008 rows=7 loops=543)

  • Index Cond: ((reg_name = (mh.reg_name)::text) AND (assnbri = (mh.assnbri)::text))
  • Heap Fetches: 0
  • Buffers: shared hit=1100
45. 111.159 111.159 ↓ 646.0 646 537

Index Scan using f1wtcass_idx2 on f1wrk_task_ctl_ass ta (cost=0.28..0.46 rows=1 width=38) (actual time=0.023..0.207 rows=646 loops=537)

  • Index Cond: (((reg_name)::text = (msal_1.reg_name)::text) AND ((assnbri)::text = (msal_1.assnbri)::text))
  • Buffers: shared hit=21599
46. 3.738 3.738 ↑ 1.0 1 534

Index Scan using f1wtc_idx1 on f1wrk_task_ctl tc (cost=0.28..1.70 rows=1 width=33) (actual time=0.007..0.007 rows=1 loops=534)

  • Index Cond: (((work_sys_name)::text = (ta.work_sys_name)::text) AND ((task_nbri)::text = (ta.task_nbri)::text))
  • Buffers: shared hit=1613
47.          

SubPlan (for Nested Loop)

48. 0.000 6,143.670 ↑ 1.0 1 534

Aggregate (cost=49.86..49.87 rows=1 width=8) (actual time=11.505..11.505 rows=1 loops=534)

  • Buffers: shared hit=2844232
49.          

Initplan (for Aggregate)

50. 2.670 3,048.072 ↑ 1.0 1 534

Aggregate (cost=24.92..24.94 rows=1 width=8) (actual time=5.708..5.708 rows=1 loops=534)

  • Buffers: shared hit=1422116
51. 105.240 3,045.402 ↓ 0.0 0 534

Nested Loop (cost=0.83..24.92 rows=1 width=8) (actual time=5.672..5.703 rows=0 loops=534)

  • Buffers: shared hit=1422116
52. 99.842 1,595.058 ↓ 315.0 315 534

Nested Loop (cost=0.55..16.61 rows=1 width=16) (actual time=0.192..2.987 rows=315 loops=534)

  • Buffers: shared hit=914380
53. 117.480 117.480 ↓ 645.0 645 534

Index Scan using f1wtcass_idx2 on f1wrk_task_ctl_ass ta_2 (cost=0.28..8.30 rows=1 width=38) (actual time=0.022..0.220 rows=645 loops=534)

  • Index Cond: (((reg_name)::text = (msal_1.reg_name)::text) AND ((assnbri)::text = (msal_1.assnbri)::text))
  • Buffers: shared hit=21458
54. 1,377.736 1,377.736 ↓ 0.0 0 344,434

Index Scan using f1faramh_idx1 on f1asr_reg_asset_mh mh3 (cost=0.28..8.30 rows=1 width=22) (actual time=0.004..0.004 rows=0 loops=344,434)

  • Index Cond: (((reg_name)::text = (msal_1.reg_name)::text) AND ((assnbri)::text = (msal_1.assnbri)::text) AND (unique_id = ta_2.mnt_hist_unique_id))
  • Filter: ((msal_1.mnt_shd_code)::text = (CASE WHEN ((msc.mnt_shd_type)::text = 'N'::text) THEN aso_nest_mnt_shd_code ELSE aso_mnt_shd_code END)::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=892922
55. 1,345.104 1,345.104 ↓ 0.0 0 168,138

Index Scan using f1wtc_idx1 on f1wrk_task_ctl tc_2 (cost=0.28..8.30 rows=1 width=25) (actual time=0.008..0.008 rows=0 loops=168,138)

  • Index Cond: (((work_sys_name)::text = (ta_2.work_sys_name)::text) AND ((task_nbri)::text = (ta_2.task_nbri)::text))
  • Filter: ((task_stage)::text = ANY ('{W,C,R}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=507736
56. 152.994 6,121.242 ↓ 312.0 312 534

Nested Loop (cost=0.83..24.92 rows=1 width=8) (actual time=5.934..11.463 rows=312 loops=534)

  • Buffers: shared hit=2844232
57. 97.172 1,596.660 ↓ 315.0 315 534

Nested Loop (cost=0.55..16.61 rows=1 width=16) (actual time=0.192..2.990 rows=315 loops=534)

  • Buffers: shared hit=914380
58. 121.752 121.752 ↓ 645.0 645 534

Index Scan using f1wtcass_idx2 on f1wrk_task_ctl_ass ta_3 (cost=0.28..8.30 rows=1 width=38) (actual time=0.022..0.228 rows=645 loops=534)

  • Index Cond: (((reg_name)::text = (msal_1.reg_name)::text) AND ((assnbri)::text = (msal_1.assnbri)::text))
  • Buffers: shared hit=21458
59. 1,377.736 1,377.736 ↓ 0.0 0 344,434

Index Scan using f1faramh_idx1 on f1asr_reg_asset_mh mh2_1 (cost=0.28..8.30 rows=1 width=22) (actual time=0.004..0.004 rows=0 loops=344,434)

  • Index Cond: (((reg_name)::text = (msal_1.reg_name)::text) AND ((assnbri)::text = (msal_1.assnbri)::text) AND (unique_id = ta_3.mnt_hist_unique_id))
  • Filter: ((msal_1.mnt_shd_code)::text = (CASE WHEN ((msc.mnt_shd_type)::text = 'N'::text) THEN aso_nest_mnt_shd_code ELSE aso_mnt_shd_code END)::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=892922
60. 4,371.588 4,371.588 ↑ 1.0 1 168,138

Index Scan using f1wtc_idx1 on f1wrk_task_ctl tc_3 (cost=0.28..8.30 rows=1 width=25) (actual time=0.026..0.026 rows=1 loops=168,138)

  • Index Cond: (((work_sys_name)::text = (ta_3.work_sys_name)::text) AND ((task_nbri)::text = (ta_3.task_nbri)::text))
  • Filter: (((task_stage)::text = ANY ('{A,D}'::text[])) AND (est_start_datei > $27))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1929852
61. 0.009 0.009 ↑ 1.0 1 1

Index Scan using f1wtc_idx1 on f1wrk_task_ctl next_wo (cost=0.28..8.30 rows=1 width=33) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (((work_sys_name)::text = (next.work_sys_name)::text) AND ((task_nbri)::text = (next.task_nbri)::text))
  • Buffers: shared hit=3
62. 0.003 3,309.164 ↑ 1.0 1 1

Materialize (cost=711.06..719.13 rows=1 width=45) (actual time=3,309.163..3,309.164 rows=1 loops=1)

  • Buffers: shared hit=1412089
63. 0.004 3,309.161 ↑ 1.0 1 1

Nested Loop Left Join (cost=711.06..719.12 rows=1 width=45) (actual time=3,309.160..3,309.161 rows=1 loops=1)

  • Buffers: shared hit=1412089
64. 0.003 3,309.150 ↑ 1.0 1 1

GroupAggregate (cost=710.78..710.81 rows=1 width=29) (actual time=3,309.150..3,309.150 rows=1 loops=1)

  • Group Key: msal_2.reg_name, msal_2.assnbri, msal_2.mnt_shd_code
  • Buffers: shared hit=1412086
65. 0.010 3,309.147 ↑ 1.0 1 1

Sort (cost=710.78..710.79 rows=1 width=29) (actual time=3,309.147..3,309.147 rows=1 loops=1)

  • Sort Key: msal_2.mnt_shd_code
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1412086
66. 1.762 3,309.137 ↑ 1.0 1 1

Nested Loop (cost=82.11..710.77 rows=1 width=29) (actual time=385.307..3,309.137 rows=1 loops=1)

  • Join Filter: ((SubPlan 6) = tc_1.est_start_datei)
  • Rows Removed by Join Filter: 451
  • Buffers: shared hit=1412086
67. 0.902 3.707 ↓ 21.5 452 1

Hash Join (cost=81.84..132.31 rows=21 width=39) (actual time=1.581..3.707 rows=452 loops=1)

  • Hash Cond: (ta_1.mnt_hist_unique_id = mh_1.unique_id)
  • Buffers: shared hit=64
68. 1.315 1.315 ↓ 1.0 756 1

Seq Scan on f1wrk_task_ctl_ass ta_1 (cost=0.00..47.51 rows=735 width=38) (actual time=0.011..1.315 rows=756 loops=1)

  • Filter: (((reg_name)::text = 'ASSET'::text) AND ((assnbri)::text = '0000010'::text))
  • Rows Removed by Filter: 485
  • Buffers: shared hit=29
69. 0.183 1.490 ↓ 41.4 455 1

Hash (cost=81.70..81.70 rows=11 width=45) (actual time=1.490..1.490 rows=455 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=35
70. 0.725 1.307 ↓ 41.4 455 1

Nested Loop (cost=2.91..81.70 rows=11 width=45) (actual time=0.066..1.307 rows=455 loops=1)

  • Join Filter: ((CASE WHEN ((msc_1.mnt_shd_type)::text = 'N'::text) THEN mh_1.aso_nest_mnt_shd_code ELSE mh_1.aso_mnt_shd_code END)::text = (msc_1.mnt_shd_code)::text)
  • Rows Removed by Join Filter: 2632
  • Buffers: shared hit=35
71. 0.141 0.141 ↓ 1.0 441 1

Seq Scan on f1asr_reg_asset_mh mh_1 (cost=0.00..37.74 rows=421 width=35) (actual time=0.020..0.141 rows=441 loops=1)

  • Filter: (((reg_name)::text = 'ASSET'::text) AND ((assnbri)::text = '0000010'::text))
  • Rows Removed by Filter: 91
  • Buffers: shared hit=30
72. 0.399 0.441 ↓ 1.4 7 441

Materialize (cost=2.91..7.13 rows=5 width=32) (actual time=0.000..0.001 rows=7 loops=441)

  • Buffers: shared hit=5
73. 0.014 0.042 ↓ 1.4 7 1

Hash Join (cost=2.91..7.11 rows=5 width=32) (actual time=0.037..0.042 rows=7 loops=1)

  • Hash Cond: ((msal_2.mnt_shd_code)::text = (msc_1.mnt_shd_code)::text)
  • Buffers: shared hit=5
74. 0.010 0.010 ↓ 1.2 7 1

Index Only Scan using f1asomsal_idx1 on f1aso_mnt_shd_asset_link msal_2 (cost=0.28..4.40 rows=6 width=21) (actual time=0.008..0.010 rows=7 loops=1)

  • Index Cond: ((reg_name = 'ASSET'::text) AND (assnbri = '0000010'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=3
75. 0.007 0.018 ↑ 1.2 18 1

Hash (cost=2.36..2.36 rows=22 width=17) (actual time=0.018..0.018 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
76. 0.011 0.011 ↑ 1.2 18 1

Seq Scan on f1aso_mnt_shd_ctl msc_1 (cost=0.00..2.36 rows=22 width=17) (actual time=0.008..0.011 rows=18 loops=1)

  • Filter: ((reg_name)::text = 'ASSET'::text)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=2
77. 7.232 7.232 ↑ 1.0 1 452

Index Scan using f1wtc_idx1 on f1wrk_task_ctl tc_1 (cost=0.28..2.60 rows=1 width=33) (actual time=0.015..0.016 rows=1 loops=452)

  • Index Cond: (((work_sys_name)::text = (ta_1.work_sys_name)::text) AND ((task_nbri)::text = (ta_1.task_nbri)::text))
  • Buffers: shared hit=1364
78.          

SubPlan (for Nested Loop)

79. 0.452 3,296.436 ↑ 1.0 1 452

Aggregate (cost=24.92..24.93 rows=1 width=8) (actual time=7.293..7.293 rows=1 loops=452)

  • Buffers: shared hit=1410658
80. 26.594 3,295.984 ↓ 0.0 0 452

Nested Loop (cost=0.83..24.92 rows=1 width=8) (actual time=7.264..7.292 rows=0 loops=452)

  • Buffers: shared hit=1410658
81. 275.720 1,768.676 ↓ 369.0 369 452

Nested Loop (cost=0.55..16.61 rows=1 width=16) (actual time=0.301..3.913 rows=369 loops=452)

  • Buffers: shared hit=907164
82. 126.108 126.108 ↓ 756.0 756 452

Index Scan using f1wtcass_idx2 on f1wrk_task_ctl_ass ta2 (cost=0.28..8.30 rows=1 width=38) (actual time=0.028..0.279 rows=756 loops=452)

  • Index Cond: (((reg_name)::text = (msal_2.reg_name)::text) AND ((assnbri)::text = (msal_2.assnbri)::text))
  • Buffers: shared hit=21244
83. 1,366.848 1,366.848 ↓ 0.0 0 341,712

Index Scan using f1faramh_idx1 on f1asr_reg_asset_mh mh2 (cost=0.28..8.30 rows=1 width=22) (actual time=0.004..0.004 rows=0 loops=341,712)

  • Index Cond: (((reg_name)::text = (msal_2.reg_name)::text) AND ((assnbri)::text = (msal_2.assnbri)::text) AND (unique_id = ta2.mnt_hist_unique_id))
  • Filter: ((msal_2.mnt_shd_code)::text = (CASE WHEN ((msc_1.mnt_shd_type)::text = 'N'::text) THEN aso_nest_mnt_shd_code ELSE aso_mnt_shd_code END)::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=885920
84. 1,500.714 1,500.714 ↓ 0.0 0 166,746

Index Scan using f1wtc_idx1 on f1wrk_task_ctl tc2 (cost=0.28..8.30 rows=1 width=25) (actual time=0.009..0.009 rows=0 loops=166,746)

  • Index Cond: (((work_sys_name)::text = (ta2.work_sys_name)::text) AND ((task_nbri)::text = (ta2.task_nbri)::text))
  • Filter: ((task_stage)::text = ANY ('{W,C,R}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=503494
85. 0.007 0.007 ↑ 1.0 1 1

Index Scan using f1wtc_idx5 on f1wrk_task_ctl prev_wo (cost=0.28..8.29 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (task_rid = (max(tc_1.task_rid)))
  • Buffers: shared hit=3
86.          

SubPlan (for WindowAgg)

87. 0.084 0.084 ↑ 1.0 1 7

Seq Scan on f1aso_mnt_shd_ctl msc1 (cost=0.00..2.44 rows=1 width=2) (actual time=0.005..0.012 rows=1 loops=7)

  • Filter: (((reg_name)::text = (msal.reg_name)::text) AND ((mnt_shd_code)::text = (msal.mnt_shd_code)::text))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=14
88. 0.001 0.022 ↑ 1.0 1 1

Aggregate (cost=45.69..45.70 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=1)

  • Buffers: shared hit=3
89. 0.004 0.021 ↓ 0.0 0 1

Nested Loop Anti Join (cost=8.73..45.69 rows=1 width=0) (actual time=0.020..0.021 rows=0 loops=1)

  • Join Filter: (((mh_2.reg_name)::text = (al.reg_name)::text) AND ((mh_2.assnbri)::text = (al.assnbri)::text) AND ((mh_2.aso_mnt_shd_code)::text = (n.sub_mnt_shd_code)::text))
  • Buffers: shared hit=3
90. 0.004 0.017 ↓ 0.0 0 1

Nested Loop (cost=0.28..9.39 rows=1 width=20) (actual time=0.017..0.017 rows=0 loops=1)

  • Buffers: shared hit=3
91. 0.013 0.013 ↓ 0.0 0 1

Index Scan using f1asomsal_idx1 on f1aso_mnt_shd_asset_link al (cost=0.28..8.30 rows=1 width=21) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (((reg_name)::text = (msal.reg_name)::text) AND ((assnbri)::text = (msal.assnbri)::text) AND ((mnt_shd_code)::text = (msal.mnt_shd_code)::text))
  • Filter: (mnt_shd_start_date = to_timestamp('1900-01-01 00:00:00'::text, 'YYYY-MM-DD hh24:mi:ss'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3
92. 0.000 0.000 ↓ 0.0 0

Seq Scan on f1aso_mnt_shd_ctl_nest n (cost=0.00..1.07 rows=1 width=21) (never executed)

  • Filter: (((reg_name)::text = (msal.reg_name)::text) AND ((mnt_shd_code)::text = (msal.mnt_shd_code)::text))
93. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f1asr_reg_asset_mh mh_2 (cost=8.45..36.01 rows=17 width=26) (never executed)

  • Recheck Cond: (((reg_name)::text = (msal.reg_name)::text) AND ((assnbri)::text = (msal.assnbri)::text))
94. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f1faramh_idx2 (cost=0.00..8.45 rows=17 width=0) (never executed)

  • Index Cond: (((reg_name)::text = (msal.reg_name)::text) AND ((assnbri)::text = (msal.assnbri)::text))
95. 0.006 0.090 ↑ 1.0 1 6

Aggregate (cost=44.38..44.39 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=6)

  • Buffers: shared hit=18
96. 0.012 0.084 ↓ 0.0 0 6

Nested Loop Anti Join (cost=8.72..44.38 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=6)

  • Join Filter: (((mh_3.reg_name)::text = (al_1.reg_name)::text) AND ((mh_3.assnbri)::text = (al_1.assnbri)::text) AND ((mh_3.aso_mnt_shd_code)::text = (al_1.mnt_shd_code)::text))
  • Buffers: shared hit=18
97. 0.072 0.072 ↓ 0.0 0 6

Index Scan using f1asomsal_idx1 on f1aso_mnt_shd_asset_link al_1 (cost=0.28..8.30 rows=1 width=21) (actual time=0.012..0.012 rows=0 loops=6)

  • Index Cond: (((reg_name)::text = (msal.reg_name)::text) AND ((assnbri)::text = (msal.assnbri)::text) AND ((mnt_shd_code)::text = (msal.mnt_shd_code)::text))
  • Filter: (mnt_shd_start_date = to_timestamp('1900-01-01 00:00:00'::text, 'YYYY-MM-DD hh24:mi:ss'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=18
98. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on f1asr_reg_asset_mh mh_3 (cost=8.45..36.05 rows=2 width=26) (never executed)

  • Recheck Cond: (((reg_name)::text = (msal.reg_name)::text) AND ((assnbri)::text = (msal.assnbri)::text))
  • Filter: ((aso_mnt_shd_code)::text = (msal.mnt_shd_code)::text)
99. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on f1faramh_idx2 (cost=0.00..8.45 rows=17 width=0) (never executed)

  • Index Cond: (((reg_name)::text = (msal.reg_name)::text) AND ((assnbri)::text = (msal.assnbri)::text))
100. 0.007 0.112 ↑ 1.0 1 7

Aggregate (cost=12.59..12.60 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=7)

  • Buffers: shared hit=43
101. 0.035 0.105 ↓ 7.0 7 7

Nested Loop (cost=0.54..12.59 rows=1 width=0) (actual time=0.011..0.015 rows=7 loops=7)

  • Buffers: shared hit=43
102. 0.042 0.042 ↑ 1.0 1 7

Index Scan using tbfatc_idx4 on tbatt_attchmts_ctl attc (cost=0.27..8.29 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=7)

  • Index Cond: (((entity_type)::text = 'F1ASSNBR'::text) AND ((entity_key_1)::text = (a.reg_name)::text) AND ((entity_key_2)::text = (a.assnbri)::text))
  • Buffers: shared hit=21
103. 0.028 0.028 ↓ 7.0 7 7

Index Only Scan using tbfatd_idx1 on tbatt_attchmts_dtl attd (cost=0.27..4.29 rows=1 width=8) (actual time=0.003..0.004 rows=7 loops=7)

  • Index Cond: (unique_nbr = attc.unique_nbr)
  • Heap Fetches: 14
  • Buffers: shared hit=22
104. 0.014 0.056 ↑ 1.0 1 7

Aggregate (cost=4.29..4.30 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=7)

  • Buffers: shared hit=15
105. 0.042 0.042 ↑ 1.0 1 7

Index Only Scan using tbfatc_idx4 on tbatt_attchmts_ctl (cost=0.27..4.29 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=7)

  • Index Cond: ((entity_type = 'F1ASSNBR'::text) AND (entity_key_1 = (a.reg_name)::text) AND (entity_key_2 = (a.assnbri)::text))
  • Heap Fetches: 0
  • Buffers: shared hit=15
Planning time : 16.982 ms
Execution time : 9,708.675 ms