explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IoFE

Settings
# exclusive inclusive rows x rows loops node
1. 304,825.594 10,977,215.987 ↓ 399.8 2,024,867 1

GroupAggregate (cost=1,214,889,226.05..1,237,373,861.64 rows=5,065 width=1,136) (actual time=8,685,453.482..10,977,215.987 rows=2,024,867 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.a, profile_scores.b, profile_scores.c, profile_scores.d, profile_scores.stress_a, profile_scores.stress_b, profile_scores.stress_c, profile_scores.stress_d, profile_scores.left_mode, profile_scores.lower_mode, profile_scores.right_mode, profile_scores.upper_mode, profile_scores.profile_code_a, profile_scores.profile_code_b, profile_scores.profile_code_c, profile_scores.profile_code_d, profile_scores.exported_at
2.          

CTE profile_scores

3. 5,401.812 1,763,109.453 ↓ 40.0 2,025,027 1

Nested Loop (cost=0.86..104,826.72 rows=50,652 width=564) (actual time=1.090..1,763,109.453 rows=2,025,027 loops=1)

4. 2,643.743 226,787.229 ↓ 40.0 2,025,027 1

Nested Loop (cost=0.85..103,687.04 rows=50,652 width=62) (actual time=0.044..226,787.229 rows=2,025,027 loops=1)

5. 1,562.030 17,590.732 ↓ 39.6 2,025,027 1

Nested Loop (cost=0.43..57,851.16 rows=51,077 width=44) (actual time=0.031..17,590.732 rows=2,025,027 loops=1)

6. 0.011 0.011 ↑ 1.0 1 1

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

  • Filter: (uuid = 'd5867329-ff5c-4d91-a8a0-5704f847433d'::uuid)
  • Rows Removed by Filter: 2
7. 16,028.691 16,028.691 ↓ 3.0 2,025,027 1

Index Scan using index_assessments_on_survey_id on assessments (cost=0.43..51,030.34 rows=681,032 width=48) (actual time=0.023..16,028.691 rows=2,025,027 loops=1)

  • Index Cond: (survey_id = surveys.id)
  • Filter: (end_time IS NOT NULL)
  • Rows Removed by Filter: 1,703
8. 206,552.754 206,552.754 ↑ 1.0 1 2,025,027

Index Scan using assessment_json_scores_pkey on assessment_json_scores scores (cost=0.43..0.90 rows=1 width=22) (actual time=0.102..0.102 rows=1 loops=2,025,027)

  • Index Cond: (id = assessments.id)
9. 1,530,920.412 1,530,920.412 ↑ 1.0 1 2,025,027

Function Scan on jsonb_populate_record profile (cost=0.01..0.01 rows=1 width=512) (actual time=0.756..0.756 rows=1 loops=2,025,027)

10. 8,144,452.954 10,672,390.393 ↑ 1.1 302,854,256 1

Sort (cost=1,214,784,399.34..1,215,617,161.74 rows=333,104,962 width=1,590) (actual time=8,685,453.264..10,672,390.393 rows=302,854,256 loops=1)

  • Sort Key: profile_scores.assessment_id, profile_scores.legacy_id, profile_scores.locale_code, profile_scores.start_time, profile_scores.end_time, profile_scores.a, profile_scores.b, profile_scores.c, profile_scores.d, profile_scores.stress_a, profile_scores.stress_b, profile_scores.stress_c, profile_scores.stress_d, profile_scores.left_mode, profile_scores.lower_mode, profile_scores.right_mode, profile_scores.upper_mode, profile_scores.profile_code_a, profile_scores.profile_code_b, profile_scores.profile_code_c, profile_scores.profile_code_d, profile_scores.exported_at
  • Sort Method: external merge Disk: 35,360,040kB
11. 169,848.713 2,527,937.439 ↑ 1.1 302,854,256 1

Hash Left Join (cost=28,877.04..17,703,275.06 rows=333,104,962 width=1,590) (actual time=1,775,717.816..2,527,937.439 rows=302,854,256 loops=1)

  • Hash Cond: (responses.item_id = items.id)
12. 307,248.759 2,358,088.679 ↑ 1.1 302,854,256 1

Merge Join (cost=28,866.14..16,805,556.76 rows=333,104,962 width=1,078) (actual time=1,775,717.752..2,358,088.679 rows=302,854,256 loops=1)

  • Merge Cond: (responses.assessment_id = profile_scores.id)
13. 202,898.883 202,898.883 ↑ 1.0 303,215,639 1

Index Scan using index_responses_on_assessment_id on responses (cost=0.57..11,021,376.00 rows=303,445,652 width=10) (actual time=2.106..202,898.883 rows=303,215,639 loops=1)

14. 70,429.340 1,847,941.037 ↓ 5,979.1 302,854,338 1

Materialize (cost=28,865.57..29,118.83 rows=50,652 width=1,076) (actual time=1,775,715.634..1,847,941.037 rows=302,854,338 loops=1)

15. 8,308.805 1,777,511.697 ↓ 40.0 2,025,027 1

Sort (cost=28,865.57..28,992.20 rows=50,652 width=1,076) (actual time=1,775,715.631..1,777,511.697 rows=2,025,027 loops=1)

  • Sort Key: profile_scores.id
  • Sort Method: external merge Disk: 226,744kB
16. 1,769,202.892 1,769,202.892 ↓ 40.0 2,025,027 1

CTE Scan on profile_scores (cost=0.00..1,013.04 rows=50,652 width=1,076) (actual time=1.095..1,769,202.892 rows=2,025,027 loops=1)

17. 0.005 0.047 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.042 0.042 ↑ 1.0 2 1

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

  • Filter: ((name)::text = ANY ('{Occupations,Education}'::text[]))
  • Rows Removed by Filter: 194
Planning time : 21.767 ms
Execution time : 10,978,678.216 ms