explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZZkfV : enable_nestloop_on_no_union_no_vpd

Settings
# exclusive inclusive rows x rows loops node
1. 2.948 638,914.271 ↓ 0.0 0 1

Merge Join (cost=1,823,849.60..1,876,130.79 rows=1 width=301) (actual time=638,914.271..638,914.271 rows=0 loops=1)

  • Merge Cond: ((mttr.asctd_mttr_id)::text = (o1.mttr_id)::text)
2. 2,811.277 638,790.355 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,811,555.84..2,281,393.24 rows=9 width=265) (actual time=30,806.240..638,790.355 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (m8.mttr_id)::text)
  • Rows Removed by Join Filter: 10683076
3. 66,369.107 630,806.128 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,253,747.47..1,666,669.97 rows=9 width=233) (actual time=28,062.744..630,806.128 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz19.mttr_id)::text)
  • Rows Removed by Join Filter: 218191268
4. 78,557.667 496,574.994 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,003,357.85..1,376,022.94 rows=9 width=208) (actual time=22,368.994..496,574.994 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz18.mttr_id)::text)
  • Rows Removed by Join Filter: 268076701
5. 3,839.238 330,068.752 ↓ 37.4 337 1

Nested Loop Left Join (cost=491,640.77..757,419.55 rows=9 width=176) (actual time=10,170.067..330,068.752 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz_1.mttr_id)::text)
  • Rows Removed by Join Filter: 12900342
6. 30,904.684 321,870.756 ↓ 37.4 337 1

Nested Loop Left Join (cost=403,459.88..667,595.49 rows=9 width=144) (actual time=9,873.064..321,870.756 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz17.mttr_id)::text)
  • Rows Removed by Join Filter: 105682540
7. 64,738.431 260,125.854 ↓ 37.4 337 1

Nested Loop Left Join (cost=288,798.16..548,992.81 rows=9 width=140) (actual time=8,104.507..260,125.854 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz.mttr_id)::text)
  • Rows Removed by Join Filter: 221058858
8. 56,086.453 128,737.922 ↓ 37.4 337 1

Nested Loop Left Join (cost=111,682.94..341,290.34 rows=9 width=118) (actual time=4,290.907..128,737.922 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz16.mttr_id)::text)
  • Rows Removed by Join Filter: 190596080
9. 391.532 16,291.926 ↓ 37.4 337 1

Nested Loop Left Join (cost=21,696.82..232,164.56 rows=9 width=103) (actual time=1,289.759..16,291.926 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz15.mttr_id)::text)
  • Rows Removed by Join Filter: 1472664
10. 7,477.743 15,527.335 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,005.90..211,102.34 rows=9 width=94) (actual time=1,232.566..15,527.335 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz13.mttr_id)::text)
  • Rows Removed by Join Filter: 25739952
11. 0.000 24.652 ↓ 14.0 126 1

Gather Merge (cost=1,001.02..113,211.78 rows=9 width=85) (actual time=24.194..24.652 rows=126 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 0.267 297.033 ↓ 23.2 93 3

Nested Loop (cost=0.99..112,210.72 rows=4 width=85) (actual time=20.354..99.011 rows=93 loops=3)

13. 1.706 295.929 ↓ 23.2 93 3

Nested Loop (cost=0.57..112,176.95 rows=4 width=85) (actual time=20.325..98.643 rows=93 loops=3)

14. 290.889 290.889 ↓ 22.2 556 3

Parallel Index Scan using mttr_idx13 on mttr (cost=0.42..112,163.74 rows=25 width=73) (actual time=18.889..96.963 rows=556 loops=3)

  • Filter: ((mttr_st_id = '1'::numeric) AND ((crtd_dt)::date >= to_date('2018-11-01'::text, 'yyyy-mm-dd'::text)) AND ((crtd_dt)::date <= to_date('2018-12-03'::text, 'yyyy-mm-dd'::text)))
  • Rows Removed by Filter: 264834
15. 3.334 3.334 ↓ 0.0 0 1,667

Index Scan using dstrt_lk_pkey on dstrt_lk d (cost=0.14..0.53 rows=1 width=27) (actual time=0.002..0.002 rows=0 loops=1,667)

  • Index Cond: (dstrt_id = mttr.dstrt_id)
  • Filter: ((dstrt_cd)::text = ANY ('{OL,OO,CI,DF,FQ,MI,OT,OH,TR,TA,TP,TC,TD,MB,FI,SI,SS,OC,SH,BT,RL,TC,TD,SV,AX}'::text[]))
  • Rows Removed by Filter: 1
16. 0.837 0.837 ↑ 1.0 1 279

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x (cost=0.42..8.44 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=279)

  • Index Cond: (mttr_id = (mttr.mttr_id)::text)
  • Heap Fetches: 1
17. 6,913.601 8,024.940 ↓ 1.0 204,288 126

Materialize (cost=4.88..61,296.80 rows=203,968 width=21) (actual time=0.004..63.690 rows=204,288 loops=126)

18. 100.583 1,111.339 ↓ 1.0 204,288 1

Subquery Scan on zz13 (cost=4.88..59,080.96 rows=203,968 width=21) (actual time=0.147..1,111.339 rows=204,288 loops=1)

19. 212.567 1,010.756 ↓ 1.0 204,288 1

Hash Left Join (cost=4.88..57,041.28 rows=203,968 width=55) (actual time=0.146..1,010.756 rows=204,288 loops=1)

  • Hash Cond: (b1.bllbl_entty_id = b2.bllbl_entty_id)
20. 383.119 798.106 ↓ 1.0 204,288 1

Merge Join (cost=0.99..52,444.16 rows=203,968 width=25) (actual time=0.042..798.106 rows=204,288 loops=1)

  • Merge Cond: ((b1.mttr_id)::text = (m.mttr_id)::text)
  • Join Filter: ((m.dstrt_id <> '35'::numeric) OR ((m.dstrt_id = '35'::numeric) AND ((b1.tm_trckg_fl)::text = 'Y'::text)))
  • Rows Removed by Join Filter: 2184
21. 146.181 146.181 ↑ 1.0 206,472 1

Index Scan using mttr_bllbl_entty_mttr_id_idx on mttr_bllbl_entty b1 (cost=0.42..11,248.89 rows=206,472 width=27) (actual time=0.012..146.181 rows=206,472 loops=1)

  • Filter: ((crrnt_fl)::text = 'Y'::text)
  • Rows Removed by Filter: 8227
22. 268.806 268.806 ↑ 1.1 746,678 1

Index Only Scan using mttr_idx15 on mttr m (cost=0.42..37,630.96 rows=796,169 width=17) (actual time=0.020..268.806 rows=746,678 loops=1)

  • Heap Fetches: 0
23. 0.044 0.083 ↑ 1.0 84 1

Hash (cost=2.84..2.84 rows=84 width=10) (actual time=0.083..0.083 rows=84 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
24. 0.039 0.039 ↑ 1.0 84 1

Seq Scan on bllbl_entty_lk b2 (cost=0.00..2.84 rows=84 width=10) (actual time=0.007..0.039 rows=84 loops=1)

25. 319.245 373.059 ↓ 106.6 4,370 337

Materialize (cost=20,690.91..21,056.79 rows=41 width=21) (actual time=0.127..1.107 rows=4,370 loops=337)

26. 2.658 53.814 ↓ 106.6 4,370 1

Subquery Scan on zz15 (cost=20,690.91..21,056.58 rows=41 width=21) (actual time=42.716..53.814 rows=4,370 loops=1)

  • Filter: (zz15.rn = 1)
  • Rows Removed by Filter: 3458
27. 6.659 51.156 ↑ 1.0 7,828 1

WindowAgg (cost=20,690.91..20,955.01 rows=8,126 width=55) (actual time=42.714..51.156 rows=7,828 loops=1)

28. 7.917 44.497 ↑ 1.0 7,828 1

Sort (cost=20,690.91..20,711.23 rows=8,126 width=25) (actual time=42.708..44.497 rows=7,828 loops=1)

  • Sort Key: b1_1.mttr_id, (CASE WHEN (b1_1.bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[])) THEN 1 ELSE 2 END), ((b1_1.last_updt_dt)::date) DESC
  • Sort Method: quicksort Memory: 804kB
29. 3.594 36.580 ↑ 1.0 7,828 1

Gather (cost=1,004.32..20,163.20 rows=8,126 width=25) (actual time=2.450..36.580 rows=7,828 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
30. 4.243 32.986 ↑ 1.2 3,914 2

Hash Left Join (cost=4.31..18,317.14 rows=4,780 width=25) (actual time=1.173..32.986 rows=3,914 loops=2)

  • Hash Cond: (b1_1.bllbl_entty_id = b2_1.bllbl_entty_id)
31. 13.677 28.671 ↑ 1.2 3,914 2

Nested Loop (cost=0.42..18,205.61 rows=4,780 width=25) (actual time=1.059..28.671 rows=3,914 loops=2)

32. 14.992 14.992 ↑ 1.2 4,114 2

Parallel Seq Scan on mttr_bllbl_entty b1_1 (cost=0.00..4,180.67 rows=4,839 width=27) (actual time=0.306..14.992 rows=4,114 loops=2)

  • Filter: ((crrnt_fl)::text = 'N'::text)
  • Rows Removed by Filter: 103236
33. 0.002 0.002 ↑ 1.0 1 8,227

Index Only Scan using mttr_idx15 on mttr m_1 (cost=0.42..2.89 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=8,227)

  • Index Cond: (mttr_id = (b1_1.mttr_id)::text)
  • Filter: ((dstrt_id <> '35'::numeric) OR ((dstrt_id = '35'::numeric) AND ((b1_1.tm_trckg_fl)::text = 'N'::text)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 0
34. 0.041 0.072 ↑ 1.0 84 2

Hash (cost=2.84..2.84 rows=84 width=10) (actual time=0.072..0.072 rows=84 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
35. 0.031 0.031 ↑ 1.0 84 2

Seq Scan on bllbl_entty_lk b2_1 (cost=0.00..2.84 rows=84 width=10) (actual time=0.007..0.031 rows=84 loops=2)

36. 53,925.157 56,359.543 ↓ 196.1 565,568 337

Materialize (cost=89,986.13..108,743.65 rows=2,884 width=27) (actual time=3.751..167.239 rows=565,568 loops=337)

37. 335.377 2,434.386 ↓ 196.1 565,568 1

Subquery Scan on zz16 (cost=89,986.13..108,729.23 rows=2,884 width=27) (actual time=1,262.969..2,434.386 rows=565,568 loops=1)

  • Filter: (zz16.rn = 1)
  • Rows Removed by Filter: 11143
38. 590.346 2,099.009 ↑ 1.0 576,711 1

WindowAgg (cost=89,986.13..101,520.35 rows=576,711 width=43) (actual time=1,262.967..2,099.009 rows=576,711 loops=1)

39. 880.773 1,508.663 ↑ 1.0 576,711 1

Sort (cost=89,986.13..91,427.90 rows=576,711 width=35) (actual time=1,262.961..1,508.663 rows=576,711 loops=1)

  • Sort Key: o1_1.mttr_id, o1_1.last_updt_dt DESC
  • Sort Method: external merge Disk: 24920kB
40. 469.224 627.890 ↑ 1.0 576,711 1

Hash Join (cost=38.24..19,031.12 rows=576,711 width=35) (actual time=1.022..627.890 rows=576,711 loops=1)

  • Hash Cond: (o1_1.orgn_id = o2_1.orgn_id)
41. 157.658 157.658 ↑ 1.0 576,711 1

Seq Scan on mttr_orgn o1_1 (cost=0.00..11,063.11 rows=576,711 width=25) (actual time=0.005..157.658 rows=576,711 loops=1)

42. 0.268 1.008 ↑ 1.0 674 1

Hash (cost=29.81..29.81 rows=674 width=20) (actual time=1.008..1.008 rows=674 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
43. 0.467 0.740 ↑ 1.0 674 1

Hash Join (cost=5.60..29.81 rows=674 width=20) (actual time=0.087..0.740 rows=674 loops=1)

  • Hash Cond: (o2_1.orgn_ctgry_id = o3_1.orgn_ctgry_id)
44. 0.198 0.198 ↑ 1.0 674 1

Seq Scan on orgn_lk o2_1 (cost=0.00..15.74 rows=674 width=10) (actual time=0.005..0.198 rows=674 loops=1)

45. 0.041 0.075 ↑ 2.0 81 1

Hash (cost=3.60..3.60 rows=160 width=20) (actual time=0.075..0.075 rows=81 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
46. 0.034 0.034 ↑ 2.0 81 1

Seq Scan on orgn_ctgry_lk o3_1 (cost=0.00..3.60 rows=160 width=20) (actual time=0.010..0.034 rows=81 loops=1)

47. 63,081.678 66,649.501 ↓ 142.4 655,962 337

Materialize (cost=177,115.21..207,091.91 rows=4,608 width=34) (actual time=5.724..197.773 rows=655,962 loops=337)

48. 389.080 3,567.823 ↓ 142.4 655,962 1

Subquery Scan on zz (cost=177,115.21..207,068.87 rows=4,608 width=34) (actual time=1,927.643..3,567.823 rows=655,962 loops=1)

  • Filter: (zz.rn = 1)
  • Rows Removed by Filter: 265689
49. 885.675 3,178.743 ↑ 1.0 921,651 1

WindowAgg (cost=177,115.21..195,548.23 rows=921,651 width=50) (actual time=1,927.642..3,178.743 rows=921,651 loops=1)

50. 1,275.000 2,293.068 ↑ 1.0 921,651 1

Sort (cost=177,115.21..179,419.34 rows=921,651 width=42) (actual time=1,927.636..2,293.068 rows=921,651 loops=1)

  • Sort Key: c1.mttr_id, c1.last_updt_dt DESC
  • Sort Method: external merge Disk: 49096kB
51. 752.079 1,018.068 ↑ 1.0 921,651 1

Hash Join (cost=31.15..29,100.89 rows=921,651 width=42) (actual time=0.764..1,018.068 rows=921,651 loops=1)

  • Hash Cond: (c1.cause_code_id = c2.cause_code_id)
52. 265.242 265.242 ↑ 1.0 921,651 1

Seq Scan on mttr_cause c1 (cost=0.00..17,443.51 rows=921,651 width=25) (actual time=0.007..265.242 rows=921,651 loops=1)

53. 0.408 0.747 ↑ 1.0 851 1

Hash (cost=20.51..20.51 rows=851 width=27) (actual time=0.747..0.747 rows=851 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 58kB
54. 0.339 0.339 ↑ 1.0 851 1

Seq Scan on cause_code_lk c2 (cost=0.00..20.51 rows=851 width=27) (actual time=0.006..0.339 rows=851 loops=1)

55. 29,355.497 30,840.218 ↓ 527.9 313,599 337

Materialize (cost=114,661.72..118,523.97 rows=594 width=16) (actual time=2.570..91.514 rows=313,599 loops=337)

56. 164.066 1,484.721 ↓ 527.9 313,599 1

Subquery Scan on zz17 (cost=114,661.72..118,521.00 rows=594 width=16) (actual time=864.755..1,484.721 rows=313,599 loops=1)

  • Filter: (zz17.rn = 1)
  • Rows Removed by Filter: 51018
57. 337.200 1,320.655 ↓ 3.1 364,617 1

WindowAgg (cost=114,661.72..117,036.66 rows=118,747 width=80) (actual time=864.753..1,320.655 rows=364,617 loops=1)

58. 668.531 983.455 ↓ 3.1 364,617 1

Sort (cost=114,661.72..114,958.59 rows=118,747 width=18) (actual time=864.746..983.455 rows=364,617 loops=1)

  • Sort Key: c1_1.mttr_id, c1_1.prmry_fl DESC NULLS LAST
  • Sort Method: external merge Disk: 10352kB
59. 0.000 314.924 ↓ 3.1 364,617 1

Gather (cost=3,263.29..102,216.81 rows=118,747 width=18) (actual time=13.714..314.924 rows=364,617 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
60. 125.013 411.339 ↓ 2.5 121,539 3

Hash Join (cost=2,263.29..89,342.11 rows=49,478 width=18) (actual time=13.170..411.339 rows=121,539 loops=3)

  • Hash Cond: ((c1_1.mp_id)::text = (c2_1.mkt_prtcp_id)::text)
61. 273.300 273.300 ↑ 1.3 264,813 3

Parallel Seq Scan on mttr_cntct c1_1 (cost=0.00..62,048.81 rows=338,417 width=18) (actual time=0.005..273.300 rows=264,813 loops=3)

  • Filter: (((actv_ind)::text = 'A'::text) AND ((row_type)::text = 'F'::text))
  • Rows Removed by Filter: 430217
62. 2.526 13.026 ↓ 1.1 6,596 3

Hash (cost=2,189.47..2,189.47 rows=5,906 width=4) (actual time=13.026..13.026 rows=6,596 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 303kB
63. 10.500 10.500 ↓ 1.1 6,596 3

Seq Scan on mkt_prtcp_eqty_hs c2_1 (cost=0.00..2,189.47 rows=5,906 width=4) (actual time=0.805..10.500 rows=6,596 loops=3)

  • Filter: (((mkt_cntr_id)::text ~~ 'NASDAQ'::text) AND (xprtn_dt = '2399-12-31'::date))
  • Rows Removed by Filter: 55102
64. 4,109.894 4,358.758 ↓ 189.5 38,280 337

Materialize (cost=88,180.89..89,797.30 rows=202 width=44) (actual time=0.405..12.934 rows=38,280 loops=337)

65. 23.961 248.864 ↓ 189.5 38,280 1

Subquery Scan on zz_1 (cost=88,180.89..89,796.29 rows=202 width=44) (actual time=135.038..248.864 rows=38,280 loops=1)

  • Filter: (zz_1.rn = 1)
  • Rows Removed by Filter: 2513
66. 73.316 224.903 ↓ 1.0 40,793 1

WindowAgg (cost=88,180.89..89,291.48 rows=40,385 width=58) (actual time=135.036..224.903 rows=40,793 loops=1)

67. 89.544 151.587 ↓ 1.0 40,793 1

Sort (cost=88,180.89..88,281.85 rows=40,385 width=300) (actual time=134.967..151.587 rows=40,793 loops=1)

  • Sort Key: mttr_cmmnt.mttr_id, mttr_cmmnt.mttr_cmmnt_id DESC
  • Sort Method: external merge Disk: 6456kB
68. 53.784 62.043 ↓ 1.0 40,793 1

Bitmap Heap Scan on mttr_cmmnt (cost=893.97..79,431.63 rows=40,385 width=300) (actual time=13.018..62.043 rows=40,793 loops=1)

  • Recheck Cond: (mttr_cmmnt_type_id = '14'::numeric)
  • Filter: ((actv_ind)::text = 'A'::text)
  • Rows Removed by Filter: 241
  • Heap Blocks: exact=29945
69. 8.259 8.259 ↓ 1.0 41,034 1

Bitmap Index Scan on mttr_cmmnt_idx2 (cost=0.00..883.88 rows=40,993 width=0) (actual time=8.259..8.259 rows=41,034 loops=1)

  • Index Cond: (mttr_cmmnt_type_id = '14'::numeric)
70. 75,382.443 87,948.575 ↓ 60.6 795,481 337

Materialize (cost=511,717.09..616,863.00 rows=13,135 width=44) (actual time=18.622..260.975 rows=795,481 loops=337)

71. 1,009.209 12,566.132 ↓ 60.6 796,049 1

Subquery Scan on zz18 (cost=511,717.09..616,797.33 rows=13,135 width=44) (actual time=6,274.174..12,566.132 rows=796,049 loops=1)

  • Filter: (zz18.rn = 1)
  • Rows Removed by Filter: 1830957
72. 4,144.929 11,556.923 ↑ 1.0 2,627,006 1

WindowAgg (cost=511,717.09..583,959.75 rows=2,627,006 width=54) (actual time=6,274.171..11,556.923 rows=2,627,006 loops=1)

73. 4,113.700 7,411.994 ↑ 1.0 2,627,006 1

Sort (cost=511,717.09..518,284.60 rows=2,627,006 width=31) (actual time=6,274.155..7,411.994 rows=2,627,006 loops=1)

  • Sort Key: s1.mttr_id, s1.prmry_fl DESC NULLS LAST
  • Sort Method: external merge Disk: 99848kB
74. 2,146.044 3,298.294 ↑ 1.0 2,627,006 1

Hash Join (cost=394.68..105,899.73 rows=2,627,006 width=31) (actual time=9.012..3,298.294 rows=2,627,006 loops=1)

  • Hash Cond: (s1.staff_id = s2.staff_id)
75. 1,143.268 1,143.268 ↑ 1.0 2,627,006 1

Seq Scan on mttr_staff s1 (cost=0.00..72,341.58 rows=2,627,006 width=19) (actual time=0.008..1,143.268 rows=2,627,006 loops=1)

  • Filter: ((actv_ind)::text = 'A'::text)
76. 4.665 8.982 ↓ 1.0 9,409 1

Hash (cost=277.08..277.08 rows=9,408 width=22) (actual time=8.982..8.982 rows=9,409 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 635kB
77. 4.317 4.317 ↓ 1.0 9,409 1

Seq Scan on staff s2 (cost=0.00..277.08 rows=9,408 width=22) (actual time=0.006..4.317 rows=9,409 loops=1)

78. 62,563.332 67,862.027 ↓ 106.8 647,453 337

Materialize (cost=250,389.62..289,843.42 rows=6,065 width=37) (actual time=9.135..201.371 rows=647,453 loops=337)

79. 546.481 5,298.695 ↓ 106.8 647,725 1

Subquery Scan on zz19 (cost=250,389.62..289,813.09 rows=6,065 width=37) (actual time=3,077.125..5,298.695 rows=647,725 loops=1)

  • Filter: (zz19.rn = 1)
  • Rows Removed by Filter: 565305
80. 1,160.971 4,752.214 ↑ 1.0 1,213,030 1

WindowAgg (cost=250,389.62..274,650.22 rows=1,213,030 width=51) (actual time=3,077.123..4,752.214 rows=1,213,030 loops=1)

81. 2,101.474 3,591.243 ↑ 1.0 1,213,030 1

Sort (cost=250,389.62..253,422.19 rows=1,213,030 width=43) (actual time=3,077.117..3,591.243 rows=1,213,030 loops=1)

  • Sort Key: p1.mttr_id, p1.mttr_prdct_id DESC
  • Sort Method: external merge Disk: 55456kB
82. 982.977 1,489.769 ↑ 1.0 1,213,030 1

Hash Left Join (cost=4.18..53,177.82 rows=1,213,030 width=43) (actual time=0.098..1,489.769 rows=1,213,030 loops=1)

  • Hash Cond: (p1.prdct_id = p2.prdct_id)
83. 506.713 506.713 ↑ 1.0 1,213,030 1

Seq Scan on mttr_prdct p1 (cost=0.00..37,965.16 rows=1,213,030 width=27) (actual time=0.009..506.713 rows=1,213,030 loops=1)

  • Filter: ((actv_ind)::text = 'A'::text)
  • Rows Removed by Filter: 107703
84. 0.044 0.079 ↑ 1.0 97 1

Hash (cost=2.97..2.97 rows=97 width=26) (actual time=0.079..0.079 rows=97 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
85. 0.035 0.035 ↑ 1.0 97 1

Seq Scan on prdct_lk p2 (cost=0.00..2.97 rows=97 width=26) (actual time=0.006..0.035 rows=97 loops=1)

86. 2,459.820 5,172.950 ↑ 7.7 31,701 337

Materialize (cost=557,808.37..565,415.33 rows=243,109 width=44) (actual time=7.905..15.350 rows=31,701 loops=337)

87. 18.827 2,713.130 ↑ 7.7 31,728 1

Subquery Scan on m8 (cost=557,808.37..562,062.78 rows=243,109 width=44) (actual time=2,663.677..2,713.130 rows=31,728 loops=1)

88. 21.813 2,694.303 ↑ 7.7 31,728 1

Unique (cost=557,808.37..559,631.69 rows=243,109 width=44) (actual time=2,663.676..2,694.303 rows=31,728 loops=1)

89. 21.265 2,672.490 ↑ 7.7 31,728 1

Sort (cost=557,808.37..558,416.15 rows=243,109 width=44) (actual time=2,663.675..2,672.490 rows=31,728 loops=1)

  • Sort Key: tm_entry.mttr_id, (sum(CASE WHEN (tm_entry.bllbl_entty_id = ANY ('{8,15}'::numeric[])) THEN tm_entry.actvy_hours_ct WHEN (tm_entry.bllbl_entty_id = '23'::numeric) THEN (tm_entry.actvy_hours_ct * 0.05) WHEN (tm_entry.bllbl_entty_id = '24'::numeric) THEN (tm_entry.actvy_hours_ct * 0.04) WHEN (tm_entry.bllbl_entty_id = '25'::numeric) THEN (tm_entry.actvy_hours_ct * 0.03) WHEN (tm_entry.bllbl_entty_id = '26'::numeric) THEN (tm_entry.actvy_hours_ct * 0.02) ELSE NULL::numeric END))
  • Sort Method: quicksort Memory: 3247kB
90. 82.115 2,651.225 ↑ 7.7 31,728 1

Finalize GroupAggregate (cost=455,167.03..528,581.26 rows=243,109 width=44) (actual time=2,239.712..2,651.225 rows=31,728 loops=1)

  • Group Key: tm_entry.mttr_id
91. 0.000 2,569.110 ↑ 5.6 86,314 1

Gather Merge (cost=455,167.03..521,895.76 rows=486,218 width=44) (actual time=2,239.691..2,569.110 rows=86,314 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
92. 524.385 7,520.340 ↑ 8.4 28,771 3

Partial GroupAggregate (cost=454,167.01..464,774.12 rows=243,109 width=44) (actual time=2,236.196..2,506.780 rows=28,771 loops=3)

  • Group Key: tm_entry.mttr_id
93. 1,812.228 6,995.955 ↓ 1.2 291,685 3

Sort (cost=454,167.01..454,797.69 rows=252,275 width=22) (actual time=2,236.181..2,331.985 rows=291,685 loops=3)

  • Sort Key: tm_entry.mttr_id
  • Sort Method: external merge Disk: 9368kB
94. 5,064.754 5,183.727 ↓ 1.2 291,685 3

Parallel Bitmap Heap Scan on tm_entry (cost=20,915.15..426,355.59 rows=252,275 width=22) (actual time=130.186..1,727.909 rows=291,685 loops=3)

  • Recheck Cond: (bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[]))
  • Rows Removed by Index Recheck: 3954501
  • Filter: ((mttr_id IS NOT NULL) AND (actvy_hours_ct > '0'::numeric) AND ((actv_ind)::text = 'A'::text))
  • Rows Removed by Filter: 33748
  • Heap Blocks: exact=19254 lossy=88839
95. 118.973 118.973 ↑ 1.0 976,299 1

Bitmap Index Scan on tm_entry_idx2 (cost=0.00..20,763.78 rows=982,554 width=0) (actual time=118.973..118.973 rows=976,299 loops=1)

  • Index Cond: (bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[]))
96. 7.417 120.968 ↑ 2.0 7,808 1

Sort (cost=12,293.76..12,332.27 rows=15,402 width=12) (actual time=118.747..120.968 rows=7,808 loops=1)

  • Sort Key: o1.mttr_id
  • Sort Method: quicksort Memory: 561kB
97. 3.114 113.551 ↑ 2.0 7,852 1

Gather (cost=1,018.04..11,222.49 rows=15,402 width=12) (actual time=0.846..113.551 rows=7,852 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
98. 62.800 110.437 ↑ 2.5 2,617 3

Hash Join (cost=18.04..8,682.29 rows=6,418 width=12) (actual time=0.465..110.437 rows=2,617 loops=3)

  • Hash Cond: (o1.orgn_id = o2.orgn_id)
99. 47.565 47.565 ↑ 1.2 192,237 3

Parallel Seq Scan on mttr_orgn o1 (cost=0.00..7,698.96 rows=240,296 width=17) (actual time=0.005..47.565 rows=192,237 loops=3)

100. 0.014 0.072 ↑ 1.0 18 3

Hash (cost=17.82..17.82 rows=18 width=5) (actual time=0.072..0.072 rows=18 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
101. 0.013 0.058 ↑ 1.0 18 3

Nested Loop (cost=4.41..17.82 rows=18 width=5) (actual time=0.032..0.058 rows=18 loops=3)

102. 0.019 0.019 ↑ 1.0 1 3

Seq Scan on orgn_ctgry_lk o3 (cost=0.00..4.00 rows=1 width=5) (actual time=0.012..0.019 rows=1 loops=3)

  • Filter: (orgn_ctgry_id = '18'::numeric)
  • Rows Removed by Filter: 80
103. 0.014 0.026 ↑ 1.0 18 3

Bitmap Heap Scan on orgn_lk o2 (cost=4.41..13.64 rows=18 width=10) (actual time=0.017..0.026 rows=18 loops=3)

  • Recheck Cond: (orgn_ctgry_id = '18'::numeric)
  • Heap Blocks: exact=4
104. 0.012 0.012 ↑ 1.0 18 3

Bitmap Index Scan on tmporgn_lk_idx1 (cost=0.00..4.41 rows=18 width=0) (actual time=0.012..0.012 rows=18 loops=3)

  • Index Cond: (orgn_ctgry_id = '18'::numeric)