explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LvOZ

Settings
# exclusive inclusive rows x rows loops node
1. 1.796 91,303.449 ↑ 1.1 321 1

Sort (cost=12,867,345.80..12,867,346.67 rows=348 width=31) (actual time=91,303.386..91,303.449 rows=321 loops=1)

  • Sort Key: courses.name
  • Sort Method: quicksort Memory: 63kB
2. 4.283 91,301.653 ↑ 1.1 321 1

Bitmap Heap Scan on courses (cost=44.40..12,867,331.11 rows=348 width=31) (actual time=1,427.063..91,301.653 rows=321 loops=1)

  • Recheck Cond: ((owner_id IS NULL) OR (owner_id = 32679))
  • Filter: (active AND (((available_for_subscription OR (available_for_subscription IS NULL)) AND (owner_id IS NULL) AND ((NOT private) OR (private IS NULL))) OR (owner_id = 32679)))
  • Rows Removed by Filter: 701
  • Heap Blocks: exact=311
3. 0.001 0.155 ↓ 0.0 0 1

BitmapOr (cost=44.40..44.40 rows=1,022 width=0) (actual time=0.155..0.155 rows=0 loops=1)

4. 0.111 0.111 ↑ 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.111..0.111 rows=750 loops=1)

  • Index Cond: (owner_id IS NULL)
5. 0.043 0.043 ↓ 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.043..0.043 rows=279 loops=1)

  • Index Cond: (owner_id = 32679)
6.          

SubPlan (forBitmap Heap Scan)

7. 29.853 2,522.739 ↑ 1.0 1 321

Aggregate (cost=11,163.07..11,163.08 rows=1 width=0) (actual time=7.858..7.859 rows=1 loops=321)

8. 670.569 2,492.886 ↑ 2.0 338 321

Nested Loop Anti Join (cost=29.80..11,161.42 rows=660 width=0) (actual time=0.307..7.766 rows=338 loops=321)

  • Join Filter: (train_sessions.assignee_login_id = logins.id)
  • Rows Removed by Join Filter: 6135
9. 261.936 1,380.621 ↑ 1.9 344 321

Nested Loop (cost=29.38..11,087.78 rows=660 width=4) (actual time=0.258..4.301 rows=344 loops=321)

10. 299.814 347.001 ↓ 1.1 1,202 321

Bitmap Heap Scan on logins (cost=28.96..3,398.82 rows=1,101 width=4) (actual time=0.222..1.081 rows=1,202 loops=321)

  • Recheck Cond: (account_id = 32679)
  • Heap Blocks: exact=220848
11. 47.187 47.187 ↓ 1.1 1,204 321

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

  • Index Cond: (account_id = 32679)
12. 771.684 771.684 ↓ 0.0 0 385,842

Index Scan using index_users_on_login_id on users (cost=0.42..6.97 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=385,842)

  • Index Cond: (login_id = logins.id)
  • Filter: ((deleted_at IS NULL) AND ((status)::text = 'Active'::text))
  • Rows Removed by Filter: 1
13. 433.029 441.696 ↓ 3.6 18 110,424

Materialize (cost=0.42..24.14 rows=5 width=4) (actual time=0.000..0.004 rows=18 loops=110,424)

14. 8.667 8.667 ↓ 4.4 22 321

Index Scan using index_train_sessions_on_account_id_and_course_id on train_sessions (cost=0.42..24.11 rows=5 width=4) (actual time=0.008..0.027 rows=22 loops=321)

  • Index Cond: ((account_id = 32679) AND (course_id = courses.id))
15. 87.312 88,774.476 ↑ 1.0 1 321

Aggregate (cost=25,810.31..25,810.32 rows=1 width=0) (actual time=276.556..276.556 rows=1 loops=321)

16. 174.303 88,687.164 ↑ 5.5 912 321

Hash Anti Join (cost=2,311.35..25,797.81 rows=4,997 width=0) (actual time=3.803..276.284 rows=912 loops=321)

  • Hash Cond: (logins_1.id = train_sessions_1.assignee_login_id)
17. 1,015.002 88,398.906 ↑ 5.4 928 321

Nested Loop (cost=1,378.67..24,802.05 rows=4,997 width=4) (actual time=3.437..275.386 rows=928 loops=321)

18. 49,835.805 85,487.436 ↑ 2.8 2,954 321

Hash Semi Join (cost=1,378.25..20,488.15 rows=8,340 width=4) (actual time=0.316..266.316 rows=2,954 loops=321)

  • Hash Cond: (logins_1.account_id = "ANY_subquery".id)
19. 35,637.420 35,637.420 ↓ 1.0 413,959 321

Seq Scan on logins logins_1 (cost=0.00..17,933.62 rows=412,762 width=8) (actual time=0.002..111.020 rows=413,959 loops=321)

20. 0.113 14.211 ↓ 1.0 197 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
21. 0.145 14.098 ↓ 1.0 197 1

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

22. 12.897 13.953 ↓ 1.0 197 1

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

  • Sort Key: accounts.name, account_hierarchies.generations
  • Sort Method: quicksort Memory: 40kB
23. 0.314 1.056 ↓ 1.0 197 1

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

24. 0.151 0.151 ↓ 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.012..0.151 rows=197 loops=1)

  • Index Cond: (ancestor_id = 32679)
25. 0.591 0.591 ↑ 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.003 rows=1 loops=197)

  • Index Cond: (id = account_hierarchies.descendant_id)
26. 1,896.468 1,896.468 ↓ 0.0 0 948,234

Index Scan using index_users_on_login_id on users users_1 (cost=0.42..0.51 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=948,234)

  • Index Cond: (login_id = logins_1.id)
  • Filter: ((deleted_at IS NULL) AND ((status)::text = 'Active'::text))
  • Rows Removed by Filter: 1
27. 16.050 113.955 ↓ 2.8 48 321

Hash (cost=932.47..932.47 rows=17 width=4) (actual time=0.355..0.355 rows=48 loops=321)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
28. 97.905 97.905 ↓ 2.8 48 321

Index Scan using index_train_sessions_on_account_id_and_course_id on train_sessions train_sessions_1 (cost=0.67..932.47 rows=17 width=4) (actual time=0.108..0.305 rows=48 loops=321)

  • 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))
Planning time : 2.605 ms
Execution time : 91,303.676 ms