explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lpiG

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 1,075.134 ↑ 1.0 18 1

Limit (cost=8,034,097.46..8,034,097.50 rows=18 width=734) (actual time=1,075.125..1,075.134 rows=18 loops=1)

2. 1.016 1,075.126 ↑ 25.7 18 1

Sort (cost=8,034,097.46..8,034,098.61 rows=462 width=734) (actual time=1,075.124..1,075.126 rows=18 loops=1)

  • Sort Key: courses.name
  • Sort Method: top-N heapsort Memory: 42kB
3. 4.691 1,074.110 ↑ 1.4 337 1

Bitmap Heap Scan on courses (cost=44.46..8,034,085.52 rows=462 width=734) (actual time=10.588..1,074.110 rows=337 loops=1)

  • Recheck Cond: ((owner_id IS NULL) OR (owner_id = 32679))
  • Filter: active
  • Rows Removed by Filter: 685
  • Heap Blocks: exact=311
4. 0.000 0.118 ↓ 0.0 0 1

BitmapOr (cost=44.46..44.46 rows=1,022 width=0) (actual time=0.118..0.118 rows=0 loops=1)

5. 0.089 0.089 ↑ 1.0 750 1

Bitmap Index Scan on index_courses_on_owner_id (cost=0.00..29.91 rows=750 width=0) (actual time=0.089..0.089 rows=750 loops=1)

  • Index Cond: (owner_id IS NULL)
6. 0.029 0.029 ↓ 1.0 279 1

Bitmap Index Scan on index_courses_on_owner_id (cost=0.00..14.32 rows=272 width=0) (actual time=0.029..0.029 rows=279 loops=1)

  • Index Cond: (owner_id = 32679)
7.          

SubPlan (forBitmap Heap Scan)

8. 0.674 5.392 ↑ 1.0 1 337

Aggregate (cost=24.14..24.15 rows=1 width=0) (actual time=0.015..0.016 rows=1 loops=337)

9. 4.718 4.718 ↑ 1.0 1 337

Index Scan using index_train_sessions_on_account_id_and_course_id on train_sessions (cost=0.42..24.14 rows=1 width=0) (actual time=0.006..0.014 rows=1 loops=337)

  • Index Cond: ((account_id = 32679) AND (course_id = courses.id))
  • Filter: (((status)::text = 'Assigned'::text) OR ((status)::text = 'Live'::text))
  • Rows Removed by Filter: 19
10. 9.099 87.957 ↑ 1.0 1 337

Aggregate (cost=932.56..932.57 rows=1 width=0) (actual time=0.261..0.261 rows=1 loops=337)

11. 78.858 78.858 ↓ 1.3 4 337

Index Scan using index_train_sessions_on_account_id_and_course_id on train_sessions train_sessions_1 (cost=0.67..932.55 rows=3 width=0) (actual time=0.183..0.234 rows=4 loops=337)

  • Index Cond: ((account_id = ANY ('{35671,35371,35374,32679,35707,35615,35677,35628,35541,35538,35630,35592,35530,35616,35710,35670,35634,35730,35708,35583,35625,35738,35723,35347,35637,35352,36182,35789,35790,35791,35716,36183,35362,35544,35539,35731,35537,35709,35606,35354,35651,35894,35691,35653,35652,35655,35656,35706,35568,35693,35694,35570,35576,35577,35533,35596,35695,35532,35696,35376,35373,35742,36188,35722,35627,35536,35567,35737,35623,35618,36198,36199,35635,35729,35728,35893,35631,35372,35355,35572,35681,35569,35574,35575,35605,35542,35721,35351,35640,35361,35642,35360,35641,35582,35584,35571,35624,35609,35713,35608,35714,35700,35676,35674,35678,35754,35755,35357,35358,35711,35715,35356,35375,35664,35661,35662,35377,36184,35663,35591,35699,35679,35683,35820,35366,35976,35369,36200,35735,35611,35349,35638,35689,35724,35819,35675,35666,35649,35690,35985,35658,35657,35659,36206,36205,35573,35654,35650,35585,35587,35588,35586,35610,35725,35726,35579,35580,35578,36185,35617,35607,35712,35702,35668,35669,35665,35672,35543,35632,35626,35535,35633,36186,35612,36769,36187,35667,35350,35660,35718,35717,35368,35821,35594,35684,35581,35719,35720,35736,35629,35595,35704,35359,35703,35727,35685,35680}'::integer[])) AND (course_id = courses.id))
  • Filter: (((status)::text = 'Assigned'::text) OR ((status)::text = 'Live'::text))
  • Rows Removed by Filter: 41
12. 0.337 3.370 ↑ 1.0 1 337

Aggregate (cost=24.14..24.15 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=337)

13. 3.033 3.033 ↓ 0.0 0 337

Index Scan using index_train_sessions_on_account_id_and_course_id on train_sessions train_sessions_2 (cost=0.42..24.14 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=337)

  • Index Cond: ((account_id = 32679) AND (course_id = courses.id))
  • Filter: (((status)::text = ANY ('{Live,Assigned}'::text[])) AND (complete_by < '2019-07-09'::date))
  • Rows Removed by Filter: 21
14. 8.425 82.902 ↑ 1.0 1 337

Aggregate (cost=932.55..932.56 rows=1 width=0) (actual time=0.245..0.246 rows=1 loops=337)

15. 74.477 74.477 ↓ 0.0 0 337

Index Scan using index_train_sessions_on_account_id_and_course_id on train_sessions train_sessions_3 (cost=0.67..932.55 rows=1 width=0) (actual time=0.221..0.221 rows=0 loops=337)

  • Index Cond: ((account_id = ANY ('{35671,35371,35374,32679,35707,35615,35677,35628,35541,35538,35630,35592,35530,35616,35710,35670,35634,35730,35708,35583,35625,35738,35723,35347,35637,35352,36182,35789,35790,35791,35716,36183,35362,35544,35539,35731,35537,35709,35606,35354,35651,35894,35691,35653,35652,35655,35656,35706,35568,35693,35694,35570,35576,35577,35533,35596,35695,35532,35696,35376,35373,35742,36188,35722,35627,35536,35567,35737,35623,35618,36198,36199,35635,35729,35728,35893,35631,35372,35355,35572,35681,35569,35574,35575,35605,35542,35721,35351,35640,35361,35642,35360,35641,35582,35584,35571,35624,35609,35713,35608,35714,35700,35676,35674,35678,35754,35755,35357,35358,35711,35715,35356,35375,35664,35661,35662,35377,36184,35663,35591,35699,35679,35683,35820,35366,35976,35369,36200,35735,35611,35349,35638,35689,35724,35819,35675,35666,35649,35690,35985,35658,35657,35659,36206,36205,35573,35654,35650,35585,35587,35588,35586,35610,35725,35726,35579,35580,35578,36185,35617,35607,35712,35702,35668,35669,35665,35672,35543,35632,35626,35535,35633,36186,35612,36769,36187,35667,35350,35660,35718,35717,35368,35821,35594,35684,35581,35719,35720,35736,35629,35595,35704,35359,35703,35727,35685,35680}'::integer[])) AND (course_id = courses.id))
  • Filter: (((status)::text = ANY ('{Live,Assigned}'::text[])) AND (complete_by < '2019-07-09'::date))
  • Rows Removed by Filter: 46
16. 1.685 7.077 ↑ 1.0 1 337

Aggregate (cost=24.14..24.15 rows=1 width=0) (actual time=0.021..0.021 rows=1 loops=337)

17. 5.392 5.392 ↓ 18.0 18 337

Index Scan using index_train_sessions_on_account_id_and_course_id on train_sessions train_sessions_4 (cost=0.42..24.14 rows=1 width=0) (actual time=0.003..0.016 rows=18 loops=337)

  • Index Cond: ((account_id = 32679) AND (course_id = courses.id))
  • Filter: ((failed_attempts < final_quiz_tries) AND ((status)::text = 'Completed'::text))
  • Rows Removed by Filter: 3
18. 10.784 90.990 ↑ 1.0 1 337

Aggregate (cost=932.56..932.57 rows=1 width=0) (actual time=0.270..0.270 rows=1 loops=337)

19. 80.206 80.206 ↓ 7.6 38 337

Index Scan using index_train_sessions_on_account_id_and_course_id on train_sessions train_sessions_5 (cost=0.67..932.55 rows=5 width=0) (actual time=0.097..0.238 rows=38 loops=337)

  • Index Cond: ((account_id = ANY ('{35671,35371,35374,32679,35707,35615,35677,35628,35541,35538,35630,35592,35530,35616,35710,35670,35634,35730,35708,35583,35625,35738,35723,35347,35637,35352,36182,35789,35790,35791,35716,36183,35362,35544,35539,35731,35537,35709,35606,35354,35651,35894,35691,35653,35652,35655,35656,35706,35568,35693,35694,35570,35576,35577,35533,35596,35695,35532,35696,35376,35373,35742,36188,35722,35627,35536,35567,35737,35623,35618,36198,36199,35635,35729,35728,35893,35631,35372,35355,35572,35681,35569,35574,35575,35605,35542,35721,35351,35640,35361,35642,35360,35641,35582,35584,35571,35624,35609,35713,35608,35714,35700,35676,35674,35678,35754,35755,35357,35358,35711,35715,35356,35375,35664,35661,35662,35377,36184,35663,35591,35699,35679,35683,35820,35366,35976,35369,36200,35735,35611,35349,35638,35689,35724,35819,35675,35666,35649,35690,35985,35658,35657,35659,36206,36205,35573,35654,35650,35585,35587,35588,35586,35610,35725,35726,35579,35580,35578,36185,35617,35607,35712,35702,35668,35669,35665,35672,35543,35632,35626,35535,35633,36186,35612,36769,36187,35667,35350,35660,35718,35717,35368,35821,35594,35684,35581,35719,35720,35736,35629,35595,35704,35359,35703,35727,35685,35680}'::integer[])) AND (course_id = courses.id))
  • Filter: ((failed_attempts < final_quiz_tries) AND ((status)::text = 'Completed'::text))
  • Rows Removed by Filter: 8
20. 0.337 1.011 ↑ 1.0 1 337

Aggregate (cost=8.45..8.46 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=337)

21. 0.674 0.674 ↓ 0.0 0 337

Index Scan using index_units_on_account_id_and_course_id on units (cost=0.42..8.45 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=337)

  • Index Cond: ((account_id = 32679) AND (course_id = courses.id))
  • Filter: ((status)::text = 'Active'::text)
22. 0.674 0.674 ↓ 0.0 0 337

Index Scan using index_course_preferences_on_account_id_and_course_id on course_preferences (cost=0.29..8.30 rows=1 width=1) (actual time=0.002..0.002 rows=0 loops=337)

  • Index Cond: ((account_id = 32679) AND (course_id = courses.id))
23. 1.685 159.401 ↑ 1.0 1 337

Aggregate (cost=5,677.61..5,677.62 rows=1 width=4) (actual time=0.473..0.473 rows=1 loops=337)

24. 4.331 157.716 ↓ 6.0 6 337

Nested Loop (cost=3,414.50..5,677.61 rows=1 width=4) (actual time=0.390..0.468 rows=6 loops=337)

  • Join Filter: (train_sessions_6.assignee_login_id = users.login_id)
25. 52.241 142.551 ↓ 8.0 16 337

Hash Join (cost=3,414.08..5,663.35 rows=2 width=8) (actual time=0.384..0.423 rows=16 loops=337)

  • Hash Cond: (train_sessions_6.assignee_login_id = logins.id)
26. 86.946 86.946 ↑ 1.8 374 337

Index Scan using index_train_sessions_on_course_id on train_sessions train_sessions_6 (cost=0.42..2,247.16 rows=671 width=4) (actual time=0.006..0.258 rows=374 loops=337)

  • Index Cond: (course_id = courses.id)
27. 0.383 3.364 ↓ 1.2 1,193 1

Hash (cost=3,401.54..3,401.54 rows=969 width=4) (actual time=3.364..3.364 rows=1,193 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
28. 2.811 2.981 ↓ 1.2 1,193 1

Bitmap Heap Scan on logins (cost=28.92..3,401.54 rows=969 width=4) (actual time=0.267..2.981 rows=1,193 loops=1)

  • Recheck Cond: (account_id = 32679)
  • Filter: (role_id = 11)
  • Rows Removed by Filter: 10
  • Heap Blocks: exact=689
29. 0.170 0.170 ↓ 1.1 1,205 1

Bitmap Index Scan on "index_ole_core.logins_on_account_id" (cost=0.00..28.68 rows=1,101 width=0) (actual time=0.170..0.170 rows=1,205 loops=1)

  • Index Cond: (account_id = 32679)
30. 10.834 10.834 ↓ 0.0 0 5,417

Index Scan using index_users_on_login_id on users (cost=0.42..7.12 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=5,417)

  • Index Cond: (login_id = logins.id)
  • Filter: ((deleted_at IS NULL) AND ((status)::text = 'Active'::text))
  • Rows Removed by Filter: 1
31. 3.033 630.527 ↑ 1.0 1 337

Aggregate (cost=8,823.95..8,823.96 rows=1 width=4) (actual time=1.871..1.871 rows=1 loops=337)

32. 11.713 627.494 ↓ 17.0 17 337

Nested Loop (cost=1,379.52..8,823.95 rows=1 width=4) (actual time=1.522..1.862 rows=17 loops=337)

33. 9.149 585.369 ↓ 45.0 45 337

Hash Semi Join (cost=1,379.09..8,819.97 rows=1 width=8) (actual time=1.513..1.737 rows=45 loops=337)

  • Hash Cond: (logins_1.account_id = "ANY_subquery".id)
34. 127.978 574.922 ↓ 4.5 45 337

Nested Loop (cost=0.85..7,441.70 rows=10 width=12) (actual time=1.508..1.706 rows=45 loops=337)

35. 69.085 69.085 ↑ 1.8 374 337

Index Scan using index_train_sessions_on_course_id on train_sessions train_sessions_7 (cost=0.42..2,247.16 rows=671 width=4) (actual time=0.004..0.205 rows=374 loops=337)

  • Index Cond: (course_id = courses.id)
36. 377.859 377.859 ↓ 0.0 0 125,953

Index Scan using logins_pkey on logins logins_1 (cost=0.42..7.73 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=125,953)

  • Index Cond: (id = train_sessions_7.assignee_login_id)
  • Filter: ((role_id = 11) AND (account_id = ANY ('{35671,35371,35374,32679,35707,35615,35677,35628,35541,35538,35630,35592,35530,35616,35710,35670,35634,35730,35708,35583,35625,35738,35723,35347,35637,35352,36182,35789,35790,35791,35716,36183,35362,35544,35539,35731,35537,35709,35606,35354,35651,35894,35691,35653,35652,35655,35656,35706,35568,35693,35694,35570,35576,35577,35533,35596,35695,35532,35696,35376,35373,35742,36188,35722,35627,35536,35567,35737,35623,35618,36198,36199,35635,35729,35728,35893,35631,35372,35355,35572,35681,35569,35574,35575,35605,35542,35721,35351,35640,35361,35642,35360,35641,35582,35584,35571,35624,35609,35713,35608,35714,35700,35676,35674,35678,35754,35755,35357,35358,35711,35715,35356,35375,35664,35661,35662,35377,36184,35663,35591,35699,35679,35683,35820,35366,35976,35369,36200,35735,35611,35349,35638,35689,35724,35819,35675,35666,35649,35690,35985,35658,35657,35659,36206,36205,35573,35654,35650,35585,35587,35588,35586,35610,35725,35726,35579,35580,35578,36185,35617,35607,35712,35702,35668,35669,35665,35672,35543,35632,35626,35535,35633,36186,35612,36769,36187,35667,35350,35660,35718,35717,35368,35821,35594,35684,35581,35719,35720,35736,35629,35595,35704,35359,35703,35727,35685,35680}'::integer[])))
  • Rows Removed by Filter: 1
37. 0.059 1.298 ↓ 1.0 197 1

Hash (cost=1,375.80..1,375.80 rows=196 width=4) (actual time=1.298..1.298 rows=197 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
38. 0.084 1.239 ↓ 1.0 197 1

Subquery Scan on ANY_subquery (cost=1,373.35..1,375.80 rows=196 width=4) (actual time=1.116..1.239 rows=197 loops=1)

39. 0.480 1.155 ↓ 1.0 197 1

Sort (cost=1,373.35..1,373.84 rows=196 width=26) (actual time=1.116..1.155 rows=197 loops=1)

  • Sort Key: accounts.name, account_hierarchies.generations
  • Sort Method: quicksort Memory: 40kB
40. 0.184 0.675 ↓ 1.0 197 1

Nested Loop (cost=0.58..1,365.88 rows=196 width=26) (actual time=0.020..0.675 rows=197 loops=1)

41. 0.097 0.097 ↓ 1.0 197 1

Index Scan using index_account_hierarchies_on_ancestor_id on account_hierarchies (cost=0.29..175.16 rows=196 width=8) (actual time=0.007..0.097 rows=197 loops=1)

  • Index Cond: (ancestor_id = 32679)
42. 0.394 0.394 ↑ 1.0 1 197

Index Scan using accounts_pkey on accounts (cost=0.29..6.07 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=197)

  • Index Cond: (id = account_hierarchies.descendant_id)
43. 30.412 30.412 ↓ 0.0 0 15,206

Index Scan using index_users_on_login_id on users users_1 (cost=0.42..3.96 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=15,206)

  • Index Cond: (login_id = logins_1.id)
  • Filter: ((deleted_at IS NULL) AND ((status)::text = 'Active'::text))
  • Rows Removed by Filter: 1