explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x6Gj

Settings
# exclusive inclusive rows x rows loops node
1. 1,242.516 3,868.346 ↓ 10.0 9,997 1

HashAggregate (cost=16,868,408.75..16,868,538.75 rows=1,000 width=688) (actual time=3,846.570..3,868.346 rows=9,997 loops=1)

  • Group Key: profile_scores.assessment_id, profile_scores.legacy_id, profile_scores.locale_code, profile_scores.start_time, profile_scores.end_time, ((profile_scores.data #>> '{A}'::text[]))::integer, ((profile_scores.data #>> '{B}'::text[]))::integer, ((profile_scores.data #>> '{C}'::text[]))::integer, ((profile_scores.data #>> '{D}'::text[]))::integer, ((profile_scores.data #>> '{Stress_A}'::text[]))::integer, ((profile_scores.data #>> '{Stress_B}'::text[]))::integer, ((profile_scores.data #>> '{Stress_C}'::text[]))::integer, ((profile_scores.data #>> '{Stress_D}'::text[]))::integer, ((profile_scores.data #>> '{Left_mode}'::text[]))::integer, ((profile_scores.data #>> '{Lower_mode}'::text[]))::integer, ((profile_scores.data #>> '{Right_mode}'::text[]))::integer, ((profile_scores.data #>> '{Upper_mode}'::text[]))::integer, ((profile_scores.data #>> '{Profile_Code_A}'::text[]))::integer, ((profile_scores.data #>> '{Profile_Code_B}'::text[]))::integer, ((profile_scores.data #>> '{Profile_Code_C}'::text[]))::integer, ((profile_scores.data #>> '{Profile_Code_D}'::text[]))::integer, profile_scores.exported_at
2.          

CTE profile_scores

3. 7.071 536.574 ↑ 1.0 10,000 1

Limit (cost=0.85..20,520.19 rows=10,000 width=84) (actual time=0.115..536.574 rows=10,000 loops=1)

4. 453.037 529.503 ↑ 5.1 10,000 1

Nested Loop (cost=0.85..103,935.39 rows=50,652 width=84) (actual time=0.113..529.503 rows=10,000 loops=1)

5. 7.181 46.466 ↑ 5.1 10,000 1

Nested Loop (cost=0.43..57,847.88 rows=51,074 width=44) (actual time=0.032..46.466 rows=10,000 loops=1)

6. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on surveys (cost=0.00..10.50 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: (uuid = 'd5867329-ff5c-4d91-a8a0-5704f847433d'::uuid)
  • Rows Removed by Filter: 1
7. 39.276 39.276 ↑ 68.1 10,000 1

Index Scan using index_assessments_on_survey_id on assessments (cost=0.43..51,027.46 rows=680,992 width=48) (actual time=0.020..39.276 rows=10,000 loops=1)

  • Index Cond: (survey_id = surveys.id)
  • Filter: (end_time IS NOT NULL)
  • Rows Removed by Filter: 1,378
8. 30.000 30.000 ↑ 1.0 1 10,000

Index Scan using assessment_json_scores_pkey on assessment_json_scores scores (cost=0.43..0.90 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=10,000)

  • Index Cond: (id = assessments.id)
9. 876.901 2,625.830 ↑ 55.3 1,188,845 1

Hash Left Join (cost=11.47..12,573,324.24 rows=65,762,528 width=1,142) (actual time=0.217..2,625.830 rows=1,188,845 loops=1)

  • Hash Cond: (responses.item_id = items.id)
10. 679.646 1,748.863 ↑ 55.3 1,188,845 1

Nested Loop (cost=0.57..4,504,582.04 rows=65,762,528 width=598) (actual time=0.140..1,748.863 rows=1,188,845 loops=1)

11. 549.217 549.217 ↑ 1.0 10,000 1

CTE Scan on profile_scores (cost=0.00..200.00 rows=10,000 width=596) (actual time=0.120..549.217 rows=10,000 loops=1)

12. 520.000 520.000 ↑ 55.3 119 10,000

Index Scan using index_responses_on_assessment_id on responses (cost=0.57..384.68 rows=6,576 width=10) (actual time=0.006..0.052 rows=119 loops=10,000)

  • Index Cond: (assessment_id = profile_scores.id)
13. 0.005 0.066 ↑ 1.0 2 1

Hash (cost=10.88..10.88 rows=2 width=520) (actual time=0.065..0.066 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.061 0.061 ↑ 1.0 2 1

Seq Scan on items (cost=0.00..10.88 rows=2 width=520) (actual time=0.041..0.061 rows=2 loops=1)

  • Filter: ((name)::text = ANY ('{Occupations,Education}'::text[]))
  • Rows Removed by Filter: 194
Planning time : 2.087 ms
Execution time : 3,879.944 ms