explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yyvo : enable_nestloop_on_no_union_vaccum_analyzed

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 611,057.470 ↑ 1.0 1 1

Aggregate (cost=1,559,464.21..1,559,464.22 rows=1 width=8) (actual time=611,057.469..611,057.470 rows=1 loops=1)

2. 2.487 611,057.467 ↓ 0.0 0 1

Merge Join (cost=1,519,034.62..1,559,464.21 rows=1 width=0) (actual time=611,057.467..611,057.467 rows=0 loops=1)

  • Merge Cond: ((mttr.asctd_mttr_id)::text = (x_1.mttr_id)::text)
3. 62,116.421 610,916.382 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,503,336.77..1,866,509.83 rows=9 width=12) (actual time=29,727.380..610,916.382 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz19.mttr_id)::text)
  • Rows Removed by Join Filter: 218160601
4. 75,251.015 482,812.328 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,258,400.54..1,581,316.19 rows=9 width=24) (actual time=24,307.958..482,812.328 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz18.mttr_id)::text)
  • Rows Removed by Join Filter: 268001213
5. 3,288.343 322,265.265 ↓ 37.4 337 1

Nested Loop Left Join (cost=693,793.74..929,525.64 rows=9 width=24) (actual time=13,098.796..322,265.265 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz_1.mttr_id)::text)
  • Rows Removed by Join Filter: 12895961
6. 29,543.291 315,225.438 ↓ 37.4 337 1

Nested Loop Left Join (cost=580,303.71..814,695.32 rows=9 width=24) (actual time=12,129.685..315,225.438 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz17.mttr_id)::text)
  • Rows Removed by Join Filter: 105682203
7. 62,063.340 254,615.465 ↓ 37.4 337 1

Nested Loop Left Join (cost=441,065.90..671,516.56 rows=9 width=24) (actual time=10,170.400..254,615.465 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz.mttr_id)::text)
  • Rows Removed by Join Filter: 221038975
8. 53,537.307 127,806.337 ↓ 37.4 337 1

Nested Loop Left Join (cost=249,331.57..449,194.97 rows=9 width=24) (actual time=5,715.512..127,806.337 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz16.mttr_id)::text)
  • Rows Removed by Join Filter: 190575186
9. 374.300 18,375.221 ↓ 37.4 337 1

Nested Loop Left Join (cost=119,260.69..299,984.43 rows=9 width=24) (actual time=2,294.459..18,375.221 rows=337 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz15.mttr_id)::text)
  • Rows Removed by Join Filter: 1472664
10. 7,636.920 17,603.261 ↓ 37.4 337 1

Nested Loop Left Join (cost=93,734.66..274,087.09 rows=9 width=24) (actual time=2,213.828..17,603.261 rows=337 loops=1)

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

Gather Merge (cost=1,001.02..113,211.78 rows=9 width=24) (actual time=29.369..30.485 rows=126 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 0.348 305.970 ↓ 23.2 93 3

Nested Loop (cost=0.99..112,210.72 rows=4 width=24) (actual time=23.994..101.990 rows=93 loops=3)

13. 1.781 304.785 ↓ 23.2 93 3

Nested Loop (cost=0.57..112,176.95 rows=4 width=24) (actual time=23.970..101.595 rows=93 loops=3)

14. 299.670 299.670 ↓ 22.2 556 3

Parallel Index Scan using mttr_idx13 on mttr (cost=0.42..112,163.74 rows=25 width=34) (actual time=22.284..99.890 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=5) (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: 18
17. 7,668.217 9,935.856 ↓ 1.0 204,272 126

Materialize (cost=92,733.64..125,881.55 rows=203,968 width=12) (actual time=12.069..78.856 rows=204,272 loops=126)

18. 80.068 2,267.639 ↓ 1.0 204,272 1

Subquery Scan on zz13 (cost=92,733.64..123,865.71 rows=203,968 width=12) (actual time=1,520.327..2,267.639 rows=204,272 loops=1)

19. 479.664 2,187.571 ↓ 1.0 204,272 1

Hash Join (cost=92,733.64..121,826.03 rows=203,968 width=88) (actual time=1,520.325..2,187.571 rows=204,272 loops=1)

  • Hash Cond: ((x_3.mttr_id)::text = (x_2.mttr_id)::text)
20. 187.821 187.821 ↓ 1.0 795,945 1

Seq Scan on xcld_fp_mttr_id_tbl x_3 (cost=0.00..12,262.57 rows=795,857 width=12) (actual time=0.010..187.821 rows=795,945 loops=1)

21. 87.869 1,520.086 ↓ 1.0 204,272 1

Hash (cost=88,192.04..88,192.04 rows=203,968 width=49) (actual time=1,520.086..1,520.086 rows=204,272 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2639kB
22. 226.079 1,432.217 ↓ 1.0 204,272 1

Hash Join (cost=26,098.78..88,192.04 rows=203,968 width=49) (actual time=526.312..1,432.217 rows=204,272 loops=1)

  • Hash Cond: ((mttr_bllbl_entty.mttr_id)::text = (x_2.mttr_id)::text)
23. 316.942 680.559 ↓ 1.0 204,288 1

Merge Join (cost=0.99..52,444.16 rows=203,968 width=37) (actual time=0.050..680.559 rows=204,288 loops=1)

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

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

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

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

  • Heap Fetches: 0
26. 305.785 525.579 ↓ 1.0 795,945 1

Hash (cost=12,262.57..12,262.57 rows=795,857 width=12) (actual time=525.579..525.579 rows=795,945 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3227kB
27. 219.794 219.794 ↓ 1.0 795,945 1

Seq Scan on xcld_fp_mttr_id_tbl x_2 (cost=0.00..12,262.57 rows=795,857 width=12) (actual time=0.007..219.794 rows=795,945 loops=1)

28. 319.953 397.660 ↓ 106.6 4,370 337

Materialize (cost=25,526.03..25,891.91 rows=41 width=12) (actual time=0.202..1.180 rows=4,370 loops=337)

29. 2.554 77.707 ↓ 106.6 4,370 1

Subquery Scan on zz15 (cost=25,526.03..25,891.70 rows=41 width=12) (actual time=67.716..77.707 rows=4,370 loops=1)

  • Filter: (zz15.rn = 1)
  • Rows Removed by Filter: 3458
30. 5.814 75.153 ↑ 1.0 7,828 1

WindowAgg (cost=25,526.03..25,790.13 rows=8,126 width=88) (actual time=67.714..75.153 rows=7,828 loops=1)

31. 7.524 69.339 ↑ 1.0 7,828 1

Sort (cost=25,526.03..25,546.35 rows=8,126 width=20) (actual time=67.709..69.339 rows=7,828 loops=1)

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

Gather (cost=1,001.27..24,998.32 rows=8,126 width=20) (actual time=2.636..61.815 rows=7,828 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
33. 13.078 57.307 ↑ 1.2 3,914 2

Nested Loop (cost=1.27..23,152.26 rows=4,780 width=20) (actual time=0.897..57.307 rows=3,914 loops=2)

34. 14.203 44.227 ↑ 1.2 3,914 2

Nested Loop (cost=0.85..20,684.85 rows=4,780 width=49) (actual time=0.883..44.227 rows=3,914 loops=2)

  • Join Filter: ((mttr_bllbl_entty_1.mttr_id)::text = (x_4.mttr_id)::text)
35. 14.826 30.021 ↑ 1.2 3,914 2

Nested Loop (cost=0.42..18,205.61 rows=4,780 width=37) (actual time=0.866..30.021 rows=3,914 loops=2)

36. 15.192 15.192 ↑ 1.2 4,114 2

Parallel Seq Scan on mttr_bllbl_entty mttr_bllbl_entty_1 (cost=0.00..4,180.67 rows=4,839 width=27) (actual time=0.266..15.192 rows=4,114 loops=2)

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

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

  • Index Cond: (mttr_id = (mttr_bllbl_entty_1.mttr_id)::text)
  • Filter: ((dstrt_id <> '35'::numeric) OR ((dstrt_id = '35'::numeric) AND ((mttr_bllbl_entty_1.tm_trckg_fl)::text = 'N'::text)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 0
38. 0.003 0.003 ↑ 1.0 1 7,828

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_4 (cost=0.42..0.51 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=7,828)

  • Index Cond: (mttr_id = (mttr_2.mttr_id)::text)
  • Heap Fetches: 3679
39. 0.002 0.002 ↑ 1.0 1 7,828

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_5 (cost=0.42..0.51 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=7,828)

  • Index Cond: (mttr_id = (x_4.mttr_id)::text)
  • Heap Fetches: 3679
40. 52,866.052 55,893.809 ↓ 196.1 565,506 337

Materialize (cost=130,070.88..148,828.40 rows=2,884 width=12) (actual time=6.173..165.857 rows=565,506 loops=337)

41. 236.380 3,027.757 ↓ 196.1 565,506 1

Subquery Scan on zz16 (cost=130,070.88..148,813.98 rows=2,884 width=12) (actual time=2,079.061..3,027.757 rows=565,506 loops=1)

  • Filter: (zz16.rn = 1)
  • Rows Removed by Filter: 11143
42. 465.691 2,791.377 ↑ 1.0 576,649 1

WindowAgg (cost=130,070.88..141,605.10 rows=576,711 width=186) (actual time=2,079.059..2,791.377 rows=576,649 loops=1)

43. 723.269 2,325.686 ↑ 1.0 576,649 1

Sort (cost=130,070.88..131,512.65 rows=576,711 width=20) (actual time=2,079.053..2,325.686 rows=576,649 loops=1)

  • Sort Key: mttr_orgn_1.mttr_id, mttr_orgn_1.last_updt_dt DESC
  • Sort Method: external merge Disk: 19200kB
44. 613.778 1,602.417 ↑ 1.0 576,649 1

Hash Join (cost=26,136.02..63,056.88 rows=576,711 width=20) (actual time=568.961..1,602.417 rows=576,649 loops=1)

  • Hash Cond: ((mttr_orgn_1.mttr_id)::text = (x_6.mttr_id)::text)
45. 398.123 533.791 ↑ 1.0 576,711 1

Hash Join (cost=38.24..19,031.12 rows=576,711 width=20) (actual time=0.900..533.791 rows=576,711 loops=1)

  • Hash Cond: (mttr_orgn_1.orgn_id = o2_1.orgn_id)
46. 134.780 134.780 ↑ 1.0 576,711 1

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

47. 0.225 0.888 ↑ 1.0 674 1

Hash (cost=29.81..29.81 rows=674 width=5) (actual time=0.888..0.888 rows=674 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
48. 0.431 0.663 ↑ 1.0 674 1

Hash Join (cost=5.60..29.81 rows=674 width=5) (actual time=0.068..0.663 rows=674 loops=1)

  • Hash Cond: (o2_1.orgn_ctgry_id = o3_1.orgn_ctgry_id)
49. 0.175 0.175 ↑ 1.0 674 1

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

50. 0.030 0.057 ↑ 2.0 81 1

Hash (cost=3.60..3.60 rows=160 width=5) (actual time=0.057..0.057 rows=81 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
51. 0.027 0.027 ↑ 2.0 81 1

Seq Scan on orgn_ctgry_lk o3_1 (cost=0.00..3.60 rows=160 width=5) (actual time=0.005..0.027 rows=81 loops=1)

52. 264.920 454.848 ↓ 1.0 795,945 1

Hash (cost=12,262.57..12,262.57 rows=795,857 width=12) (actual time=454.848..454.848 rows=795,945 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3157kB
53. 189.928 189.928 ↓ 1.0 795,945 1

Seq Scan on xcld_fp_mttr_id_tbl x_6 (cost=0.00..12,262.57 rows=795,857 width=12) (actual time=0.005..189.928 rows=795,945 loops=1)

54. 60,463.819 64,745.788 ↓ 142.3 655,903 337

Materialize (cost=191,734.33..221,711.03 rows=4,608 width=12) (actual time=8.586..192.124 rows=655,903 loops=337)

55. 328.722 4,281.969 ↓ 142.3 655,903 1

Subquery Scan on zz (cost=191,734.33..221,687.99 rows=4,608 width=12) (actual time=2,892.359..4,281.969 rows=655,903 loops=1)

  • Filter: (zz.rn = 1)
  • Rows Removed by Filter: 265667
56. 706.570 3,953.247 ↑ 1.0 921,570 1

WindowAgg (cost=191,734.33..210,167.35 rows=921,651 width=166) (actual time=2,892.357..3,953.247 rows=921,570 loops=1)

57. 1,159.568 3,246.677 ↑ 1.0 921,570 1

Sort (cost=191,734.33..194,038.46 rows=921,651 width=20) (actual time=2,892.351..3,246.677 rows=921,570 loops=1)

  • Sort Key: mttr_cause.mttr_id, mttr_cause.last_updt_dt DESC
  • Sort Method: external merge Disk: 30688kB
58. 805.864 2,087.109 ↑ 1.0 921,570 1

Hash Join (cost=26,128.93..81,523.51 rows=921,651 width=20) (actual time=444.838..2,087.109 rows=921,570 loops=1)

  • Hash Cond: ((mttr_cause.mttr_id)::text = (x_7.mttr_id)::text)
59. 621.320 837.518 ↑ 1.0 921,651 1

Hash Join (cost=31.15..29,100.89 rows=921,651 width=20) (actual time=0.545..837.518 rows=921,651 loops=1)

  • Hash Cond: (mttr_cause.cause_code_id = c2.cause_code_id)
60. 215.671 215.671 ↑ 1.0 921,651 1

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

61. 0.279 0.527 ↑ 1.0 851 1

Hash (cost=20.51..20.51 rows=851 width=5) (actual time=0.527..0.527 rows=851 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
62. 0.248 0.248 ↑ 1.0 851 1

Seq Scan on cause_code_lk c2 (cost=0.00..20.51 rows=851 width=5) (actual time=0.008..0.248 rows=851 loops=1)

63. 258.447 443.727 ↓ 1.0 795,945 1

Hash (cost=12,262.57..12,262.57 rows=795,857 width=12) (actual time=443.727..443.727 rows=795,945 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3157kB
64. 185.280 185.280 ↓ 1.0 795,945 1

Seq Scan on xcld_fp_mttr_id_tbl x_7 (cost=0.00..12,262.57 rows=795,857 width=12) (actual time=0.007..185.280 rows=795,945 loops=1)

65. 29,339.943 31,066.682 ↓ 527.9 313,598 337

Materialize (cost=139,237.81..143,100.06 rows=594 width=12) (actual time=3.537..92.186 rows=313,598 loops=337)

66. 139.340 1,726.739 ↓ 527.9 313,598 1

Subquery Scan on zz17 (cost=139,237.81..143,097.09 rows=594 width=12) (actual time=1,190.488..1,726.739 rows=313,598 loops=1)

  • Filter: (zz17.rn = 1)
  • Rows Removed by Filter: 51018
67. 297.267 1,587.399 ↓ 3.1 364,616 1

WindowAgg (cost=139,237.81..141,612.75 rows=118,747 width=96) (actual time=1,190.486..1,587.399 rows=364,616 loops=1)

68. 685.345 1,290.132 ↓ 3.1 364,616 1

Sort (cost=139,237.81..139,534.68 rows=118,747 width=14) (actual time=1,190.481..1,290.132 rows=364,616 loops=1)

  • Sort Key: mttr_cntct.mttr_id, mttr_cntct.prmry_fl DESC NULLS LAST
  • Sort Method: external merge Disk: 8568kB
69. 0.000 604.787 ↓ 3.1 364,616 1

Gather (cost=3,263.72..127,198.91 rows=118,747 width=14) (actual time=13.724..604.787 rows=364,616 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
70. 424.703 835.336 ↓ 2.5 121,539 3

Nested Loop (cost=2,263.72..114,324.21 rows=49,478 width=14) (actual time=12.511..835.336 rows=121,539 loops=3)

71. 128.215 410.631 ↓ 2.5 121,539 3

Hash Join (cost=2,263.29..89,342.11 rows=49,478 width=14) (actual time=12.488..410.631 rows=121,539 loops=3)

  • Hash Cond: ((mttr_cntct.mp_id)::text = (c2_1.mkt_prtcp_id)::text)
72. 270.033 270.033 ↑ 1.3 264,813 3

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

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 303kB
74. 10.159 10.159 ↓ 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.820..10.159 rows=6,596 loops=3)

  • Filter: (((mkt_cntr_id)::text ~~ 'NASDAQ'::text) AND (xprtn_dt = '2399-12-31'::date))
  • Rows Removed by Filter: 55102
75. 0.002 0.002 ↑ 1.0 1 364,617

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_8 (cost=0.42..0.50 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=364,617)

  • Index Cond: (mttr_id = (mttr_cntct.mttr_id)::text)
  • Heap Fetches: 45579
76. 2,808.035 3,751.484 ↓ 189.4 38,267 337

Materialize (cost=113,490.03..114,803.56 rows=202 width=12) (actual time=2.626..11.132 rows=38,267 loops=337)

77. 16.421 943.449 ↓ 189.4 38,267 1

Subquery Scan on zz_1 (cost=113,490.03..114,802.55 rows=202 width=12) (actual time=884.697..943.449 rows=38,267 loops=1)

  • Filter: (zz_1.rn = 1)
  • Rows Removed by Filter: 2513
78. 32.377 927.028 ↓ 1.0 40,780 1

WindowAgg (cost=113,490.03..114,297.73 rows=40,385 width=58) (actual time=884.695..927.028 rows=40,780 loops=1)

79. 65.343 894.651 ↓ 1.0 40,780 1

Sort (cost=113,490.03..113,591.00 rows=40,385 width=18) (actual time=884.688..894.651 rows=40,780 loops=1)

  • Sort Key: mttr_cmmnt.mttr_id, mttr_cmmnt.mttr_cmmnt_id DESC
  • Sort Method: external sort Disk: 1160kB
80. 326.064 829.308 ↓ 1.0 40,780 1

Hash Join (cost=26,991.76..110,400.27 rows=40,385 width=18) (actual time=461.797..829.308 rows=40,780 loops=1)

  • Hash Cond: ((mttr_cmmnt.mttr_id)::text = (x_9.mttr_id)::text)
81. 46.611 55.106 ↓ 1.0 40,793 1

Bitmap Heap Scan on mttr_cmmnt (cost=893.97..79,431.63 rows=40,385 width=18) (actual time=13.230..55.106 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
82. 8.495 8.495 ↓ 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.495..8.495 rows=41,034 loops=1)

  • Index Cond: (mttr_cmmnt_type_id = '14'::numeric)
83. 261.340 448.138 ↓ 1.0 795,945 1

Hash (cost=12,262.57..12,262.57 rows=795,857 width=12) (actual time=448.138..448.138 rows=795,945 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3226kB
84. 186.798 186.798 ↓ 1.0 795,945 1

Seq Scan on xcld_fp_mttr_id_tbl x_9 (cost=0.00..12,262.57 rows=795,857 width=12) (actual time=0.009..186.798 rows=795,945 loops=1)

85. 73,969.214 85,296.048 ↓ 60.5 795,257 337

Materialize (cost=564,606.79..650,050.16 rows=13,135 width=12) (actual time=23.383..253.104 rows=795,257 loops=337)

86. 738.654 11,326.834 ↓ 60.6 795,825 1

Subquery Scan on zz18 (cost=564,606.79..649,984.49 rows=13,135 width=12) (actual time=7,878.405..11,326.834 rows=795,825 loops=1)

  • Filter: (zz18.rn = 1)
  • Rows Removed by Filter: 1829922
87. 1,813.158 10,588.180 ↑ 1.0 2,625,747 1

WindowAgg (cost=564,606.79..617,146.91 rows=2,627,006 width=54) (actual time=7,878.404..10,588.180 rows=2,625,747 loops=1)

88. 3,452.205 8,775.022 ↑ 1.0 2,625,747 1

Sort (cost=564,606.79..571,174.31 rows=2,627,006 width=14) (actual time=7,878.396..8,775.022 rows=2,625,747 loops=1)

  • Sort Key: mttr_staff.mttr_id, mttr_staff.prmry_fl DESC NULLS LAST
  • Sort Method: external merge Disk: 61672kB
89. 2,224.077 5,322.817 ↑ 1.0 2,625,747 1

Hash Join (cost=26,492.46..194,706.44 rows=2,627,006 width=14) (actual time=444.973..5,322.817 rows=2,625,747 loops=1)

  • Hash Cond: ((mttr_staff.mttr_id)::text = (x_10.mttr_id)::text)
90. 1,672.750 2,660.119 ↑ 1.0 2,627,006 1

Hash Join (cost=394.68..105,899.73 rows=2,627,006 width=14) (actual time=5.897..2,660.119 rows=2,627,006 loops=1)

  • Hash Cond: (mttr_staff.staff_id = s2.staff_id)
91. 981.495 981.495 ↑ 1.0 2,627,006 1

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

  • Filter: ((actv_ind)::text = 'A'::text)
92. 3.139 5.874 ↓ 1.0 9,409 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 469kB
93. 2.735 2.735 ↓ 1.0 9,409 1

Seq Scan on staff s2 (cost=0.00..277.08 rows=9,408 width=5) (actual time=0.005..2.735 rows=9,409 loops=1)

94. 253.066 438.621 ↓ 1.0 795,945 1

Hash (cost=12,262.57..12,262.57 rows=795,857 width=12) (actual time=438.621..438.621 rows=795,945 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3157kB
95. 185.555 185.555 ↓ 1.0 795,945 1

Seq Scan on xcld_fp_mttr_id_tbl x_10 (cost=0.00..12,262.57 rows=795,857 width=12) (actual time=0.007..185.555 rows=795,945 loops=1)

96. 60,827.960 65,987.633 ↓ 106.7 647,362 337

Materialize (cost=244,936.23..284,390.03 rows=6,065 width=12) (actual time=10.158..195.809 rows=647,362 loops=337)

97. 393.265 5,159.673 ↓ 106.8 647,634 1

Subquery Scan on zz19 (cost=244,936.23..284,359.70 rows=6,065 width=12) (actual time=3,421.853..5,159.673 rows=647,634 loops=1)

  • Filter: (zz19.rn = 1)
  • Rows Removed by Filter: 565258
98. 905.228 4,766.408 ↑ 1.0 1,212,892 1

WindowAgg (cost=244,936.23..269,196.83 rows=1,213,030 width=210) (actual time=3,421.851..4,766.408 rows=1,212,892 loops=1)

99. 1,965.297 3,861.180 ↑ 1.0 1,212,892 1

Sort (cost=244,936.23..247,968.80 rows=1,213,030 width=18) (actual time=3,421.843..3,861.180 rows=1,212,892 loops=1)

  • Sort Key: mttr_prdct.mttr_id, mttr_prdct.mttr_prdct_id DESC
  • Sort Method: external merge Disk: 34488kB
100. 955.566 1,895.883 ↑ 1.0 1,212,892 1

Hash Join (cost=26,097.78..97,480.43 rows=1,213,030 width=18) (actual time=449.173..1,895.883 rows=1,212,892 loops=1)

  • Hash Cond: ((mttr_prdct.mttr_id)::text = (x_11.mttr_id)::text)
101. 491.769 491.769 ↑ 1.0 1,213,030 1

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

  • Filter: ((actv_ind)::text = 'A'::text)
  • Rows Removed by Filter: 107703
102. 262.771 448.548 ↓ 1.0 795,945 1

Hash (cost=12,262.57..12,262.57 rows=795,857 width=12) (actual time=448.548..448.548 rows=795,945 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3227kB
103. 185.777 185.777 ↓ 1.0 795,945 1

Seq Scan on xcld_fp_mttr_id_tbl x_11 (cost=0.00..12,262.57 rows=795,857 width=12) (actual time=0.008..185.777 rows=795,945 loops=1)

104. 7.325 138.598 ↑ 2.0 7,808 1

Sort (cost=15,697.86..15,736.36 rows=15,402 width=24) (actual time=136.948..138.598 rows=7,808 loops=1)

  • Sort Key: mttr_orgn.mttr_id
  • Sort Method: quicksort Memory: 806kB
105. 3.710 131.273 ↑ 2.0 7,852 1

Gather (cost=1,018.47..14,626.58 rows=15,402 width=24) (actual time=1.140..131.273 rows=7,852 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
106. 10.566 127.563 ↑ 2.5 2,617 3

Nested Loop (cost=18.47..12,086.38 rows=6,418 width=24) (actual time=0.270..127.563 rows=2,617 loops=3)

107. 64.167 116.994 ↑ 2.5 2,617 3

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

  • Hash Cond: (mttr_orgn.orgn_id = o2.orgn_id)
108. 52.756 52.756 ↑ 1.2 192,237 3

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

109. 0.015 0.071 ↑ 1.0 18 3

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

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

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

111. 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
112. 0.012 0.024 ↑ 1.0 18 3

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

  • Recheck Cond: (orgn_ctgry_id = '18'::numeric)
  • Heap Blocks: exact=4
113. 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)
114. 0.003 0.003 ↑ 1.0 1 7,852

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_1 (cost=0.42..0.53 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=7,852)

  • Index Cond: (mttr_id = (mttr_orgn.mttr_id)::text)
  • Heap Fetches: 2611