explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cmVl

Settings
# exclusive inclusive rows x rows loops node
1. 1,079.155 4,792.658 ↓ 1.0 525,076 1

Seq Scan on domains (cost=0.00..26,081,294,600.58 rows=521,971 width=878) (actual time=4,124.482..4,792.658 rows=525,076 loops=1)

  • 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))
  • Rows Removed by Filter: 1144
  • Buffers: shared hit=1593166
2.          

SubPlan (forSeq Scan)

3. 0.000 0.000 ↓ 0.0 0

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) (never executed)

  • Index Cond: ((item_id = 1) AND ((item_type)::text = 'CourseTemplate'::text) AND (domain_id = domains.id))
  • Filter: (state = 1)
4. 274.116 274.116 ↑ 1.0 523,227 1

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) (actual time=0.026..274.116 rows=523,227 loops=1)

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

Nested Loop (cost=0.85..86.48 rows=7 width=0) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Index Scan using index_users_on_sub_account_id on users u (cost=0.43..4.17 rows=31 width=8) (never executed)

  • Index Cond: (sub_account_id = domains.id)
7. 0.000 0.000 ↓ 0.0 0

Index Scan using index_enrollments_on_user_id on enrollments e (cost=0.42..2.65 rows=1 width=8) (never executed)

  • Index Cond: (user_id = u.id)
  • Filter: ((deleted_at IS NULL) AND ((inactive IS NULL) OR (NOT inactive)) AND (course_template_id = 1))
8. 830.083 2,082.631 ↓ 1.0 685,385 1

Merge Join (cost=13.07..130,778.98 rows=685,260 width=8) (actual time=0.069..2,082.631 rows=685,385 loops=1)

  • Merge Cond: (e_1.user_id = u_1.id)
  • Buffers: shared hit=929230
9. 358.083 358.083 ↓ 1.0 685,385 1

Index Scan using index_enrollments_on_user_id on enrollments e_1 (cost=0.42..31,891.44 rows=685,260 width=8) (actual time=0.024..358.083 rows=685,385 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((inactive IS NULL) OR (NOT inactive)) AND (course_template_id = 1))
  • Rows Removed by Filter: 2373
  • Buffers: shared hit=240304
10. 894.465 894.465 ↑ 1.1 2,684,469 1

Index Scan using users_pkey on users u_1 (cost=0.43..212,909.35 rows=2,864,540 width=16) (actual time=0.008..894.465 rows=2,684,469 loops=1)

  • Buffers: shared hit=688926
11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..49,948.42 rows=1 width=0) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Seq Scan on affiliated_sub_accounts a_2 (cost=0.00..49,945.76 rows=1 width=8) (never executed)

  • Filter: (((item_type)::text = 'CourseTemplate'::text) AND (item_id = 1) AND (domain_id = domains.id))
13. 0.000 0.000 ↓ 0.0 0

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) (never executed)

  • Index Cond: (affiliated_sub_account_id = a_2.id)
  • Filter: (((item_type)::text <> 'CourseTemplate'::text) OR (item_id <> 1))
14. 260.369 1,176.311 ↓ 0.0 0 1

Hash Join (cost=52,649.28..86,661.20 rows=353,456 width=8) (actual time=1,176.311..1,176.311 rows=0 loops=1)

  • Hash Cond: (s_1.affiliated_sub_account_id = a_3.id)
  • Buffers: shared hit=33311
15. 348.962 348.962 ↓ 1.0 1,046,693 1

Seq Scan on affiliated_sub_account_sources s_1 (cost=0.00..26,552.39 rows=1,046,658 width=8) (actual time=0.026..348.962 rows=1,046,693 loops=1)

  • Filter: ((deleted_at IS NULL) AND (((item_type)::text <> 'CourseTemplate'::text) OR (item_id <> 1)))
  • Rows Removed by Filter: 26
  • Buffers: shared hit=10852
16. 141.035 566.980 ↑ 1.0 523,227 1

Hash (cost=46,019.08..46,019.08 rows=530,416 width=16) (actual time=566.980..566.980 rows=523,227 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 32719kB
  • Buffers: shared hit=22459
17. 425.945 425.945 ↑ 1.0 523,227 1

Seq Scan on affiliated_sub_accounts a_3 (cost=0.00..46,019.08 rows=530,416 width=16) (actual time=0.016..425.945 rows=523,227 loops=1)

  • Filter: (((item_type)::text = 'CourseTemplate'::text) AND (item_id = 1))
  • Rows Removed by Filter: 1047250
  • Buffers: shared hit=22459
18. 0.000 0.000 ↓ 0.0 0

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) (never executed)

  • Index Cond: ((item_id = 1) AND ((item_type)::text = 'CourseTemplate'::text) AND (domain_id = domains.id))
  • Filter: (state = 0)
19. 180.445 180.445 ↓ 0.0 0 1

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) (actual time=180.445..180.445 rows=0 loops=1)

  • Index Cond: ((item_id = 1) AND ((item_type)::text = 'CourseTemplate'::text))
  • Filter: (state = 0)
  • Rows Removed by Filter: 523227
  • Buffers: shared hit=284655