explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Me6H : Optimization for: Optimization for: Optimization for: plan #ZN9x; plan #wgFb; plan #f8oq

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 9,028.685 55,618.988 ↓ 6.1 18,664 1

GroupAggregate (cost=414,442.27..897,800.23 rows=3,067 width=376) (actual time=37,894.471..55,618.988 rows=18,664 loops=1)

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

CTE fd

3. 0.219 2.307 ↑ 1.0 1 1

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

4. 2.088 2.088 ↑ 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.088 rows=297 loops=1)

5.          

Initplan (for GroupAggregate)

6. 2.314 2.314 ↑ 1.0 1 1

CTE Scan on fd (cost=0.00..0.02 rows=1 width=8) (actual time=2.312..2.314 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.001 0.001 ↑ 1.0 1 1

CTE Scan on fd fd_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 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.001..0.001 rows=1 loops=1)

12. 0.001 0.001 ↑ 1.0 1 1

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

13. 0.000 0.000 ↑ 1.0 1 1

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

14. 13,890.060 41,996.638 ↓ 1,151.8 3,532,717 1

Sort (cost=412,132.38..412,140.05 rows=3,067 width=376) (actual time=37,893.963..41,996.638 rows=3,532,717 loops=1)

  • Sort Key: cu.id, ces.id, cf.fid, st.id, tm.id, cr.id, sh.id
  • Sort Method: external merge Disk: 1115448kB
15. 6,249.282 28,106.578 ↓ 1,151.8 3,532,717 1

Merge Join (cost=399,225.10..411,954.76 rows=3,067 width=376) (actual time=17,344.320..28,106.578 rows=3,532,717 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)))
  • Join Filter: ((st.updated_at > $9) OR (pd1.updated_at > $10) OR (pd2.updated_at > $11) OR (en.updated_at > $12) OR (ese.updated_at > $13) OR (sbd.created_at > $14) OR (asd.created_at > $15) OR (crd.created_at > $16))
  • Rows Removed by Join Filter: 3617
16. 5,281.098 16,187.413 ↓ 3.8 1,223,617 1

Sort (cost=299,207.09..300,011.74 rows=321,861 width=84) (actual time=13,951.980..16,187.413 rows=1,223,617 loops=1)

  • Sort Key: sbd.user_id, acd.sis_id, tmd.sis_id
  • Sort Method: external merge Disk: 108272kB
17. 4,712.497 10,906.315 ↓ 3.8 1,223,617 1

Hash Join (cost=109,507.06..254,359.62 rows=321,861 width=84) (actual time=3,799.963..10,906.315 rows=1,223,617 loops=1)

  • Hash Cond: (sbf.submission_id = sbd.id)
18. 2,394.104 2,394.104 ↑ 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..2,394.104 rows=4,873,887 loops=1)

19. 914.801 3,799.714 ↓ 3.8 1,223,617 1

Hash (cost=101,082.80..101,082.80 rows=321,861 width=84) (actual time=3,799.714..3,799.714 rows=1,223,617 loops=1)

  • Buckets: 262144 (originally 262144) Batches: 8 (originally 2) Memory Usage: 30721kB
20. 1,120.015 2,884.913 ↓ 3.8 1,223,617 1

Nested Loop (cost=217.19..101,082.80 rows=321,861 width=84) (actual time=6.872..2,884.913 rows=1,223,617 loops=1)

21. 10.926 55.861 ↓ 2.4 9,339 1

Hash Join (cost=216.76..3,373.58 rows=3,870 width=42) (actual time=6.854..55.861 rows=9,339 loops=1)

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

Hash Join (cost=131.75..3,227.50 rows=5,966 width=32) (actual time=2.601..40.688 rows=9,339 loops=1)

  • Hash Cond: (asd.assignment_group_id = agf.assignment_group_id)
23. 23.709 23.709 ↓ 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.006..23.709 rows=16,126 loops=1)

  • Filter: (status = 'published'::text)
  • Rows Removed by Filter: 28668
24. 0.990 2.589 ↑ 1.0 2,163 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 117kB
25. 1.599 1.599 ↑ 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.007..1.599 rows=2,163 loops=1)

  • Filter: (group_weight > '0'::double precision)
  • Rows Removed by Filter: 3654
26. 0.733 4.247 ↓ 1.4 1,313 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 88kB
27. 1.275 3.514 ↓ 1.4 1,313 1

Hash Join (cost=3.73..73.63 rows=910 width=26) (actual time=0.109..3.514 rows=1,313 loops=1)

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

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

  • Hash Cond: (crd.account_id = acd.id)
29. 0.755 0.755 ↑ 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.005..0.755 rows=1,403 loops=1)

30. 0.029 0.075 ↑ 1.0 48 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
31. 0.046 0.046 ↑ 1.0 48 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.010 0.010 ↑ 1.0 9 1

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

34. 1,709.037 1,709.037 ↑ 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.008..0.183 rows=131 loops=9,339)

  • Index Cond: (assignment_id = asd.id)
  • Filter: (status <> 'deleted'::text)
  • Rows Removed by Filter: 7
35. 1,718.199 5,669.883 ↓ 13.3 3,660,699 1

Materialize (cost=100,018.02..101,395.06 rows=275,408 width=334) (actual time=3,389.990..5,669.883 rows=3,660,699 loops=1)

36. 2,039.579 3,951.684 ↑ 1.4 203,951 1

Sort (cost=100,018.02..100,706.54 rows=275,408 width=334) (actual time=3,389.984..3,951.684 rows=203,951 loops=1)

  • Sort Key: (COALESCE(pd1.user_id, pd2.user_id)), ((cr.sis_fid)::text), ((tm.sis_fid)::text)
  • Sort Method: external merge Disk: 68464kB
37. 466.677 1,912.105 ↑ 1.1 247,478 1

Hash Right Join (cost=24,597.10..32,772.74 rows=275,408 width=334) (actual time=1,138.773..1,912.105 rows=247,478 loops=1)

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

Hash Right Join (cost=7,904.71..12,470.89 rows=228,101 width=41) (actual time=260.679..567.409 rows=245,387 loops=1)

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

Hash Join (cost=219.44..3,964.22 rows=59,738 width=23) (actual time=6.577..105.554 rows=59,738 loops=1)

  • Hash Cond: (ese.event_session_id = es.id)
40. 39.383 39.383 ↑ 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.005..39.383 rows=59,738 loops=1)

41. 1.732 6.552 ↑ 1.0 3,084 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 159kB
42. 3.122 4.820 ↑ 1.0 3,084 1

Hash Join (cost=6.64..180.89 rows=3,084 width=10) (actual time=0.189..4.820 rows=3,084 loops=1)

  • Hash Cond: (es.event_id = ev.id)
43. 1.523 1.523 ↑ 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.523 rows=3,084 loops=1)

44. 0.087 0.175 ↑ 1.0 162 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
45. 0.088 0.088 ↑ 1.0 162 1

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

46. 127.084 253.966 ↑ 1.0 228,101 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 15414kB
47. 126.882 126.882 ↑ 1.0 228,101 1

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

48. 61.069 878.019 ↑ 1.2 61,873 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 16715kB
49. 64.927 816.950 ↑ 1.2 61,873 1

Hash Join (cost=11,713.40..15,766.37 rows=74,082 width=297) (actual time=613.491..816.950 rows=61,873 loops=1)

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

Hash Right Join (cost=11,711.66..14,746.00 rows=74,082 width=282) (actual time=613.439..751.979 rows=61,873 loops=1)

  • Hash Cond: (pd2.unique_name = (cu.mail)::text)
51. 34.593 34.593 ↑ 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..34.593 rows=71,019 loops=1)

52. 60.317 613.364 ↓ 1.5 61,786 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 14984kB
53. 64.350 553.047 ↓ 1.5 61,786 1

Hash Join (cost=7,949.48..11,205.95 rows=40,456 width=258) (actual time=304.767..553.047 rows=61,786 loops=1)

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

Hash Join (cost=7,946.97..10,647.17 rows=40,456 width=216) (actual time=304.726..488.664 rows=61,786 loops=1)

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

Hash Join (cost=7,945.58..10,089.51 rows=40,456 width=201) (actual time=304.699..424.027 rows=61,786 loops=1)

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

Hash Left Join (cost=2,914.93..4,575.32 rows=26,347 width=32) (actual time=82.377..134.480 rows=26,087 loops=1)

  • Hash Cond: ((cf.fid)::text = pd1.sis_user_id)
57. 17.987 17.987 ↑ 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.432..17.987 rows=26,087 loops=1)

  • Filter: (data_source_id = 'sis'::text)
  • Rows Removed by Filter: 35267
58. 36.308 78.872 ↑ 1.8 38,501 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 3552kB
59. 42.564 42.564 ↑ 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.006..42.564 rows=71,019 loops=1)

60. 47.835 222.274 ↓ 1.3 61,357 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 9855kB
61. 68.435 174.439 ↓ 1.3 61,357 1

Hash Join (cost=2,311.35..4,427.52 rows=48,251 width=173) (actual time=70.571..174.439 rows=61,357 loops=1)

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

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

63. 18.697 70.538 ↓ 1.0 26,009 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 3411kB
64. 28.980 51.841 ↓ 1.0 26,009 1

Hash Join (cost=1.38..1,986.55 rows=25,984 width=130) (actual time=0.055..51.841 rows=26,009 loops=1)

  • Hash Cond: (cu.enabled_status_id = ces.id)
65. 22.838 22.838 ↑ 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.838 rows=33,042 loops=1)

66. 0.015 0.023 ↑ 1.0 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
67. 0.008 0.008 ↑ 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.008 rows=17 loops=1)

68. 0.010 0.021 ↑ 1.0 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
69. 0.011 0.011 ↑ 1.0 17 1

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

70. 0.017 0.033 ↑ 1.0 23 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
71. 0.016 0.016 ↑ 1.0 23 1

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

72. 0.023 0.044 ↑ 1.0 33 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
73. 0.021 0.021 ↑ 1.0 33 1

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

74.          

SubPlan (for GroupAggregate)

75. 55.992 391.944 ↑ 1.0 1 18,664

Aggregate (cost=91.41..91.42 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=18,664)

76. 25.376 335.952 ↓ 0.0 0 18,664

Nested Loop Anti Join (cost=9.50..91.41 rows=3 width=4) (actual time=0.018..0.018 rows=0 loops=18,664)

  • Join Filter: ((od2.customer_id = od.customer_id) AND (ol2.product_id = ol.product_id))
  • Rows Removed by Join Filter: 1
77. 31.574 261.296 ↓ 0.0 0 18,664

Nested Loop (cost=4.75..32.27 rows=3 width=12) (actual time=0.014..0.014 rows=0 loops=18,664)

78. 149.312 223.968 ↓ 0.0 0 18,664

Bitmap Heap Scan on orders od (cost=4.33..23.51 rows=1 width=8) (actual time=0.011..0.012 rows=0 loops=18,664)

  • Recheck Cond: (customer_id = cu.id)
  • Filter: (last_order_state_id = 13)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=79609
79. 74.656 74.656 ↑ 1.0 5 18,664

Bitmap Index Scan on orders_customer_id_idx (cost=0.00..4.33 rows=5 width=0) (actual time=0.004..0.004 rows=5 loops=18,664)

  • Index Cond: (customer_id = cu.id)
80. 5.754 5.754 ↑ 4.0 1 822

Index Scan using orders_lines_order_id_idx on orders_lines ol (cost=0.42..8.72 rows=4 width=12) (actual time=0.006..0.007 rows=1 loops=822)

  • Index Cond: (order_id = od.id)
81. 10.826 49.280 ↑ 1.0 10 1,232

Materialize (cost=4.75..58.61 rows=10 width=8) (actual time=0.010..0.040 rows=10 loops=1,232)

82. 12.261 38.454 ↓ 1.1 11 754

Nested Loop (cost=4.75..58.56 rows=10 width=8) (actual time=0.014..0.051 rows=11 loops=754)

83. 6.786 9.048 ↓ 1.2 5 754

Bitmap Heap Scan on orders od2 (cost=4.33..23.52 rows=4 width=8) (actual time=0.006..0.012 rows=5 loops=754)

  • 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=3831
84. 2.262 2.262 ↓ 1.6 8 754

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=754)

  • Index Cond: (customer_id = cu.id)
85. 17.145 17.145 ↑ 2.0 2 3,429

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

  • Index Cond: (order_id = od2.id)
86. 93.320 4,199.400 ↑ 1.0 1 18,664

Aggregate (cost=66.03..66.04 rows=1 width=1) (actual time=0.225..0.225 rows=1 loops=18,664)

87. 261.296 4,106.080 ↓ 1.4 7 18,664

HashAggregate (cost=65.92..65.97 rows=5 width=9) (actual time=0.216..0.220 rows=7 loops=18,664)

  • Group Key: fsb.id
88. 477.562 3,844.784 ↓ 2.6 13 18,664

Nested Loop Left Join (cost=0.71..65.89 rows=5 width=9) (actual time=0.047..0.206 rows=13 loops=18,664)

89. 287.313 2,874.256 ↓ 2.6 13 18,664

Nested Loop (cost=0.29..23.64 rows=5 width=8) (actual time=0.043..0.154 rows=13 loops=18,664)

90. 452.001 989.192 ↓ 2.0 2 18,664

Nested Loop (cost=0.29..16.67 rows=1 width=8) (actual time=0.029..0.053 rows=2 loops=18,664)

  • Join Filter: (fst.term_id = ftm.id)
  • Rows Removed by Join Filter: 38
91. 93.320 93.320 ↓ 3.0 3 18,664

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=18,664)

  • Index Cond: (customer_id = st.customer_id)
  • Filter: (course_id = st.course_id)
  • Rows Removed by Filter: 0
92. 443.871 443.871 ↓ 2.5 15 49,319

Seq Scan on terms ftm (cost=0.00..1.21 rows=6 width=4) (actual time=0.001..0.009 rows=15 loops=49,319)

  • Filter: (enroll_from_date <= tm.enroll_from_date)
  • Rows Removed by Filter: 2
93. 1,597.751 1,597.751 ↓ 1.4 7 37,157

Seq Scan on subjects fsb (cost=0.00..6.93 rows=5 width=8) (actual time=0.010..0.043 rows=7 loops=37,157)

  • Filter: (mandatory_for_fct AND (course_id = st.course_id))
  • Rows Removed by Filter: 307
94. 492.966 492.966 ↓ 0.0 0 246,483

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=246,483)

  • Index Cond: ((student_id = fst.id) AND (subject_id = fsb.id))
Planning time : 8.636 ms
Execution time : 55,778.823 ms