explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y2ud

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 228,836.302 ↓ 3.0 3 1

Subquery Scan on percentiles_by_domain (cost=67,423.02..67,423.06 rows=1 width=12) (actual time=228,835.987..228,836.302 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=5157405, temp read=5154374 written=5448
2. 0.280 228,836.254 ↓ 435.0 435 1

WindowAgg (cost=67,423.02..67,423.05 rows=1 width=48) (actual time=228,835.972..228,836.254 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=5157405, temp read=5154374 written=5448
3. 0.383 228,835.974 ↓ 435.0 435 1

Sort (cost=67,423.02..67,423.03 rows=1 width=40) (actual time=228,835.933..228,835.974 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=5157405, temp read=5154374 written=5448
4. 0.075 228,835.591 ↓ 435.0 435 1

Subquery Scan on current_360_scores_by_domain (cost=67,422.98..67,423.01 rows=1 width=40) (actual time=228,714.907..228,835.591 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=5157405, temp read=5154374 written=5448
5. 62.275 228,835.516 ↓ 435.0 435 1

GroupAggregate (cost=67,422.98..67,423.00 rows=1 width=40) (actual time=228,714.907..228,835.516 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=5157405, temp read=5154374 written=5448
6. 212.457 228,773.241 ↓ 316,760.0 316,760 1

Sort (cost=67,422.98..67,422.98 rows=1 width=13) (actual time=228,714.762..228,773.241 rows=316,760 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=5157405, temp read=5154374 written=5448
7. 1,127.727 228,560.784 ↓ 316,760.0 316,760 1

Nested Loop (cost=1.96..67,422.97 rows=1 width=13) (actual time=203,510.313..228,560.784 rows=316,760 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: 11086600
  • Buffers: shared hit=5157405, temp read=5153484 written=4558
8. 250.594 226,482.777 ↓ 316,760.0 316,760 1

Nested Loop Left Join (cost=1.96..67,419.30 rows=1 width=13) (actual time=203,510.298..226,482.777 rows=316,760 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=4523885, temp read=5153484 written=4558
9. 11,864.032 224,964.823 ↓ 10,220.6 316,840 1

Nested Loop Left Join (cost=1.82..66,978.40 rows=31 width=21) (actual time=203,213.053..224,964.823 rows=316,840 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: 150499000
  • Buffers: shared hit=2939685, temp read=5153484 written=4558
10. 267.444 205,813.471 ↓ 10,220.6 316,840 1

Nested Loop (cost=1.82..66,748.45 rows=31 width=21) (actual time=203,212.990..205,813.471 rows=316,840 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=2939679, temp read=5153484 written=4558
11. 1,493.079 205,079.427 ↓ 9,332.0 466,600 1

Nested Loop (cost=1.54..66,731.46 rows=50 width=25) (actual time=203,212.948..205,079.427 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=1519023, temp read=5153484 written=4558
12. 0.048 0.048 ↑ 2.1 36 1

Seq Scan on public.domain (cost=0.00..2.74 rows=74 width=8) (actual time=0.004..0.048 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. 2,032.969 203,586.300 ↓ 9,332.0 466,600 36

Materialize (cost=1.54..66,673.34 rows=50 width=25) (actual time=1.204..5,655.175 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=1519021, temp read=5153484 written=4558
14. 135.006 201,553.331 ↓ 9,332.0 466,600 1

Merge Join (cost=1.54..66,673.09 rows=50 width=25) (actual time=43.202..201,553.331 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=1519021, temp read=5077674 written=2393
15. 85,295.990 201,355.914 ↓ 9,332.0 466,600 1

Nested Loop (cost=1.27..70,877.31 rows=50 width=25) (actual time=43.194..201,355.914 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: 989658600
  • Buffers: shared hit=1519016, temp read=5077674 written=2393
16. 7.744 7.744 ↓ 1.0 2,122 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.016..7.744 rows=2,122 loops=1)

  • Output: section_to_question.section_id, section_to_question.question_id
  • Heap Fetches: 2122
  • Buffers: shared hit=65
17. 113,546.903 116,052.180 ↓ 8,640.7 466,600 2,122

Materialize (cost=0.99..69,135.44 rows=54 width=29) (actual time=0.023..54.690 rows=466,600 loops=2,122)

  • 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=1518951, temp read=5077674 written=2393
18. 133.368 2,505.277 ↓ 8,640.7 466,600 1

Nested Loop (cost=0.99..69,135.17 rows=54 width=29) (actual time=43.051..2,505.277 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=1518951
19. 58.626 1,905.309 ↓ 8,640.7 466,600 1

Nested Loop (cost=0.71..69,118.55 rows=54 width=25) (actual time=43.046..1,905.309 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=119202
20. 1,041.440 1,704.003 ↓ 3,567.0 7,134 1

Nested Loop (cost=0.29..69,104.27 rows=2 width=20) (actual time=43.036..1,704.003 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: 13447590
  • Buffers: shared hit=59457
21. 0.577 0.577 ↓ 1.0 1,886 1

Seq Scan on public."user" (cost=0.00..62.81 rows=1,881 width=8) (actual time=0.005..0.577 rows=1,886 loops=1)

  • Output: "user".organization_id, "user".id
  • Buffers: shared hit=44
22. 623.118 661.986 ↓ 3,567.0 7,134 1,886

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

  • Output: form_response.id, form_response.user_id, form_response_group.form_id
  • Buffers: shared hit=59413
23. 0.633 38.868 ↓ 3,567.0 7,134 1

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

  • Output: form_response.id, form_response.user_id, form_response_group.form_id
  • Buffers: shared hit=59413
24. 5.979 5.979 ↓ 1.4 10,752 1

Seq Scan on public.form_response (cost=0.00..762.00 rows=7,737 width=12) (actual time=0.005..5.979 rows=10,752 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: 21485
  • Buffers: shared hit=293
25. 17.074 32.256 ↑ 1.0 1 10,752

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,752)

  • 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=59120
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. 142.680 142.680 ↓ 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.020 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=59745
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. 62.411 62.411 ↓ 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.005..62.411 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=1420656
33. 7,287.223 7,287.320 ↓ 1.0 476 316,840

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

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

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

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

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,840)

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

SubPlan (forNested Loop Left Join)

37. 316.840 950.520 ↑ 2.0 1 316,840

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,840)

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

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,840)

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

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,760)

  • Output: parent.id, parent.hierarchy_level, parent.name, parent.parent_id, parent.created_date, parent.description
  • Buffers: shared hit=633520
Planning time : 2.501 ms
Execution time : 228,842.748 ms