# Result: wgFb : Optimization for: plan #ZN9x

Settings

### Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 9,862.772 79,709.186 ↑ 4.3 61,489 1

(cost=483,332.98..42,193,957.62 rows=264,662 width=376) (actual time=50,236.268..79,709.186 rows=61,489 loops=1)

• Group Key: cu.id, ces.id, cf.fid, st.id, tm.id, cr.id, sh.id
2.

CTE fd

3. 0.273 2.699 ↑ 1.0 1 1

Aggregate (cost=2,309.71..2,309.72 rows=1 width=8) (actual time=2.698..2.699 rows=1 loops=1)

4. 2.426 2.426 ↑ 1.0 297 1

Seq Scan on students_list_bck (cost=0.00..2,308.97 rows=297 width=8) (actual time=0.015..2.426 rows=297 loops=1)

5.

Initplan (for GroupAggregate)

6. 2.705 2.705 ↑ 1.0 1 1

CTE Scan on fd (cost=0.00..0.02 rows=1 width=8) (actual time=2.703..2.705 rows=1 loops=1)

7. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on fd fd_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

8. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on fd fd_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

9. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on fd fd_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

10. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on fd fd_4 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

11. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on fd fd_5 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

12. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on fd fd_6 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

13. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on fd fd_7 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

14. 16,724.459 54,594.426 ↓ 14.0 3,699,508 1

(cost=481,023.09..481,684.75 rows=264,662 width=376) (actual time=50,235.752..54,594.426 rows=3,699,508 loops=1)

• Sort Key: cu.id, ces.id, cf.fid, st.id, tm.id, cr.id, sh.id
• Sort Method: external merge Disk: 1,154,176kB
15. 6,608.857 37,869.967 ↓ 14.0 3,699,508 1

(cost=399,225.10..411,954.76 rows=264,662 width=376) (actual time=26,617.564..37,869.967 rows=3,699,508 loops=1)

• Merge Cond: ((sbd.user_id = (COALESCE(pd1.user_id, pd2.user_id))) AND (acd.sis_id = ((cr.sis_fid)::text)) AND (tmd.sis_id = ((tm.sis_fid)::text)))
• Filter: ((COALESCE(pd1.updated_at, '2000-01-01 00:00:00+01'::timestamp with time zone) > \$9) OR (COALESCE(pd2.updated_at, '2000-01-01 00:00:00+01'::timestamp with time zone) > \$10) OR (st.updated_at > \$11) OR (COALESCE(en.updated_at, '2000-01-01 00:00:00+01'::timestamp with time zone) > \$12) OR (COALESCE(ese.updated_at, '2000-01-01 00:00:00+01'::timestamp with time zone) > \$13) OR (COALESCE(sbd.created_at, '2000-01-01 00:00:00+01'::timestamp with time zone) > \$14) OR (COALESCE(asd.created_at, '2000-01-01 00:00:00+01'::timestamp with time zone) > \$15) OR (COALESCE(crd.created_at, '2000-01-01 00:00:00+01'::timestamp with time zone) > \$16))
• Rows Removed by Filter: 4,718
16. 5,492.783 25,433.095 ↓ 3.8 1,223,617 1

(cost=299,207.09..300,011.74 rows=321,861 width=84) (actual time=23,201.042..25,433.095 rows=1,223,617 loops=1)

• Sort Key: sbd.user_id, acd.sis_id, tmd.sis_id
• Sort Method: external merge Disk: 108,272kB
17. 7,356.945 19,940.312 ↓ 3.8 1,223,617 1

(cost=109,507.06..254,359.62 rows=321,861 width=84) (actual time=7,885.813..19,940.312 rows=1,223,617 loops=1)

• Hash Cond: (sbf.submission_id = sbd.id)
18. 4,697.799 4,697.799 ↑ 1.0 4,873,887 1

Seq Scan on raw_submissions_facts sbf (cost=0.00..71,357.87 rows=4,873,887 width=16) (actual time=0.010..4,697.799 rows=4,873,887 loops=1)

19. 1,857.422 7,885.568 ↓ 3.8 1,223,617 1

(cost=101,082.80..101,082.80 rows=321,861 width=84) (actual time=7,885.568..7,885.568 rows=1,223,617 loops=1)

• Buckets: 262,144 (originally 262144) Batches: 8 (originally 2) Memory Usage: 30,721kB
20. 2,243.866 6,028.146 ↓ 3.8 1,223,617 1

(cost=217.19..101,082.80 rows=321,861 width=84) (actual time=15.109..6,028.146 rows=1,223,617 loops=1)

21. 20.450 123.392 ↓ 2.4 9,339 1

(cost=216.76..3,373.58 rows=3,870 width=42) (actual time=15.091..123.392 rows=9,339 loops=1)

• Hash Cond: (asd.course_id = crd.id)
22. 39.039 94.653 ↓ 1.6 9,339 1

(cost=131.75..3,227.50 rows=5,966 width=32) (actual time=6.795..94.653 rows=9,339 loops=1)

• Hash Cond: (asd.assignment_group_id = agf.assignment_group_id)
23. 48.854 48.854 ↓ 1.0 16,126 1

Seq Scan on raw_assignments_dim asd (cost=0.00..2,975.93 rows=16,045 width=40) (actual time=0.010..48.854 rows=16,126 loops=1)

• Filter: (status = 'published'::text)
• Rows Removed by Filter: 28,668
24. 1.127 6.760 ↑ 1.0 2,163 1

(cost=104.71..104.71 rows=2,163 width=8) (actual time=6.760..6.760 rows=2,163 loops=1)

• Buckets: 4,096 Batches: 1 Memory Usage: 117kB
25. 5.633 5.633 ↑ 1.0 2,163 1

Seq Scan on raw_assignment_groups_facts agf (cost=0.00..104.71 rows=2,163 width=8) (actual time=0.008..5.633 rows=2,163 loops=1)

• Filter: (group_weight > '0'::double precision)
• Rows Removed by Filter: 3,654
26. 0.686 8.289 ↓ 1.4 1,313 1

(cost=73.63..73.63 rows=910 width=26) (actual time=8.289..8.289 rows=1,313 loops=1)

• Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 88kB
27. 5.365 7.603 ↓ 1.4 1,313 1

(cost=3.73..73.63 rows=910 width=26) (actual time=0.095..7.603 rows=1,313 loops=1)

• Hash Cond: (crd.term_id = tmd.id)
28. 1.448 2.222 ↓ 1.4 1,313 1

(cost=2.53..59.92 rows=910 width=30) (actual time=0.076..2.222 rows=1,313 loops=1)

• Hash Cond: (crd.account_id = acd.id)
29. 0.707 0.707 ↑ 1.0 1,403 1

Seq Scan on raw_courses_dim crd (cost=0.00..43.03 rows=1,403 width=32) (actual time=0.004..0.707 rows=1,403 loops=1)

30. 0.030 0.067 ↑ 1.0 48 1

(cost=1.93..1.93 rows=48 width=14) (actual time=0.067..0.067 rows=48 loops=1)

• Buckets: 1,024 Batches: 1 Memory Usage: 11kB
31. 0.037 0.037 ↑ 1.0 48 1

Seq Scan on raw_accounts_dim acd (cost=0.00..1.93 rows=48 width=14) (actual time=0.008..0.037 rows=48 loops=1)

• Filter: (status <> 'deleted'::text)
• Rows Removed by Filter: 26
32. 0.011 0.016 ↑ 1.0 9 1

(cost=1.09..1.09 rows=9 width=12) (actual time=0.016..0.016 rows=9 loops=1)

• Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.005 0.005 ↑ 1.0 9 1

Seq Scan on raw_terms_dim tmd (cost=0.00..1.09 rows=9 width=12) (actual time=0.002..0.005 rows=9 loops=1)

34. 3,660.888 3,660.888 ↑ 1.3 131 9,339

Index Scan using raw_dim_submissions_assignment_id_idx on raw_submissions_dim sbd (cost=0.43..23.50 rows=175 width=50) (actual time=0.017..0.392 rows=131 loops=9,339)

• Index Cond: (assignment_id = asd.id)
• Filter: (status <> 'deleted'::text)
• Rows Removed by Filter: 7
35. 1,784.716 5,828.015 ↓ 13.4 3,704,226 1

(cost=100,018.02..101,395.06 rows=275,408 width=334) (actual time=3,413.793..5,828.015 rows=3,704,226 loops=1)

36. 2,118.733 4,043.299 ↑ 1.1 247,478 1

(cost=100,018.02..100,706.54 rows=275,408 width=334) (actual time=3,413.787..4,043.299 rows=247,478 loops=1)

• Sort Key: (COALESCE(pd1.user_id, pd2.user_id)), ((cr.sis_fid)::text), ((tm.sis_fid)::text)
• Sort Method: external merge Disk: 68,312kB
37. 463.701 1,924.566 ↑ 1.1 247,478 1

(cost=24,597.10..32,772.74 rows=275,408 width=334) (actual time=1,152.815..1,924.566 rows=247,478 loops=1)

• Hash Cond: (en.student_id = st.id)
38. 208.823 575.439 ↓ 1.1 245,387 1

(cost=7,904.71..12,470.89 rows=228,101 width=41) (actual time=267.308..575.439 rows=245,387 loops=1)

• Hash Cond: (ese.enroll_id = en.id)
39. 60.003 105.987 ↑ 1.0 59,738 1

(cost=219.44..3,964.22 rows=59,738 width=23) (actual time=6.533..105.987 rows=59,738 loops=1)

• Hash Cond: (ese.event_session_id = es.id)
40. 39.472 39.472 ↑ 1.0 59,738 1

Seq Scan on events_sessions_enrolls ese (cost=0.00..2,923.38 rows=59,738 width=21) (actual time=0.004..39.472 rows=59,738 loops=1)

41. 1.490 6.512 ↑ 1.0 3,084 1

(cost=180.89..180.89 rows=3,084 width=10) (actual time=6.512..6.512 rows=3,084 loops=1)

• Buckets: 4,096 Batches: 1 Memory Usage: 159kB
42. 3.498 5.022 ↑ 1.0 3,084 1

(cost=6.64..180.89 rows=3,084 width=10) (actual time=0.166..5.022 rows=3,084 loops=1)

• Hash Cond: (es.event_id = ev.id)
43. 1.371 1.371 ↑ 1.0 3,084 1

Seq Scan on events_sessions es (cost=0.00..131.84 rows=3,084 width=8) (actual time=0.003..1.371 rows=3,084 loops=1)

44. 0.075 0.153 ↑ 1.0 162 1

(cost=4.62..4.62 rows=162 width=6) (actual time=0.153..0.153 rows=162 loops=1)

• Buckets: 1,024 Batches: 1 Memory Usage: 15kB
45. 0.078 0.078 ↑ 1.0 162 1

Seq Scan on events ev (cost=0.00..4.62 rows=162 width=6) (actual time=0.005..0.078 rows=162 loops=1)

46. 130.301 260.629 ↑ 1.0 228,101 1

(cost=4,834.01..4,834.01 rows=228,101 width=22) (actual time=260.629..260.629 rows=228,101 loops=1)

• Buckets: 262,144 Batches: 1 Memory Usage: 15,414kB
47. 130.328 130.328 ↑ 1.0 228,101 1

Seq Scan on enrolls en (cost=0.00..4,834.01 rows=228,101 width=22) (actual time=0.012..130.328 rows=228,101 loops=1)

48. 61.140 885.426 ↑ 1.2 61,873 1

(cost=15,766.37..15,766.37 rows=74,082 width=297) (actual time=885.426..885.426 rows=61,873 loops=1)

• Buckets: 131,072 Batches: 1 Memory Usage: 16,715kB
49. 64.638 824.286 ↑ 1.2 61,873 1

(cost=11,713.40..15,766.37 rows=74,082 width=297) (actual time=623.109..824.286 rows=61,873 loops=1)

• Hash Cond: (st.center_id = sh.id)
50. 102.618 759.598 ↑ 1.2 61,873 1

(cost=11,711.66..14,746.00 rows=74,082 width=282) (actual time=623.051..759.598 rows=61,873 loops=1)

• Hash Cond: (pd2.unique_name = (cu.mail)::text)
51. 33.994 33.994 ↑ 1.0 71,019 1

Seq Scan on raw_pseudonym_dim pd2 (cost=0.00..2,027.19 rows=71,019 width=58) (actual time=0.005..33.994 rows=71,019 loops=1)

52. 59.851 622.986 ↓ 1.5 61,786 1

(cost=11,205.95..11,205.95 rows=40,456 width=258) (actual time=622.986..622.986 rows=61,786 loops=1)

• Buckets: 65,536 Batches: 1 Memory Usage: 14,984kB
53. 64.375 563.135 ↓ 1.5 61,786 1

(cost=7,949.48..11,205.95 rows=40,456 width=258) (actual time=314.523..563.135 rows=61,786 loops=1)

• Hash Cond: (st.course_id = cr.id)
54. 64.743 498.722 ↓ 1.5 61,786 1

(cost=7,946.97..10,647.17 rows=40,456 width=216) (actual time=314.478..498.722 rows=61,786 loops=1)

• Hash Cond: (st.term_id = tm.id)
55. 67.434 433.956 ↓ 1.5 61,786 1

(cost=7,945.58..10,089.51 rows=40,456 width=201) (actual time=314.447..433.956 rows=61,786 loops=1)

• Hash Cond: (cf.customer_id = cu.id)
56. 37.446 136.254 ↑ 1.0 26,087 1

(cost=2,914.93..4,575.32 rows=26,347 width=32) (actual time=84.127..136.254 rows=26,087 loops=1)

• Hash Cond: ((cf.fid)::text = pd1.sis_user_id)
57. 18.386 18.386 ↑ 1.0 26,087 1

Seq Scan on customers_fids cf (cost=0.00..1,133.45 rows=26,347 width=8) (actual time=3.629..18.386 rows=26,087 loops=1)

• Filter: (data_source_id = 'sis'::text)
• Rows Removed by Filter: 35,267
58. 37.540 80.422 ↑ 1.8 38,501 1

(cost=2,027.19..2,027.19 rows=71,019 width=34) (actual time=80.422..80.422 rows=38,501 loops=1)

• Buckets: 131,072 Batches: 1 Memory Usage: 3,552kB
59. 42.882 42.882 ↑ 1.0 71,019 1

Seq Scan on raw_pseudonym_dim pd1 (cost=0.00..2,027.19 rows=71,019 width=34) (actual time=0.005..42.882 rows=71,019 loops=1)

60. 50.066 230.268 ↓ 1.3 61,357 1

(cost=4,427.52..4,427.52 rows=48,251 width=173) (actual time=230.268..230.268 rows=61,357 loops=1)

• Buckets: 65,536 Batches: 1 Memory Usage: 9,855kB
61. 71.808 180.202 ↓ 1.3 61,357 1

(cost=2,311.35..4,427.52 rows=48,251 width=173) (actual time=71.786..180.202 rows=61,357 loops=1)

• Hash Cond: (st.customer_id = cu.id)
62. 36.643 36.643 ↑ 1.0 61,357 1

Seq Scan on students st (cost=0.00..1,403.57 rows=61,357 width=43) (actual time=0.007..36.643 rows=61,357 loops=1)

63. 19.489 71.751 ↓ 1.0 26,009 1

(cost=1,986.55..1,986.55 rows=25,984 width=130) (actual time=71.751..71.751 rows=26,009 loops=1)

• Buckets: 32,768 Batches: 1 Memory Usage: 3,411kB
64. 29.419 52.262 ↓ 1.0 26,009 1

(cost=1.38..1,986.55 rows=25,984 width=130) (actual time=0.052..52.262 rows=26,009 loops=1)

• Hash Cond: (cu.enabled_status_id = ces.id)
65. 22.820 22.820 ↑ 1.0 33,042 1

Seq Scan on customers cu (cost=0.00..1,601.42 rows=33,042 width=74) (actual time=0.006..22.820 rows=33,042 loops=1)

66. 0.010 0.023 ↑ 1.0 17 1

(cost=1.17..1.17 rows=17 width=56) (actual time=0.023..0.023 rows=17 loops=1)

• Buckets: 1,024 Batches: 1 Memory Usage: 10kB
67. 0.013 0.013 ↑ 1.0 17 1

Seq Scan on customer_enabled_status ces (cost=0.00..1.17 rows=17 width=56) (actual time=0.003..0.013 rows=17 loops=1)

68. 0.015 0.023 ↑ 1.0 17 1

(cost=1.17..1.17 rows=17 width=19) (actual time=0.023..0.023 rows=17 loops=1)

• Buckets: 1,024 Batches: 1 Memory Usage: 9kB
69. 0.008 0.008 ↑ 1.0 17 1

Seq Scan on terms tm (cost=0.00..1.17 rows=17 width=19) (actual time=0.004..0.008 rows=17 loops=1)

70. 0.017 0.038 ↑ 1.0 23 1

(cost=2.23..2.23 rows=23 width=42) (actual time=0.038..0.038 rows=23 loops=1)

• Buckets: 1,024 Batches: 1 Memory Usage: 10kB
71. 0.021 0.021 ↑ 1.0 23 1

Seq Scan on courses cr (cost=0.00..2.23 rows=23 width=42) (actual time=0.007..0.021 rows=23 loops=1)

72. 0.022 0.050 ↑ 1.0 33 1

(cost=1.33..1.33 rows=33 width=19) (actual time=0.050..0.050 rows=33 loops=1)

• Buckets: 1,024 Batches: 1 Memory Usage: 10kB
73. 0.028 0.028 ↑ 1.0 33 1

Seq Scan on shops sh (cost=0.00..1.33 rows=33 width=19) (actual time=0.012..0.028 rows=33 loops=1)

74.

SubPlan (for GroupAggregate)

75. 184.467 1,168.291 ↑ 1.0 1 61,489

Aggregate (cost=91.41..91.42 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=61,489)

76. 82.980 983.824 ↓ 0.0 0 61,489

Nested Loop Anti Join (cost=9.50..91.41 rows=3 width=4) (actual time=0.016..0.016 rows=0 loops=61,489)

• Join Filter: ((od2.customer_id = od.customer_id) AND (ol2.product_id = ol.product_id))
• Rows Removed by Join Filter: 1
77. 109.528 737.868 ↓ 0.0 0 61,489

(cost=4.75..32.27 rows=3 width=12) (actual time=0.012..0.012 rows=0 loops=61,489)

78. 430.423 614.890 ↓ 0.0 0 61,489

Bitmap Heap Scan on orders od (cost=4.33..23.51 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=61,489)

• Recheck Cond: (customer_id = cu.id)
• Filter: (last_order_state_id = 13)
• Rows Removed by Filter: 5
• Heap Blocks: exact=312,168
79. 184.467 184.467 ↑ 1.0 5 61,489

Bitmap Index Scan on orders_customer_id_idx (cost=0.00..4.33 rows=5 width=0) (actual time=0.003..0.003 rows=5 loops=61,489)

• Index Cond: (customer_id = cu.id)
80. 13.450 13.450 ↑ 4.0 1 2,690

Index Scan using orders_lines_order_id_idx on orders_lines ol (cost=0.42..8.72 rows=4 width=12) (actual time=0.004..0.005 rows=1 loops=2,690)

• Index Cond: (order_id = od.id)
81. 35.068 162.976 ↓ 1.2 12 3,704

(cost=4.75..58.61 rows=10 width=8) (actual time=0.009..0.044 rows=12 loops=3,704)

82. 48.344 127.908 ↓ 1.2 12 2,508

(cost=4.75..58.56 rows=10 width=8) (actual time=0.012..0.051 rows=12 loops=2,508)

83. 20.064 27.588 ↓ 1.2 5 2,508

Bitmap Heap Scan on orders od2 (cost=4.33..23.52 rows=4 width=8) (actual time=0.005..0.011 rows=5 loops=2,508)

• Recheck Cond: (customer_id = cu.id)
• Filter: (last_order_state_id = ANY ('{2,4,16,17}'::integer[]))
• Rows Removed by Filter: 1
• Heap Blocks: exact=14,612
84. 7.524 7.524 ↓ 1.6 8 2,508

Bitmap Index Scan on orders_customer_id_idx (cost=0.00..4.33 rows=5 width=0) (actual time=0.003..0.003 rows=8 loops=2,508)

• Index Cond: (customer_id = cu.id)
85. 51.976 51.976 ↑ 2.0 2 12,994

Index Scan using orders_lines_order_id_idx on orders_lines ol2 (cost=0.42..8.72 rows=4 width=8) (actual time=0.003..0.004 rows=2 loops=12,994)

• Index Cond: (order_id = od2.id)
86. 307.445 14,080.981 ↑ 1.0 1 61,489

Aggregate (cost=66.03..66.04 rows=1 width=1) (actual time=0.229..0.229 rows=1 loops=61,489)

87. 860.846 13,773.536 ↓ 1.4 7 61,489

(cost=65.92..65.97 rows=5 width=9) (actual time=0.220..0.224 rows=7 loops=61,489)

• Group Key: fsb.id
88. 1,548.640 12,912.690 ↓ 2.6 13 61,489

(cost=0.71..65.89 rows=5 width=9) (actual time=0.045..0.210 rows=13 loops=61,489)

89. 913.947 9,715.262 ↓ 2.6 13 61,489

(cost=0.29..23.64 rows=5 width=8) (actual time=0.041..0.158 rows=13 loops=61,489)

90. 1,465.625 3,320.406 ↓ 2.0 2 61,489

(cost=0.29..16.67 rows=1 width=8) (actual time=0.028..0.054 rows=2 loops=61,489)

• Join Filter: (fst.term_id = ftm.id)
• Rows Removed by Join Filter: 37
91. 307.445 307.445 ↓ 3.0 3 61,489

Index Scan using students_customer_id_idx on students fst (cost=0.29..15.38 rows=1 width=12) (actual time=0.003..0.005 rows=3 loops=61,489)

• Index Cond: (customer_id = st.customer_id)
• Filter: (course_id = st.course_id)
• Rows Removed by Filter: 0
92. 1,547.336 1,547.336 ↓ 2.0 12 193,417

Seq Scan on terms ftm (cost=0.00..1.21 rows=6 width=4) (actual time=0.001..0.008 rows=12 loops=193,417)

• Filter: (enroll_from_date <= tm.enroll_from_date)
• Rows Removed by Filter: 5
93. 5,480.909 5,480.909 ↓ 1.2 6 127,463

Seq Scan on subjects fsb (cost=0.00..6.93 rows=5 width=8) (actual time=0.010..0.043 rows=6 loops=127,463)

• Filter: (mandatory_for_fct AND (course_id = st.course_id))
• Rows Removed by Filter: 308
94. 1,648.788 1,648.788 ↓ 0.0 0 824,394

Index Scan using enrolls_student_id_subject_id_key on enrolls fen (cost=0.42..8.44 rows=1 width=13) (actual time=0.002..0.002 rows=0 loops=824,394)

• Index Cond: ((student_id = fst.id) AND (subject_id = fsb.id))
Planning time Execution time : 74.536 ms : 79,903.497 ms