explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 433L

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 375.234 ↑ 1.0 1 1

Index Scan using domains_pkey on domains (cost=0.42..49,969.46 rows=1 width=878) (actual time=375.233..375.234 rows=1 loops=1)

  • Index Cond: (id = 5)
  • Filter: ((deleted_at IS NULL) AND (parent_id IS NOT NULL) AND (deleted_at IS NULL) AND active AND (domain_type <> 1) AND (domain_type <> 1))
  • Buffers: shared hit=22506
2.          

SubPlan (forIndex Scan)

3. 0.014 0.014 ↑ 1.0 1 1

Index Scan using index_affiliated_sub_accounts_on_item_and_domain_id on affiliated_sub_accounts a (cost=0.43..2.65 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: ((item_id = 1) AND ((item_type)::text = 'CourseTemplate'::text) AND (domain_id = domains.id))
  • Filter: (state = 1)
  • Buffers: shared hit=4
4. 0.000 0.000 ↓ 0.0 0

Index Scan using index_affiliated_sub_accounts_on_item_and_domain_id on affiliated_sub_accounts a_1 (cost=0.43..37,015.58 rows=530,416 width=8) (never executed)

  • Index Cond: ((item_id = 1) AND ((item_type)::text = 'CourseTemplate'::text))
  • Filter: (state = 1)
5. 0.008 0.027 ↓ 0.0 0 1

Nested Loop (cost=0.85..86.48 rows=7 width=0) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=31
6. 0.010 0.010 ↑ 3.4 9 1

Index Scan using index_users_on_sub_account_id on users u (cost=0.43..4.17 rows=31 width=8) (actual time=0.007..0.010 rows=9 loops=1)

  • Index Cond: (sub_account_id = domains.id)
  • Buffers: shared hit=4
7. 0.009 0.009 ↓ 0.0 0 9

Index Scan using index_enrollments_on_user_id on enrollments e (cost=0.42..2.65 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=9)

  • Index Cond: (user_id = u.id)
  • Filter: ((deleted_at IS NULL) AND ((inactive IS NULL) OR (NOT inactive)) AND (course_template_id = 1))
  • Buffers: shared hit=27
8. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=13.07..130,778.98 rows=685,260 width=8) (never executed)

  • Merge Cond: (e_1.user_id = u_1.id)
9. 0.000 0.000 ↓ 0.0 0

Index Scan using index_enrollments_on_user_id on enrollments e_1 (cost=0.42..31,891.44 rows=685,260 width=8) (never executed)

  • Filter: ((deleted_at IS NULL) AND ((inactive IS NULL) OR (NOT inactive)) AND (course_template_id = 1))
10. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on users u_1 (cost=0.43..212,909.35 rows=2,864,540 width=16) (never executed)

11. 0.006 375.150 ↓ 0.0 0 1

Nested Loop (cost=0.42..49,948.42 rows=1 width=0) (actual time=375.150..375.150 rows=0 loops=1)

  • Buffers: shared hit=22463
12. 375.123 375.123 ↑ 1.0 1 1

Seq Scan on affiliated_sub_accounts a_2 (cost=0.00..49,945.76 rows=1 width=8) (actual time=375.111..375.123 rows=1 loops=1)

  • Filter: (((item_type)::text = 'CourseTemplate'::text) AND (item_id = 1) AND (domain_id = domains.id))
  • Rows Removed by Filter: 1570476
  • Buffers: shared hit=22459
13. 0.021 0.021 ↓ 0.0 0 1

Index Scan using index_affiliated_sub_account_sources_on_item_and_affiliation on affiliated_sub_account_sources s (cost=0.42..2.65 rows=1 width=8) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (affiliated_sub_account_id = a_2.id)
  • Filter: (((item_type)::text <> 'CourseTemplate'::text) OR (item_id <> 1))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4
14. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=52,649.28..86,661.20 rows=353,456 width=8) (never executed)

  • Hash Cond: (s_1.affiliated_sub_account_id = a_3.id)
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on affiliated_sub_account_sources s_1 (cost=0.00..26,552.39 rows=1,046,658 width=8) (never executed)

  • Filter: ((deleted_at IS NULL) AND (((item_type)::text <> 'CourseTemplate'::text) OR (item_id <> 1)))
16. 0.000 0.000 ↓ 0.0 0

Hash (cost=46,019.08..46,019.08 rows=530,416 width=16) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on affiliated_sub_accounts a_3 (cost=0.00..46,019.08 rows=530,416 width=16) (never executed)

  • Filter: (((item_type)::text = 'CourseTemplate'::text) AND (item_id = 1))
18. 0.015 0.015 ↓ 0.0 0 1

Index Scan using index_affiliated_sub_accounts_on_item_and_domain_id on affiliated_sub_accounts a_4 (cost=0.43..2.65 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: ((item_id = 1) AND ((item_type)::text = 'CourseTemplate'::text) AND (domain_id = domains.id))
  • Filter: (state = 0)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4
19. 0.000 0.000 ↓ 0.0 0

Index Scan using index_affiliated_sub_accounts_on_item_and_domain_id on affiliated_sub_accounts a_5 (cost=0.43..37,015.58 rows=1 width=8) (never executed)

  • Index Cond: ((item_id = 1) AND ((item_type)::text = 'CourseTemplate'::text))
  • Filter: (state = 0)