explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wwO6

Settings
# exclusive inclusive rows x rows loops node
1. 0.050 226,051.141 ↓ 3.0 3 1

Subquery Scan on percentiles_by_domain (cost=67,401.77..67,401.81 rows=1 width=12) (actual time=226,050.840..226,051.141 rows=3 loops=1)

  • Output: percentiles_by_domain.domain_id, percentiles_by_domain.percentile
  • Filter: (percentiles_by_domain.organization_id = 77)
  • Rows Removed by Filter: 432
  • Buffers: shared hit=5156640 dirtied=2, temp read=5159162 written=5448
2. 0.268 226,051.091 ↓ 435.0 435 1

WindowAgg (cost=67,401.77..67,401.80 rows=1 width=48) (actual time=226,050.825..226,051.091 rows=435 loops=1)

  • Output: (percent_rank() OVER (?) * '100'::double precision), current_360_scores_by_domain.domain_id, current_360_scores_by_domain.organization_id, current_360_scores_by_domain.current_value
  • Buffers: shared hit=5156640 dirtied=2, temp read=5159162 written=5448
3. 0.405 226,050.823 ↓ 435.0 435 1

Sort (cost=67,401.77..67,401.78 rows=1 width=40) (actual time=226,050.775..226,050.823 rows=435 loops=1)

  • Output: current_360_scores_by_domain.domain_id, current_360_scores_by_domain.current_value, current_360_scores_by_domain.organization_id
  • Sort Key: current_360_scores_by_domain.domain_id, current_360_scores_by_domain.current_value
  • Sort Method: quicksort Memory: 58kB
  • Buffers: shared hit=5156640 dirtied=2, temp read=5159162 written=5448
4. 0.068 226,050.418 ↓ 435.0 435 1

Subquery Scan on current_360_scores_by_domain (cost=67,401.73..67,401.76 rows=1 width=40) (actual time=225,932.102..226,050.418 rows=435 loops=1)

  • Output: current_360_scores_by_domain.domain_id, current_360_scores_by_domain.current_value, current_360_scores_by_domain.organization_id
  • Buffers: shared hit=5156637 dirtied=2, temp read=5159162 written=5448
5. 60.663 226,050.350 ↓ 435.0 435 1

GroupAggregate (cost=67,401.73..67,401.75 rows=1 width=40) (actual time=225,932.102..226,050.350 rows=435 loops=1)

  • Output: avg(form_response_answer.answer_value), parent.id, "user".organization_id
  • Group Key: "user".organization_id, parent.id
  • Buffers: shared hit=5156637 dirtied=2, temp read=5159162 written=5448
6. 217.425 225,989.687 ↓ 316,551.0 316,551 1

Sort (cost=67,401.73..67,401.73 rows=1 width=13) (actual time=225,931.873..225,989.687 rows=316,551 loops=1)

  • Output: parent.id, "user".organization_id, form_response_answer.answer_value
  • Sort Key: "user".organization_id, parent.id
  • Sort Method: external merge Disk: 7080kB
  • Buffers: shared hit=5156637 dirtied=2, temp read=5159162 written=5448
7. 1,123.356 225,772.262 ↓ 316,551.0 316,551 1

Nested Loop (cost=1.96..67,401.72 rows=1 width=13) (actual time=200,716.977..225,772.262 rows=316,551 loops=1)

  • Output: parent.id, "user".organization_id, form_response_answer.answer_value
  • Join Filter: (domain.parent_id = parent.id)
  • Rows Removed by Join Filter: 11079285
  • Buffers: shared hit=5156637 dirtied=2, temp read=5158272 written=4558
8. 262.661 223,699.253 ↓ 316,551.0 316,551 1

Nested Loop Left Join (cost=1.96..67,398.05 rows=1 width=13) (actual time=200,716.956..223,699.253 rows=316,551 loops=1)

  • Output: form_response_answer.answer_value, "user".organization_id, domain.parent_id
  • Filter: (form_response_group.form_id = (SubPlan 2))
  • Rows Removed by Filter: 80
  • Buffers: shared hit=4523535 dirtied=2, temp read=5158272 written=4558
9. 11,915.143 222,170.068 ↓ 10,554.4 316,631 1

Nested Loop Left Join (cost=1.82..66,971.37 rows=30 width=21) (actual time=200,435.206..222,170.068 rows=316,631 loops=1)

  • Output: form_response_answer.answer_value, form_response_group.form_id, "user".organization_id, domain.parent_id, subscription.subscription_configuration_id
  • Join Filter: (subscription.id = engagement.subscription_id)
  • Rows Removed by Join Filter: 150399725
  • Buffers: shared hit=2940380 dirtied=2, temp read=5158272 written=4558
10. 270.707 202,972.412 ↓ 10,554.4 316,631 1

Nested Loop (cost=1.82..66,748.46 rows=30 width=21) (actual time=200,435.146..202,972.412 rows=316,631 loops=1)

  • Output: form_response_answer.answer_value, form_response_group.form_id, "user".organization_id, domain.parent_id, engagement.subscription_id
  • Join Filter: ("user".id = engagement.client_id)
  • Buffers: shared hit=2940374 dirtied=2, temp read=5158272 written=4558
11. 1,446.254 202,235.105 ↓ 9,332.0 466,600 1

Nested Loop (cost=1.54..66,731.46 rows=50 width=25) (actual time=200,435.091..202,235.105 rows=466,600 loops=1)

  • Output: form_response_answer.answer_value, form_response.user_id, form_response_group.form_id, "user".organization_id, "user".id, domain.parent_id
  • Join Filter: (section.domain_id = domain.id)
  • Rows Removed by Join Filter: 16331000
  • Buffers: shared hit=1519071, temp read=5158272 written=4558
12. 0.039 0.039 ↑ 2.1 36 1

Seq Scan on public.domain (cost=0.00..2.74 rows=74 width=8) (actual time=0.003..0.039 rows=36 loops=1)

  • Output: domain.id, domain.hierarchy_level, domain.name, domain.parent_id, domain.created_date, domain.description
  • Buffers: shared hit=2
13. 1,984.103 200,788.812 ↓ 9,332.0 466,600 36

Materialize (cost=1.54..66,673.35 rows=50 width=25) (actual time=1.236..5,577.467 rows=466,600 loops=36)

  • Output: form_response_answer.answer_value, section.domain_id, form_response.user_id, form_response_group.form_id, "user".organization_id, "user".id
  • Buffers: shared hit=1519069, temp read=5158272 written=4558
14. 134.934 198,804.709 ↓ 9,332.0 466,600 1

Merge Join (cost=1.54..66,673.10 rows=50 width=25) (actual time=44.346..198,804.709 rows=466,600 loops=1)

  • Output: form_response_answer.answer_value, section.domain_id, form_response.user_id, form_response_group.form_id, "user".organization_id, "user".id
  • Merge Cond: (section_to_question.section_id = section.id)
  • Buffers: shared hit=1519069, temp read=5082462 written=2393
15. 86,220.896 198,608.043 ↓ 9,332.0 466,600 1

Nested Loop (cost=1.27..70,877.32 rows=50 width=25) (actual time=44.339..198,608.043 rows=466,600 loops=1)

  • Output: form_response_answer.answer_value, section_to_question.section_id, form_response.user_id, form_response_group.form_id, "user".organization_id, "user".id
  • Join Filter: (question.id = section_to_question.question_id)
  • Rows Removed by Join Filter: 990591800
  • Buffers: shared hit=1519064, temp read=5082462 written=2393
16. 8.431 8.431 ↓ 1.0 2,124 1

Index Only Scan using section_to_question_pkey on public.section_to_question (cost=0.28..87.98 rows=2,042 width=8) (actual time=0.020..8.431 rows=2,124 loops=1)

  • Output: section_to_question.section_id, section_to_question.question_id
  • Heap Fetches: 2124
  • Buffers: shared hit=67
17. 109,807.011 112,378.716 ↓ 8,640.7 466,600 2,124

Materialize (cost=0.99..69,135.45 rows=54 width=29) (actual time=0.024..52.909 rows=466,600 loops=2,124)

  • Output: form_response_answer.answer_value, form_response_answer.question_id, question.id, form_response.user_id, form_response_group.form_id, "user".organization_id, "user".id
  • Buffers: shared hit=1518997, temp read=5082462 written=2393
18. 155.523 2,571.705 ↓ 8,640.7 466,600 1

Nested Loop (cost=0.99..69,135.18 rows=54 width=29) (actual time=44.174..2,571.705 rows=466,600 loops=1)

  • Output: form_response_answer.answer_value, form_response_answer.question_id, question.id, form_response.user_id, form_response_group.form_id, "user".organization_id, "user".id
  • Buffers: shared hit=1518997
19. 64.332 1,949.582 ↓ 8,640.7 466,600 1

Nested Loop (cost=0.71..69,118.56 rows=54 width=25) (actual time=44.160..1,949.582 rows=466,600 loops=1)

  • Output: form_response_answer.answer_value, form_response_answer.question_id, form_response.user_id, form_response_group.form_id, "user".organization_id, "user".id
  • Buffers: shared hit=119248
20. 1,068.652 1,735.436 ↓ 3,567.0 7,134 1

Nested Loop (cost=0.29..69,104.27 rows=2 width=20) (actual time=44.149..1,735.436 rows=7,134 loops=1)

  • Output: form_response.id, form_response.user_id, form_response_group.form_id, "user".organization_id, "user".id
  • Join Filter: (form_response.user_id = "user".id)
  • Rows Removed by Join Filter: 13454724
  • Buffers: shared hit=59502
21. 0.673 0.673 ↓ 1.0 1,887 1

Seq Scan on public."user" (cost=0.00..62.81 rows=1,881 width=8) (actual time=0.004..0.673 rows=1,887 loops=1)

  • Output: "user".organization_id, "user".id
  • Buffers: shared hit=44
22. 626.214 666.111 ↓ 3,567.0 7,134 1,887

Materialize (cost=0.29..68,985.04 rows=2 width=12) (actual time=0.000..0.353 rows=7,134 loops=1,887)

  • Output: form_response.id, form_response.user_id, form_response_group.form_id
  • Buffers: shared hit=59458
23. 1.480 39.897 ↓ 3,567.0 7,134 1

Nested Loop (cost=0.29..68,985.03 rows=2 width=12) (actual time=0.036..39.897 rows=7,134 loops=1)

  • Output: form_response.id, form_response.user_id, form_response_group.form_id
  • Buffers: shared hit=59458
24. 6.116 6.116 ↓ 1.4 10,767 1

Seq Scan on public.form_response (cost=0.00..762.00 rows=7,737 width=12) (actual time=0.005..6.116 rows=10,767 loops=1)

  • Output: form_response.id, form_response.form_response_group_id, form_response.user_id
  • Filter: ((form_response.submitter_type <> 'client'::audience_type_enum) AND (form_response.form_response_status_type = 'complete'::form_response_status_type))
  • Rows Removed by Filter: 21531
  • Buffers: shared hit=293
25. 17.119 32.301 ↑ 1.0 1 10,767

Index Scan using form_response_group_pkey on public.form_response_group (cost=0.29..8.81 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10,767)

  • Output: form_response_group.id, form_response_group.active, form_response_group.number, form_response_group.engagement_id, form_response_group.form_id, form_response_group.client_viewable, form_response_group.created_date, form_response_group.updated_date, form_response_group.user_id, form_response_group.released_date
  • Index Cond: (form_response_group.id = form_response.form_response_group_id)
  • Filter: (form_response_group.client_viewable AND (form_response_group.number = (SubPlan 1)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=59165
26.          

SubPlan (forIndex Scan)

27. 7.591 15.182 ↑ 1.0 1 7,591

GroupAggregate (cost=0.29..8.32 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=7,591)

  • Output: max(frg2.number), frg2.engagement_id, frg2.form_id
  • Group Key: frg2.engagement_id, frg2.form_id
  • Buffers: shared hit=26817
28. 7.591 7.591 ↑ 1.0 1 7,591

Index Scan using ix_form_response_group_form_id_engagement_id_number on public.form_response_group frg2 (cost=0.29..8.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=7,591)

  • Output: frg2.id, frg2.active, frg2.number, frg2.engagement_id, frg2.form_id, frg2.client_viewable, frg2.created_date, frg2.updated_date, frg2.user_id, frg2.released_date
  • Index Cond: ((frg2.form_id = form_response_group.form_id) AND (frg2.engagement_id = form_response_group.engagement_id))
  • Filter: frg2.client_viewable
  • Rows Removed by Filter: 0
  • Buffers: shared hit=26817
29. 149.814 149.814 ↓ 1.0 65 7,134

Index Scan using ix_form_response_answer_form_response_id on public.form_response_answer (cost=0.42..6.52 rows=62 width=13) (actual time=0.004..0.021 rows=65 loops=7,134)

  • Output: form_response_answer.id, form_response_answer.form_response_id, form_response_answer.question_id, form_response_answer.answer_text, form_response_answer.answer_value, form_response_answer.created_date, form_response_answer.updated_date, form_response_answer.follow_up_text
  • Index Cond: (form_response_answer.form_response_id = form_response.id)
  • Filter: (form_response_answer.answer_value IS NOT NULL)
  • Rows Removed by Filter: 7
  • Buffers: shared hit=59746
30. 466.600 466.600 ↑ 1.0 1 466,600

Index Only Scan using question_pkey on public.question (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=466,600)

  • Output: question.id
  • Index Cond: (question.id = form_response_answer.question_id)
  • Heap Fetches: 466548
  • Buffers: shared hit=1399749
31. 61.732 61.732 ↓ 535.1 466,618 1

Index Scan using section_pkey on public.section (cost=0.28..45.55 rows=872 width=8) (actual time=0.006..61.732 rows=466,618 loops=1)

  • Output: section.id, section.name, section.created_date, section.updated_date, section.domain_id
  • Buffers: shared hit=5
32. 466.600 466.600 ↑ 1.0 1 466,600

Index Scan using engagement_client_id_coach_id_subscription_id_key on public.engagement (cost=0.28..0.33 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=466,600)

  • Output: engagement.client_id, engagement.subscription_id
  • Index Cond: (engagement.client_id = form_response.user_id)
  • Filter: ((engagement.end_date > now()) OR (engagement.end_date IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1421303 dirtied=2
33. 7,282.425 7,282.513 ↓ 1.0 476 316,631

Materialize (cost=0.00..13.04 rows=469 width=8) (actual time=0.000..0.023 rows=476 loops=316,631)

  • Output: subscription.id, subscription.subscription_configuration_id
  • Buffers: shared hit=6
34. 0.088 0.088 ↓ 1.0 476 1

Seq Scan on public.subscription (cost=0.00..10.69 rows=469 width=8) (actual time=0.008..0.088 rows=476 loops=1)

  • Output: subscription.id, subscription.subscription_configuration_id
  • Buffers: shared hit=6
35. 316.631 316.631 ↑ 1.0 1 316,631

Index Scan using subscription_configuration_pkey on public.subscription_configuration (cost=0.14..0.18 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=316,631)

  • Output: subscription_configuration.id, subscription_configuration.three_sixty_custom_slug
  • Index Cond: (subscription_configuration.id = subscription.subscription_configuration_id)
  • Buffers: shared hit=633262
36.          

SubPlan (forNested Loop Left Join)

37. 316.631 949.893 ↑ 2.0 1 316,631

Bitmap Heap Scan on public.form (cost=8.57..14.03 rows=2 width=4) (actual time=0.003..0.003 rows=1 loops=316,631)

  • Output: form.id
  • Recheck Cond: ((form.slug)::text = ANY ((ARRAY['360'::character varying, subscription_configuration.three_sixty_custom_slug])::text[]))
  • Heap Blocks: exact=316631
  • Buffers: shared hit=949893
38. 633.262 633.262 ↑ 2.0 1 316,631

Bitmap Index Scan on form_slug_key (cost=0.00..8.57 rows=2 width=0) (actual time=0.002..0.002 rows=1 loops=316,631)

  • Index Cond: ((form.slug)::text = ANY ((ARRAY['360'::character varying, subscription_configuration.three_sixty_custom_slug])::text[]))
  • Buffers: shared hit=633262
39. 949.653 949.653 ↑ 2.1 36 316,551

Seq Scan on public.domain parent (cost=0.00..2.74 rows=74 width=4) (actual time=0.001..0.003 rows=36 loops=316,551)

  • Output: parent.id, parent.hierarchy_level, parent.name, parent.parent_id, parent.created_date, parent.description
  • Buffers: shared hit=633102
Planning time : 2.979 ms
Execution time : 226,058.155 ms