explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xfso : Optimization for: plan #zGcF

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.370 19.641 ↑ 1.0 1 1

GroupAggregate (cost=250.81..256.89 rows=1 width=44) (actual time=19.641..19.641 rows=1 loops=1)

  • Group Key: s.user_id, s.question_set_id, qs.question_set_id
2.          

CTE subtopic_breakdown

3. 1.444 17.907 ↑ 40.0 5 1

HashAggregate (cost=216.26..218.26 rows=200 width=24) (actual time=17.894..17.907 rows=5 loops=1)

  • Group Key: q.topic_id, q.subtopic_id
4. 4.638 16.463 ↓ 2.0 1,000 1

Hash Join (cost=102.71..211.22 rows=504 width=16) (actual time=12.649..16.463 rows=1,000 loops=1)

  • Hash Cond: (q.question_id = q2.question_id)
5. 1.945 1.945 ↑ 1.0 4,000 1

Seq Scan on questions q (cost=0.00..98.00 rows=4,000 width=12) (actual time=0.016..1.945 rows=4,000 loops=1)

6. 0.992 9.880 ↓ 2.0 1,000 1

Hash (cost=96.41..96.41 rows=504 width=8) (actual time=9.879..9.880 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
7. 3.026 8.888 ↓ 2.0 1,000 1

GroupAggregate (cost=0.57..91.37 rows=504 width=8) (actual time=0.141..8.888 rows=1,000 loops=1)

  • Group Key: q2.question_id
8. 2.922 5.862 ↓ 2.0 1,000 1

Merge Join (cost=0.57..81.29 rows=504 width=12) (actual time=0.116..5.862 rows=1,000 loops=1)

  • Merge Cond: (q2.question_id = aq.question_id)
9. 1.791 1.791 ↑ 1.0 2,000 1

Index Only Scan using ix_questions_question_set_id_question_id_answer_index on questions q2 (cost=0.28..44.28 rows=2,000 width=8) (actual time=0.058..1.791 rows=2,000 loops=1)

  • Index Cond: (question_set_id = 2)
  • Heap Fetches: 0
10. 1.149 1.149 ↑ 1.0 1,000 1

Index Only Scan using ix_answered_questions_user_id_q_set_id_q_id_answer_index on subscriptions_questions_association aq (cost=0.29..24.47 rows=1,009 width=8) (actual time=0.043..1.149 rows=1,000 loops=1)

  • Index Cond: ((user_id = 1) AND (question_set_id = 2))
  • Heap Fetches: 0
11.          

Initplan (forGroupAggregate)

12. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on question_counts_mv qc (cost=0.00..1.01 rows=1 width=1,444) (actual time=0.009..0.010 rows=1 loops=1)

13. 0.016 18.911 ↑ 1.0 5 1

Nested Loop (cost=31.54..34.71 rows=5 width=106) (actual time=18.889..18.911 rows=5 loops=1)

14. 0.011 0.115 ↑ 1.0 1 1

Nested Loop (cost=0.28..3.33 rows=1 width=74) (actual time=0.102..0.115 rows=1 loops=1)

15. 0.067 0.067 ↑ 1.0 1 1

Index Scan using pk_user_question_set_association on user_question_set_association s (cost=0.28..2.30 rows=1 width=24) (actual time=0.060..0.067 rows=1 loops=1)

  • Index Cond: ((user_id = 1) AND (question_set_id = 2))
16. 0.037 0.037 ↑ 1.0 1 1

Seq Scan on question_sets qs (cost=0.00..1.02 rows=1 width=50) (actual time=0.032..0.037 rows=1 loops=1)

  • Filter: (question_set_id = 2)
  • Rows Removed by Filter: 1
17. 0.033 18.780 ↑ 1.0 5 1

Sort (cost=31.26..31.28 rows=5 width=44) (actual time=18.778..18.780 rows=5 loops=1)

  • Sort Key: ts.name
  • Sort Method: quicksort Memory: 18kB
18.          

Initplan (forSort)

19. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on question_counts_mv qc_1 (cost=0.00..1.01 rows=1 width=1,444) (actual time=0.009..0.010 rows=1 loops=1)

20. 0.279 18.737 ↑ 1.0 5 1

GroupAggregate (cost=29.92..30.20 rows=5 width=44) (actual time=18.559..18.737 rows=5 loops=1)

  • Group Key: ts.topic_id
21. 0.028 18.458 ↑ 1.0 5 1

Sort (cost=29.92..29.93 rows=5 width=44) (actual time=18.455..18.458 rows=5 loops=1)

  • Sort Key: ts.topic_id
  • Sort Method: quicksort Memory: 18kB
22. 0.046 18.430 ↑ 1.0 5 1

Hash Right Join (cost=26.83..29.86 rows=5 width=44) (actual time=18.403..18.430 rows=5 loops=1)

  • Hash Cond: (subtopic_breakdown.topic_id = ts.topic_id)
23. 0.014 18.336 ↑ 40.0 5 1

Sort (cost=25.64..26.14 rows=200 width=55) (actual time=18.334..18.336 rows=5 loops=1)

  • Sort Key: sts.name
  • Sort Method: quicksort Memory: 18kB
24.          

Initplan (forSort)

25. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on question_counts_mv qc_2 (cost=0.00..1.01 rows=1 width=1,444) (actual time=0.014..0.015 rows=1 loops=1)

26. 0.238 18.307 ↑ 40.0 5 1

HashAggregate (cost=8.49..16.99 rows=200 width=55) (actual time=18.199..18.307 rows=5 loops=1)

  • Group Key: sts.subtopic_id, subtopic_breakdown.topic_id, subtopic_breakdown.subtopic_id
27. 0.041 18.069 ↑ 40.0 5 1

Hash Left Join (cost=1.45..5.99 rows=200 width=43) (actual time=18.040..18.069 rows=5 loops=1)

  • Hash Cond: (subtopic_breakdown.subtopic_id = sts.subtopic_id)
28. 17.925 17.925 ↑ 40.0 5 1

CTE Scan on subtopic_breakdown (cost=0.00..4.00 rows=200 width=24) (actual time=17.903..17.925 rows=5 loops=1)

29. 0.052 0.103 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=19) (actual time=0.102..0.103 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
30. 0.051 0.051 ↑ 1.0 20 1

Seq Scan on subtopics sts (cost=0.00..1.20 rows=20 width=19) (actual time=0.031..0.051 rows=20 loops=1)

31. 0.022 0.048 ↑ 1.0 5 1

Hash (cost=1.12..1.12 rows=5 width=12) (actual time=0.047..0.048 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
32. 0.026 0.026 ↑ 1.0 5 1

Seq Scan on topics ts (cost=0.00..1.12 rows=5 width=12) (actual time=0.019..0.026 rows=5 loops=1)

  • Filter: (question_set_id = 2)
  • Rows Removed by Filter: 5
33.          

SubPlan (forGroupAggregate)

34. 0.100 0.335 ↑ 1.0 1 1

Aggregate (cost=1.73..1.74 rows=1 width=8) (actual time=0.335..0.335 rows=1 loops=1)

35. 0.235 0.235 ↑ 1.0 20 1

Index Only Scan using ix_subs_score on user_question_set_association centile_rank_table (cost=0.28..1.68 rows=20 width=4) (actual time=0.221..0.235 rows=20 loops=1)

  • Index Cond: ((question_set_id = s.question_set_id) AND (score IS NOT NULL))
  • Heap Fetches: 0
36. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on tests active_test (cost=0.00..1.02 rows=1 width=1) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: ((user_id = s.user_id) AND (question_set_id = s.question_set_id))